Select record based on user criteria

sbyrne

New Member
Joined
Jan 8, 2004
Messages
4
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!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

anvil19

Board Regular
Joined
Nov 18, 2003
Messages
230
Hey sbyrne

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

anvil19
:eek:
 

sbyrne

New Member
Joined
Jan 8, 2004
Messages
4
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

Board Regular
Joined
Nov 18, 2003
Messages
230
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:

Code:
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 :

Code:
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
:eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,568
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top