Select record based on user criteria

[sbyrne]

Hello,

I hope someone can help me with this, I think it should be easy but can't figure it out!

I have a command button that when selected I want it open an input box asking the user to enter a record number, then to go to that particular record.

So far I have the command button linked to a query to get the record number, but can't figure out the code to get the macro to go to the record using the user's input.

Any help greatly appreciated!

 

[anvil19]

Hey sbyrne

Are you using a form or the table when viewing the records?

anvil19

 

[sbyrne]

I'm using a form, and want the user to be able to pull up a different record by selecting the record number....Any ideas?

Thanks!

 

[anvil19]

Me again

If you have a form then you want to alter the forms Record Source to reflect the required record.

You need to add this code to the forms current Event:

Sub Form_Current()
Dim strSQL As String
Dim vara

vara = ComboBox1.Value

strSQL = "Select * From  YOURTABLENAMEHERE Where YOURFIELDNAMEHERE = " & vara

Form.RecordSource = strSQL

End Sub

This action will give your form a single record to view.

If you wish to have access (via the mousewheel or the pageup\pagedown buttons) then this code will do. Add it to the forms code sheet :

Private Sub ComboNUMBERHERE_AfterUpdate()
    Dim rs As Object
    Dim vara
    vara = ComboNUMBERHERE.Value        
    Set rs = Me.Recordset.Clone
    rs.FindFirst "["" & vara] = " & Str(Me![ComboNUMBERHERE])
    Me.Bookmark = rs.Bookmark

End Sub

Try That

anvil19