I'm trying to create a form that will consist of 10 fields. On field (1) ("JobID"), I want a user to have the ability to type in a job# (numeric) and have remaining 9 fields fill in the information related to what job# was inputed. I have been told that the best way to do this is with a combo box, but the data table is much to large for this. Thank you in advance for you help.....
Place a unbound textbox on your form (to be used for job number input) and then in the 'LostFocus' event place the following code:
'(all one line)
Me.RecordSource = "SELECT * FROM myGreatBigTable WHERE (([myGreatBigTable]![Field1])=[Forms]![my10FieldForm]![MyJobNumberInputTextBox]));"
OR try this:
Make sure the Record Source property for your form is set to the Table containing the data you seek and the ten display fields in your form have their Control Source properties set to their respective table fields. Place a unbound textbox on your form (to be used for job number input - say called MyJobNumberInputTextBox) and then in the 'LostFocus' event place the following code:
When the user tabs off the input text box the desired record(s) fills in.
Now....rather than placing the code above into the LostFocus event of the job number input box, you may want to place it into the 'KeyDown' event instead so that everything fires when the [Enter] key is pressed (or what the heck...you might want it in both events). For example:
If KeyCode = vbKeyReturn Then
Me.Filter = "[Field1of myTable] = '" & Me.MyJobNumberInputTextBox & "'"
Me.FilterOn = True
Oh what the heck...Fill a combo box and place the code into the KeyDown event for that box (or or a dozen other different ways)
Try this. I think a combo box is still the way to go, however, you can type in a number and press return to display the corresponding record info. You don't always have to display the whole list to choose an item. Try also to set the 'limit to list' property to "yes" and the list size to 1.