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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,813
Messages
5,855,782
Members
431,763
Latest member
AlexisChch

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