searching table

[Guest]

There is a situation in which the client uses 2 fields in the table as the
primary key. In a search field (Combo59) the 2 fields are concatenated
together and called FileNumber thru a query. I get a type mismatch error in
the after update event of the combobox. Below is the code that is used and
inorder to do a search, the 2 table fields will need to concatenated together
inorder for "FileNumber" to do the search.

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

I am at a loss as to how to go about doing a search using a concat cbo
against 2 fields in a table and seek assistance. Also, don't be to harsh on
the naming conventions or process as this is coming from a new client that
had their "neighbor" develop this and I am now the lucky one to fix it.

Thank you inadvance for any assistance.

 

[Marshall Barton]

There is a situation in which the client uses 2 fields in the table as the
primary key. In a search field (Combo59) the 2 fields are concatenated
together and called FileNumber thru a query. I get a type mismatch error in
the after update event of the combobox. Below is the code that is used and
inorder to do a search, the 2 table fields will need to concatenated together
inorder for "FileNumber" to do the search.

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

I am at a loss as to how to go about doing a search using a concat cbo
against 2 fields in a table and seek assistance. Also, don't be to harsh on
the naming conventions or process as this is coming from a new client that
had their "neighbor" develop this and I am now the lucky one to fix it.

It would help if you told us what the filenumber field looks
like, its parts and the same for the combo box's Value.

If you have code for the NewFindFirst function, that might
help too (though I suspect the use of a function may not be
necessary).

 

[John W. Vinson]

There is a situation in which the client uses 2 fields in the table as the
primary key. In a search field (Combo59) the 2 fields are concatenated
together and called FileNumber thru a query.

That is neither necessary nor a good idea. FindFirst can use an argument which
is a legal WHERE clause to a query - it could have TEN fields in it. Creating
a redundant concatenated field will do you more harm than good!

Something like

Me.RecordsetClone.FindFirst "[Thisfield] = " & Me.cboFirstField _
& " AND [Thatfield] = " & Me.cboSecondField

should work just fine.


John W. Vinson [MVP]