bookmarking a concatenation

[JohnE]

I am working on an old db (so go easy on the criticism) and have a form that
has a cbo on it that is used for searching using a file number. The file
number is composed of 2 fields (FileLetter and FileNo) from the Client table.
These are concatenated in a query (shown below)

FileNumber: [FileLetter] & Format([FileNo],"0000")

and displays fine within the cbo. I then have in the after update event the
following lines of code;

Me.RecordsetClone.FindFirst NewFindFirst("[FileNumber]", Str(Me![Combo59]))
Me.Bookmark = Me.RecordsetClone.Bookmark

that would normally go to the selected record. Unfortunately, this one does
not and I am at a loss trying to figure out why. I suspect it has to do with
FileNumber as not being an actual field.

So I am asking for help on getting this to work correctly.
Thanks for the response and help on this matter.
.... John

 

[Baz]

Is it FileNumber or FileNo? You appear to be using both.

If that isn't the problem, then at the very least you will need to post the
code for the function NewFindFirst.

 

[John W. Vinson]

I am working on an old db (so go easy on the criticism) and have a form that
has a cbo on it that is used for searching using a file number. The file
number is composed of 2 fields (FileLetter and FileNo) from the Client table.
These are concatenated in a query (shown below)

FileNumber: [FileLetter] & Format([FileNo],"0000")

and displays fine within the cbo. I then have in the after update event the
following lines of code;

Me.RecordsetClone.FindFirst NewFindFirst("[FileNumber]", Str(Me![Combo59]))

Include three columns in the combo box - your concatenated FileNumber,
FileLetter, and FileNo. I'm not familiar with NewFindFirst but you can just
use the builtin function. Assuming that your combo's query has FileNumber,
FileLetter and FileNo in sequence, and that FileNo is a number datatype,

Set rs = Me.RecordsetClone
rs.FindFirst "[FileLetter] = '" & cboFile.Column(1) & "' AND FileNo = " _
& cboFile.Column(2)
If rs.NoMatch Then
MsgBox "File not found"
Else
Me.Bookmark = rs.Bookmark
End If

John W. Vinson [MVP]