help with Code

  • Thread starter Thread starter Ken Hubbard
  • Start date Start date
K

Ken Hubbard

A while ago, I posted a question about programming an autosearch feature I
wanted to add to a customer list on a subform. I use a double click to
bring up the record for editing, but with hundreds of customers, I wanted to
"On KeyPress" have the record selection go to the box with the first letter
matching the key pressed.

I received this code (below) as an answer, which looks like it will send me
on the right trail, but I don't understand the "[XYZ] LIKE ""x*"""

The ".findfirst" doesn't come up in help and the .findnext doesn't have any
criteria
I would appreciate any help.

with me.recordsetclone
.findfirst "[XYZ] LIKE ""x*"""
if .nomatch then
msgbox "no such record"
else
me.bookmark = .bookmark
endif
end with
 
Ken said:
A while ago, I posted a question about programming an autosearch feature I
wanted to add to a customer list on a subform. I use a double click to
bring up the record for editing, but with hundreds of customers, I wanted to
"On KeyPress" have the record selection go to the box with the first letter
matching the key pressed.

I received this code (below) as an answer, which looks like it will send me
on the right trail, but I don't understand the "[XYZ] LIKE ""x*"""

The ".findfirst" doesn't come up in help and the .findnext doesn't have any
criteria
I would appreciate any help.

with me.recordsetclone
.findfirst "[XYZ] LIKE ""x*"""
if .nomatch then
msgbox "no such record"
else
me.bookmark = .bookmark
endif
end with
Click to expand...


You are supposed to replace the XYZ with the name of the
Field you're searching. The x is apparently just a place
holder for a string variable that contains the character
that was typed. Since that is not known at coding time, it
would have to be written:

.findfirst "[XYZ] LIKE """ & x & "*"""
 
Hate to be a bug, this is the code as I have it entered, I don't understand
the ""& X &" * """", Is the & like a Character used in a Concatenate
statement, and the X a variable? and the * a wild card? I am getting a type
mismatch error, I assume because I am comparing the Ascii value of the key
pressed to the letter?

Private Sub Company_KeyPress(keyAscii As Integer)
With Me.RecordsetClone
.FindFirst "[ID] LIKE ""& X &" * """"
If .NoMatch Then
MsgBox "no such record"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
 
Ken said:
Hate to be a bug, this is the code as I have it entered, I don't understand
the ""& X &" * """", Is the & like a Character used in a Concatenate
statement, and the X a variable? and the * a wild card? I am getting a type
mismatch error, I assume because I am comparing the Ascii value of the key
pressed to the letter?

Private Sub Company_KeyPress(keyAscii As Integer)
With Me.RecordsetClone
.FindFirst "[ID] LIKE ""& X &" * """"
If .NoMatch Then
MsgBox "no such record"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
Click to expand...

Yes, & is the concatenation operator and Asterisk is one of
the wildcard characters.

X is supposed to be replaced by whatever variable contains
the keyboard character. Since the keyAscii value is an
integer character code and not a string, you'll have to
convert it to a character.

Don't add extra spaces inside quote marks, they mean
something there. Also, you removed some of the quotes.
Note that a quote inside quotes is entered as two quotes.

I think this is what you're looking for:

.FindFirst "[ID] LIKE """ & Chr(keyAscii) & "*"""

Now that we've got all that straight ;-) I think you would
be better off staying away from the keyboard events. Your
code does not deal with situations where a user hits some
unexpected keys (e.g. backspace) and it might be a little
more versatile if you used the text box's Change event
instead of KeyPress. The code for this would be:

Private Sub Company_Change()
With Me.RecordsetClone
.FindFirst "[ID] LIKE """ & Company.Text & "*"""
If .NoMatch Then
MsgBox "no such record"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
 
I cant thank you enough. I sincerely appreciate your input.

Marshall Barton said:
Ken said:
Hate to be a bug, this is the code as I have it entered, I don't understand
the ""& X &" * """", Is the & like a Character used in a Concatenate
statement, and the X a variable? and the * a wild card? I am getting a type
mismatch error, I assume because I am comparing the Ascii value of the key
pressed to the letter?

Private Sub Company_KeyPress(keyAscii As Integer)
With Me.RecordsetClone
.FindFirst "[ID] LIKE ""& X &" * """"
If .NoMatch Then
MsgBox "no such record"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
Click to expand...

Yes, & is the concatenation operator and Asterisk is one of
the wildcard characters.

X is supposed to be replaced by whatever variable contains
the keyboard character. Since the keyAscii value is an
integer character code and not a string, you'll have to
convert it to a character.

Don't add extra spaces inside quote marks, they mean
something there. Also, you removed some of the quotes.
Note that a quote inside quotes is entered as two quotes.

I think this is what you're looking for:

.FindFirst "[ID] LIKE """ & Chr(keyAscii) & "*"""

Now that we've got all that straight ;-) I think you would
be better off staying away from the keyboard events. Your
code does not deal with situations where a user hits some
unexpected keys (e.g. backspace) and it might be a little
more versatile if you used the text box's Change event
instead of KeyPress. The code for this would be:

Private Sub Company_Change()
With Me.RecordsetClone
.FindFirst "[ID] LIKE """ & Company.Text & "*"""
If .NoMatch Then
MsgBox "no such record"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
Click to expand...
 
Ken said:
I cant thank you enough. I sincerely appreciate your input.
Click to expand...

You're welcome. It's nice of you to say so and I'm glad to
hear you're making some progress.
--
Marsh
MVP [MS Access]



Ken said:
Hate to be a bug, this is the code as I have it entered, I don't understand
the ""& X &" * """", Is the & like a Character used in a Concatenate
statement, and the X a variable? and the * a wild card? I am getting a type
mismatch error, I assume because I am comparing the Ascii value of the key
pressed to the letter?

Private Sub Company_KeyPress(keyAscii As Integer)
With Me.RecordsetClone
.FindFirst "[ID] LIKE ""& X &" * """"
If .NoMatch Then
MsgBox "no such record"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
Click to expand...
"Marshall Barton" wrote
Yes, & is the concatenation operator and Asterisk is one of
the wildcard characters.

X is supposed to be replaced by whatever variable contains
the keyboard character. Since the keyAscii value is an
integer character code and not a string, you'll have to
convert it to a character.

Don't add extra spaces inside quote marks, they mean
something there. Also, you removed some of the quotes.
Note that a quote inside quotes is entered as two quotes.

I think this is what you're looking for:

.FindFirst "[ID] LIKE """ & Chr(keyAscii) & "*"""

Now that we've got all that straight ;-) I think you would
be better off staying away from the keyboard events. Your
code does not deal with situations where a user hits some
unexpected keys (e.g. backspace) and it might be a little
more versatile if you used the text box's Change event
instead of KeyPress. The code for this would be:

Private Sub Company_Change()
With Me.RecordsetClone
.FindFirst "[ID] LIKE """ & Company.Text & "*"""
If .NoMatch Then
MsgBox "no such record"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
Click to expand...
Click to expand...
 
Back
Top