DataCombo control question

[xinyin]

Hi all,
I have a form which shows information of each product. There is also a DataCombo control - I want it to be a product finder - if the user picks a product code in the list, the program will "jump" to the corresponding record. At first I thought this can be done by setting the source of the DataCombo to be Product Code; but in the actual run only the DataCombo itself changed but nothing happens to the record position, now the DataCombo shows the Product Code you pick, while all other controls still display data of the previous product. Then I wrote the following code. Its idea is to match product codes between the recordset and the DataCombo:

Private Sub XDataCombo_Change()
XRecordset.Find ("XRecordset.Fields('ProductCode').Value = XDataCombo.Text", , adSearchForward)
End Sub
(Please beware single-column in the code; documents said that Find method does not allow more then 1 pair of double-columns)

Although I followed the instructions in the reference books about using Find method, this code simply doesn’t work (runtime error 3001). Could anyone please kindly point out the problem. Also can someone tell me SQL can do the job. According to my knowledge of SQL (still very weak) I think SQL can only filter certain record(s) from the recordset but not “jumping” to a certain record?

 

[SDuke]

XRecordset.Find "ProductCode='" & XDataCombo.Text & "'"

If ProductCode is not a text field, then drop the single quotes

 

[xinyin]

Now there is no more error message. But the current record position still has no response to the change of the data combo. Please help!

 

[xinyin]

After more testing, I realize that although

Private Sub XDataCombo_Change()
XRecordset.Find "ProductCode='" & XDataCombo.Text & "'"
End Sub

has no syntax error, but the Find method here is not working at all. For example if there are 2 records having Product Code "ABC" and "XYZ". When you are currently in record #1 then the data combo is showing "ABC"; if you change the data combo to "XYZ", the Find method suppose to find a record having product code "XYZ" (which is record #2), once found then it should MOVE to that record. Now in the real run it just change record #1's product code from "ABC" to "XYZ" (now record #1 and #2 have the same product code as "XYZ"). It means that the Find method had no effect at all.

Can someone please tell me how to change it arround or is there a methord I can use instead of Find? Thanks.