To load or not to load

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

Hello,

I would like to do something like this when the form load is attempted:
1) Check to see if any of the records [Comment] fields equal the special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
 
Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition is
true and count the query records instead of the table records for it to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Hi Rod,

in the code that opens the form, before you do this:

DoCmd.Openform "formname"

do this:

On Error Resume Next


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




It canceled the form but I get a VB window that pops up stating
Run-time error '2001':
You canceled the previous operation.

I just want the operation to fail without any notification. This is very
close.

Thanks

strive4peace said:
Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition is
true and count the query records instead of the table records for it to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Hello,

I would like to do something like this when the form load is attempted:
1) Check to see if any of the records [Comment] fields equal the special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
I have
Private Sub Form_Open(Cancel As Integer)

If DCount("*", "[tblCandidates]", "[COMMENTS] = '" & Chr(255) & "'") = 0
Then
Cancel = True
End If

End Sub
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0; it should be 1 and therefore
load.

strive4peace said:
Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition is
true and count the query records instead of the table records for it to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Hello,

I would like to do something like this when the form load is attempted:
1) Check to see if any of the records [Comment] fields equal the special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
Click to expand...
Click to expand...
 
Hi Rod,

it is looking for the field EQUAL to chr(255), not containing "255"...
you will need to change the condition in that case -- and this will take
even longer to find:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0"
or, if you want to find either condition:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0 OR [COMMENTS]
= '" & Chr(255) & "'"

InStr is in string -- returns the position in the first string that the
second string was found


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




I have
Private Sub Form_Open(Cancel As Integer)

If DCount("*", "[tblCandidates]", "[COMMENTS] = '" & Chr(255) & "'") = 0
Then
Cancel = True
End If

End Sub
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0; it should be 1 and therefore
load.

strive4peace said:
Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition is
true and count the query records instead of the table records for it to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Hello,

I would like to do something like this when the form load is attempted:
1) Check to see if any of the records [Comment] fields equal the special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
This key record will have only char(255) in the comments field, nothing else.
So, I will not need to traverse the entire memo field. With that in mind
shouldn't the DCount solution work - you are right, DCount should be faster
than InStr so that is my preference, but I can't figure out why the DCount
solution does not work.

strive4peace said:
Hi Rod,

it is looking for the field EQUAL to chr(255), not containing "255"...
you will need to change the condition in that case -- and this will take
even longer to find:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0"
or, if you want to find either condition:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0 OR [COMMENTS]
= '" & Chr(255) & "'"

InStr is in string -- returns the position in the first string that the
second string was found


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




I have
Private Sub Form_Open(Cancel As Integer)

If DCount("*", "[tblCandidates]", "[COMMENTS] = '" & Chr(255) & "'") = 0
Then
Cancel = True
End If

End Sub
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0; it should be 1 and therefore
load.

strive4peace said:
Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition is
true and count the query records instead of the table records for it to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*





Rod wrote:
Hello,

I would like to do something like this when the form load is attempted:
1) Check to see if any of the records [Comment] fields equal the special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
Hi Rod,

you said:
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0;
it should be 1 and therefore load.
Click to expand...

this is not chr(255), this is a text string that says
"hold_the_alt_key_dn_and_type_255"

what you are looking for, chr(255), is not in this string... that is why
the count is zero...

since it is a memo field, you will not be able to index it. This is
going to be a VERY slow way to mark a key record. While this may be the
way you have the user do it, I would store whether a record is marked or
not in a number field -- all you need is byte, which goes from 0 to 255
-- although I prefer to use the Integer data type, which can hold values
+/- 32K

Warm Regards,
Crystal
remote programming and training

*
(: have an awesome day :)
*
~~~~~~
Learn Access on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
~~~~~~
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~~~~~


This key record will have only char(255) in the comments field, nothing else.
So, I will not need to traverse the entire memo field. With that in mind
shouldn't the DCount solution work - you are right, DCount should be faster
than InStr so that is my preference, but I can't figure out why the DCount
solution does not work.

strive4peace said:
Hi Rod,

it is looking for the field EQUAL to chr(255), not containing "255"...
you will need to change the condition in that case -- and this will take
even longer to find:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0"
or, if you want to find either condition:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0 OR [COMMENTS]
= '" & Chr(255) & "'"

InStr is in string -- returns the position in the first string that the
second string was found


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




I have
Private Sub Form_Open(Cancel As Integer)

If DCount("*", "[tblCandidates]", "[COMMENTS] = '" & Chr(255) & "'") = 0
Then
Cancel = True
End If

End Sub
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0; it should be 1 and therefore
load.

:

Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition is
true and count the query records instead of the table records for it to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*





Rod wrote:
Hello,

I would like to do something like this when the form load is attempted:
1) Check to see if any of the records [Comment] fields equal the special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
I am looking to use that invisible character as a pseudo hidden key to open
the form. How can I accomplish this?

strive4peace said:
Hi Rod,

you said:
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0;
it should be 1 and therefore load.
Click to expand...

this is not chr(255), this is a text string that says
"hold_the_alt_key_dn_and_type_255"

