Compile error when using .FindFirst in Access 97 1

[arthuro111]

The following code which I got from the archives here keeps giving me a "Method or data member not found" error on the rs.FindFirst "pkey = 5"

Private Sub select_store_location_Click()
Dim lPK As Long
Dim rs As Recordset
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
lPK = Forms![StoreAudit]![pkey]
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "pkey = 5"
If Not rs.nomatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

NOTE: I have the autonumber pkey value hardcoded just to try to get this to work, it was

rs.FindFirst "pkey =" & lPK

I've tried a bunch of things with no success. Help please! I have a feeling there's something simple I'm missing.

Thanks,

Arthur

 

[wemeier]

Although it may seem obvious, is the form that contains the subroutine named "select_store_location_Click" bound to a table that contains a field named "pkey"? In order to use RecordsetClone the form must be bound to a recordset (a table or query).

Also, I notice that you refer to Forms![StoreAudit]![pkey] in your code? Is this your way of referencing a field on the current form or are you referencing a different form?


Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]

 

[arthuro111]

Yes, the form that the subroutine is in, is bound (I'm assuming that's equivilant to the Record Source field) to the StoreAudit table, which does contain a pkey field, of type autonumber.

The Forms![StoreAudit]![pkey] refers to this form, and grabs the pkey of the current record.

What I'm trying to accomplish is the following:

There is one pull down which lists city and state. When this is selected, I want to do a requery, which also updates another combobox, which then only pulls records from another table with that same city and state.

This works except that Access always jumps back to the first record.

This subroutine was meant to fix that. What that subroutine should do is save everything, and then put the pkey of the current record into local memory, do a requery, and jump back to the record that was previously being edited.

According to everything I've read the above code should work, do you have any other ideas? (or anyone else for that matter )

Arthur

 

[PHV]

Have you tried to replace this:
Dim rs As Recordset
By this ?
Dim rs As DAO.Recordset
You have to check that the Micdrosoft DAO 3.x Object Library is referenced:
While in VBE, menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

Hi

If you only wish to requery a single combo box, why not do that instead of requerying the whole form? (this will stop the jump back to first record), so you simply say

MyCombo.Requery

instead of

Me.Requery

(using your own control name of course)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Also, shouldn't

lPK = Forms![StoreAudit]![pkey]
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "pkey = 5"

read

lPK = Forms![StoreAudit]![pkey]
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "pkey = " & lPK


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[arthuro111]

Thank you everyone, between all of these posts I was able to do what I needed!