Using findnext/seek method with an input box

[blakex1]

Hi, I'm still new to VBA but i've had relative success until now.
My question is in regard to the simplest way to accept a value from a user (via an input box) and use that value to 'find' a record in my table that matches the input value.
I have a table with a field called "courtID" (a string)
It is my primary key field with no duplicates.
I also created a form that displays the data from this table.
I've also created my input box and it accepts the value from the user and stores it into a variable called "InCourtID". Simple so far.
I've tried findfirst...
I've tried using Seek and bookmarks with no success.
I've tried cloning my recordset and can't seem to get it to work. I've tried GoTo as well, and still no success.
Which way would you recommend to accomplish this.
I'm using Access97. So many options makes it difficult to determine how to get there.
I did manage to create a combo box (from the wizard) that lists the possible courtID's and lets the user pick the one they would like to move to. It works with no trouble, and moves to the record that matches the selection.
It has the following code:

Sub Combo82_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CourtID] = '" & Me![Combo82] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

It seems logical that I could use the this same code in my module, and just replace the reference to the combobox with a reference to my variable (the input variable), however, my attempts failed. The module always errors out saying:
"invalid use of me keyword"

Thank you in advance for any suggestions or solutions.
Blake

 

[MichaelRed]

You need to use the 'formal' reference to the form when you are in a "Module". "ME" only works for the "code behind forms" modules (e.g. the events and functions attached to the form itself).

The variable must be declared Public or global.

The RcordsetClone needs to also be avaialble to the "module".

_____________________ OR _________________________

You could place your function in the "general" area of the Form's Module.


In either instance, You need to check the nomatch property of the recordset before setting the form's recordset to the bookmark. Otherwise, someons WILL enter an invalid [CourtID] - and you will display the WRONG thing (or get an error).

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[blakex1]

Thanks Michael,
I moved the code behind the form and changed the syntax a bit to accomodate my input box. I also had to make it a DAO.Recordset. It works great!
Here is the code in case anyone can use it:

Private Sub Form_Activate()
Dim db As Database
Dim rec As DAO.Recordset

Dim INcourtID As String
Dim Tablename As String

Tablename = "CourtSurveyTable"

Set db = CurrentDb()
Set rec = db.OpenRecordset(Tablename)

INcourtID = InputBox("Please enter the CourtID to update", "Choose a courtID&quot

Me.RecordsetClone.FindFirst "[courtID] = '" & INcourtID & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
rec.Close

End Sub