Find Record on Form in Access 2000 Project / SQL 2000 back end 2

[Nickela]

I am currently creating an Access 2000 Project that ties into a SQL 2000 database. I am modeling it after an Access 2000 database that I had originally created (tables in Access). I have a form with a combo box that is populated by a query. Based on the selection in the combo box (in the form header) it finds the appropriate record in the detail section. The code I used to do this in the original is:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[WorkID] = " & Str(Me![Combo48])
Me.Bookmark = rs.Bookmark

In my Access 2000 project (SQL 2000 backend), I know that I need to modify this logic, but I haven't been able to figure out exactly what I need to do. I have been trying to filter the form and using the find command, but I have been unsuccessful in coming up with the correct syntax.

Any thoughts? Thank you very much for your help.

nickela

 

[jbehrne]

Hi,

I have a db that has something like that. It uses an unbound combobox (driven by a query) to let the user select an item and then view it's corresponding record. If you would like a copy send me an e-mail to: jbehrne@hotmail.com

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[BillPower]

Hi Nickela,

Try this:

Dim rs As Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "[WorkID] = " & Str(Me![Combo48])
Me.Bookmark = rs.Bookmark

or, if that doesn't work, this:

Dim rs As Recordset

Set rs = Me.RecordsetClone
rst.FindFirst "[WorkID] = '" & Me![Combo48])& "'"
Me.Bookmark = rs.Bookmark

Bill

 

[Nickela]

Thanks for the help.

I tried both of the suggestions, billpower, but I got a Method or Data Member not found for the FindFirst Method.

Any thoughts?

Thanks for the help,

nickela

 

[BillPower]

While in the Visual Basic Editor you will need to check that Microsoft DAO 3.6 Object Library is installed. To do this, from the Menu select TOOLS> REFERENCES. If Microsoft DAO 3.6 Object Library is not ticked/checked, tick/check it. If it is already ticked/checked, try adding DAO. to Recordset in the line Dim rs As Recordset, so that it looks like this:

Dim rs As DAO.Recordset

Bill

 

[Nickela]

Thanks Bill.

Now I am getting a Type Mismatch (Error 13) on the

set rs = Me.RecordsetClone line

My code is as follows:

Dim Rs As DAO.Recordset

Set Rs = Me.RecordsetClone
Rs.FindFirst "[WorkID] = " & Str(Me![comboboxWorkIssue])
Me.Bookmark = Rs.Bookmark

My References are:
Microsost Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library

Any thoughts?

Thanks again for your help.

nickela

 

[BillPower]

Hi Nickela,

If you zip up your DB and email it to me at billpower@cwcom.net , I'll have a look at it. Make a copy of your DB and remove any sensitive data 1st. I can't reproduce the problem here.

Bill