Runtime error '3464'

[Capt. A. Morgan via AccessMonster.com]

Hi all,

I have a list box [List77] on a bound form and i would like to be able to
click on the info in the list box and have the corosponding record show up in
the form. The field on the form that I am trying to have the list box [List77]
search is [TTS] which is a text field. When I click on the list box I get a

"Runtime error '3464'
Data type mismatch in criteria expression."

The code that I am using is:

Sub List77_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TTS] = " & Str(Me![List77])
Me.Bookmark = rs.Bookmark
End Sub

If anyone has any ideas that could help I would really appreciate it.

Thanks,

A. Morgan

 

[Dirk Goldgar]

Hi all,

I have a list box [List77] on a bound form and i would like to be
able to click on the info in the list box and have the corosponding
record show up in the form. The field on the form that I am trying to
have the list box [List77] search is [TTS] which is a text field.
When I click on the list box I get a

"Runtime error '3464'
Data type mismatch in criteria expression."

The code that I am using is:

Sub List77_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TTS] = " & Str(Me![List77])
Me.Bookmark = rs.Bookmark
End Sub

If anyone has any ideas that could help I would really appreciate it.

Thanks,

A. Morgan

Since TTS is a text field, you need to enclose the value you're
searching for in quotes. This can be complicated somewhat by the
possibility that the value itself may contain the quote character. That
complexity can be alleviated somewhat by the fact that you can use
either single quotes (') or double quotes (") for quoting text values.

If TTS will never contain the single-quote character, you can use this:

rs.FindFirst "[TTS] = '" & Me![List77] & "'"

If TTS might contain the single-quote character but will never contain
the double-quote character, you can use this:

rs.FindFirst "[TTS] = " & Chr(34) & Me![List77] & Chr(34)

If TTS might contain either the single-quote or the double-quote
character, you can use this:


rs.FindFirst "[TTS] = " & _
Chr(34) & _
Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

Incidentally, I notice that you don't have any check afer that FindFirst
to see if you actually found a matching record. Is that because your
list box is drawn from the same set of records as your form's
recordsource, so you are sure you'll find a record? If not, you ought
to check for rs.NoMatch before synchronizing bookmarks.

 

[Capt. A. Morgan via AccessMonster.com]

Thank you for your response,

With your help I am now a step closer. I am having a couple of new problems.

First, when i run the following code I can click in the list box [List77]
and the appropriate record pops up. But when I click a record in the list box
[List77] that is not found the form goes back to the first record. How can I
stop this?

Sub List77_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TTS] = " & _
Chr(34) & _
Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

Me.Bookmark = rs.Bookmark
End Sub

My other problem is that I inserted the following code

If rs.NoMatch Then
MsgBox "No match"
Me.Bookmark = rs.Bookmark
End If

between

Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

and

End Sub

to check for rs.NoMatch before synchronizing bookmarks. But when I click in
the list box [List77] nothing happens. How can i fix this?

Thanks again for all of the help,

A. Morgan

Hi all,

[quoted text clipped - 23 lines]

A. Morgan

Since TTS is a text field, you need to enclose the value you're
searching for in quotes. This can be complicated somewhat by the
possibility that the value itself may contain the quote character. That
complexity can be alleviated somewhat by the fact that you can use
either single quotes (') or double quotes (") for quoting text values.

If TTS will never contain the single-quote character, you can use this:

rs.FindFirst "[TTS] = '" & Me![List77] & "'"

If TTS might contain the single-quote character but will never contain
the double-quote character, you can use this:

rs.FindFirst "[TTS] = " & Chr(34) & Me![List77] & Chr(34)

If TTS might contain either the single-quote or the double-quote
character, you can use this:

rs.FindFirst "[TTS] = " & _
Chr(34) & _
Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

Incidentally, I notice that you don't have any check afer that FindFirst
to see if you actually found a matching record. Is that because your
list box is drawn from the same set of records as your form's
recordsource, so you are sure you'll find a record? If not, you ought
to check for rs.NoMatch before synchronizing bookmarks.

 

[Dirk Goldgar]

Thank you for your response,

With your help I am now a step closer. I am having a couple of new
problems.

First, when i run the following code I can click in the list box
[List77] and the appropriate record pops up. But when I click a
record in the list box [List77] that is not found the form goes back
to the first record. How can I stop this?

Sub List77_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TTS] = " & _
Chr(34) & _
Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

Me.Bookmark = rs.Bookmark
End Sub

My other problem is that I inserted the following code

If rs.NoMatch Then
MsgBox "No match"
Me.Bookmark = rs.Bookmark
End If

between

Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

and

End Sub

to check for rs.NoMatch before synchronizing bookmarks. But when I
click in the list box [List77] nothing happens. How can i fix this?

I think you set up your EOF test wrong. I would write the code this way
(incorporating a couple of other, minor changes):

'----- start of revised code -----
Sub List77_AfterUpdate()
' Find the record that matches the control.

With Me.RecordsetClone

.FindFirst "[TTS] = " & _
Chr(34) & _
Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

If .NoMatch Then
MsgBox "The record you selected was not found."
Else
Me.Bookmark = .Bookmark
End If

End With

End Sub
'----- end of revised code -----

 

[Capt. A. Morgan via AccessMonster.com]

Great, with your help it appears to be working properly. Thanks again for the
help.

Thank you for your response,

[quoted text clipped - 37 lines]

to check for rs.NoMatch before synchronizing bookmarks. But when I
click in the list box [List77] nothing happens. How can i fix this?

I think you set up your EOF test wrong. I would write the code this way
(incorporating a couple of other, minor changes):

'----- start of revised code -----
Sub List77_AfterUpdate()
' Find the record that matches the control.

With Me.RecordsetClone

.FindFirst "[TTS] = " & _
Chr(34) & _
Replace(Me![List77], Chr(34), Chr(34) & Chr(34)) & _
Chr(34)

If .NoMatch Then
MsgBox "The record you selected was not found."
Else
Me.Bookmark = .Bookmark
End If

End With

End Sub
'----- end of revised code -----