By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,619 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

How to add a search field that will go directly to the record entered?

P: 49
I have a form with ID, Name, Class and several Scores.
I want to be able to enter the ID and that record comes up so that I can just use tab to enter the scores.
Example: 1024 tab 3 tab 4 tab 3 tab 6 tab 9 tab tab
by entering 1024 I would go to that record and be able to start entering the scores. the last tab would take me back to the ID field for the next record source.
The plan is to never have to use the mouse (in the search at the bottom of the screen).
I figure I will need VBA code OnUpdate or OnEnter to goto the record entered.
Aug 5 '10 #1

✓ answered by Steven Kogan

The control wizard for a new combo box has an option to find a record based on what you select.

Click the combo box control, then click on your form, and if you have the wizards enabled it will prompt you on what to do.

A sample of the code generated is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo6_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Dim rs As Object
  4.  
  5.     Set rs = Me.Recordset.Clone
  6.     rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. End Sub
  9.  
  10.  

Share this Question
Share on Google+
6 Replies


Expert 100+
P: 107
The control wizard for a new combo box has an option to find a record based on what you select.

Click the combo box control, then click on your form, and if you have the wizards enabled it will prompt you on what to do.

A sample of the code generated is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo6_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Dim rs As Object
  4.  
  5.     Set rs = Me.Recordset.Clone
  6.     rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. End Sub
  9.  
  10.  
Aug 5 '10 #2

P: 49
That works great! I added the following so that it would auto advance and clear the search field for the next search:

Expand|Select|Wrap|Line Numbers
  1.     Me![Score1].SetFocus
  2.     Me![SearchID].Value = Null
I'm so new that I haven't used the control wizard (didn't know about it).
Aug 5 '10 #3

Expert 100+
P: 107
Are you setting the combo box value to null? Or your ID field value to null (which would erase your data)?

I'm surprised your combo box name would be ID.
Aug 5 '10 #4

P: 49
I edited the pasted info so it would be understandable.
The combo box is [searchid]. I just fixed it in my last post.
Aug 5 '10 #5

Expert 100+
P: 107
To avoid going to a new record after the last tab, set the 'Cycle' property, on the 'Other' tab of your Form properties to 'Current Record'.
Aug 5 '10 #6

P: 49
That's cool! So much to learn in too little time. Sorry to be such a schmuck. I have to finnish this project by Saturday for a contest.
Aug 5 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.