what you are looking for, chr(255), is not in this string... that is why
the count is zero...

since it is a memo field, you will not be able to index it. This is
going to be a VERY slow way to mark a key record. While this may be the
way you have the user do it, I would store whether a record is marked or
not in a number field -- all you need is byte, which goes from 0 to 255
-- although I prefer to use the Integer data type, which can hold values
+/- 32K

Warm Regards,
Crystal
remote programming and training

*
(: have an awesome day :)
*
~~~~~~
Learn Access on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
~~~~~~
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~~~~~


This key record will have only char(255) in the comments field, nothing else.
So, I will not need to traverse the entire memo field. With that in mind
shouldn't the DCount solution work - you are right, DCount should be faster
than InStr so that is my preference, but I can't figure out why the DCount
solution does not work.

strive4peace said:
Hi Rod,

it is looking for the field EQUAL to chr(255), not containing "255"...
you will need to change the condition in that case -- and this will take
even longer to find:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0"
or, if you want to find either condition:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0 OR [COMMENTS]
= '" & Chr(255) & "'"

InStr is in string -- returns the position in the first string that the
second string was found


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*





Rod wrote:
I have
Private Sub Form_Open(Cancel As Integer)

If DCount("*", "[tblCandidates]", "[COMMENTS] = '" & Chr(255) & "'") = 0
Then
Cancel = True
End If

End Sub
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0; it should be 1 and therefore
load.

:

Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition is
true and count the query records instead of the table records for it to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*





Rod wrote:
Hello,

I would like to do something like this when the form load is attempted:
1) Check to see if any of the records [Comment] fields equal the special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
I tested this on a 73000 record zipcode table. First, write a query like
this (substituting your tablenames) and save it as Query1:

SELECT tblZipCode.City, tblZipCode.State, tblZipCode.County
FROM tblZipCode
WHERE (((tblZipCode.County) Like "* *"));
******* between the two asterisks in the query line, enter Alt-255********

On the form's open event:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Query1")
If Not rs.EOF Then
MsgBox "The Alt-255 Exists", vbCritical, "Thank you Jesus!"
..........blah,blah,
else
docmd.close acform "YourFormName"
End If
rs.Close
Set db = Nothing
Set rs = Nothing

Damon


Rod said:
I am looking to use that invisible character as a pseudo hidden key to open
the form. How can I accomplish this?

strive4peace said:
Hi Rod,

you said:
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255. It
never loads the form, so the DCount must be 0;
it should be 1 and therefore load.
Click to expand...

this is not chr(255), this is a text string that says
"hold_the_alt_key_dn_and_type_255"

what you are looking for, chr(255), is not in this string... that is why
the count is zero...

since it is a memo field, you will not be able to index it. This is
going to be a VERY slow way to mark a key record. While this may be the
way you have the user do it, I would store whether a record is marked or
not in a number field -- all you need is byte, which goes from 0 to 255
-- although I prefer to use the Integer data type, which can hold values
+/- 32K

Warm Regards,
Crystal
remote programming and training

*
(: have an awesome day :)
*
~~~~~~
Learn Access on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal
~~~~~~
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
~~~~~~


This key record will have only char(255) in the comments field, nothing
else.
So, I will not need to traverse the entire memo field. With that in
mind
shouldn't the DCount solution work - you are right, DCount should be
faster
than InStr so that is my preference, but I can't figure out why the
DCount
solution does not work.

:

Hi Rod,

it is looking for the field EQUAL to chr(255), not containing "255"...
you will need to change the condition in that case -- and this will
take
even longer to find:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0"
or, if you want to find either condition:

"InStr([COMMENTS],'hold_the_alt_key_dn_and_type_255') > 0 OR
[COMMENTS]
= '" & Chr(255) & "'"

InStr is in string -- returns the position in the first string that
the
second string was found


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*





Rod wrote:
I have
Private Sub Form_Open(Cancel As Integer)

If DCount("*", "[tblCandidates]", "[COMMENTS] = '" & Chr(255) &
"'") = 0
Then
Cancel = True
End If

End Sub
and record 78076 has [COMMENTS] = hold_the_alt_key_dn_and_type_255.
It
never loads the form, so the DCount must be 0; it should be 1 and
therefore
load.

:

Hi Rod,

you can do something like this on the form OPEN event:

'~~~~~~~~~~~~
if dcount("*","[Tablename]" _
,"[comment] = '" & chr(255) & "'") = 0 then
Cancel = true
end if
'~~~~~~~~~~~~

I would suggest, however, that you add an index to your comment
field to
make this faster. If it is a memo field, you cannot. I would also
recommend you use another field (or another method to determine
permission) as it will be slow to search a long comment field...

you can also set up a query to return records where this condition
is
true and count the query records instead of the table records for it
to
be faster

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*





Rod wrote:
Hello,

I would like to do something like this when the form load is
attempted:
1) Check to see if any of the records [Comment] fields equal the
special
invisible character 255.
2) If found then load and continue normally.
3) If not found I want to halt with no error - just terminate.

Thanks for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top