Full Version: ComboBox Lookup question(s)
UtterAccess Forums > Microsoft® Access > Access Forms
OK, I created a Combo Box which I type in a search for last name and it will display the existing names as I type it in, all well and good. When I see the match I hit Enter and it proceeds to display the found record and the data. If there is more than one match pressing the
enter key brings up the next matching record .. so far so good. Now if there is no match found and I press Enter the form defaults to the first record in the database, which of course already contains data. So, I need the Combo Box to do two things .... If there is no match it needs to bring up what amounts to a New Record and put the Combo Box typed in data into the field LastName then allow me to continue entering the remaining data, such as Address, Phone, etc.
Ideally what I would like is that if the Combo box does not find a match and the user pressed Enter a popup comes up asking if they want to create a new record and if Yes then open up the New Record (blank form) and put the Combo box data into the LastName field.
hew, sorry a little long winded but wanted to be sure to try and cover all the bases on this
Here's a link that can help you with your problem:
web page
Hi ... Thanks for the link. It appears that it deals with NoInList operations and not table record lookup. That being said this is the scenario that I presently have
The database contains over 1000 records. Of this 10 have the LastName of ADAMS
Ocreated a ComboBox using the third option and selected LastName as the default.
Problem number One :
Now when I open the form and type in ADAMS in the ComboBox and press ENTER it brings up the first ADAMS in the database. This is where the problems start. Pressing ENTER the second time drops out of the ComboBox and back onto the main form, rather than bringing up the second ADAMS. it would seem that a press of the Enter key should bring up, in sequence, all the ADAMS in the database until it reaches the end of the records.
It would seem that three things are possible
1 - All ADAMS would be brought up by pressing the Enter key until the last record (see problem two)
2 - The Pulldown would display only ADAMS from the database ... right now it displays every record.
3 - The pulldown is disabled not allowing the user to display a list of possible matches
roblem number Two:
If the data typed into the ComboBox does not match any LastName record, or the EOF has been reached, then it should ask the user if they want to create a new record and if Yes then display a blank form with the lastname field filled with the text in the ComboBox
Phew, sorry, a little long winded but this thing is driving me nuts.
What is happening is that the code the wizard writes is using the FindFirst instruction to land on the first ADAMS in the database. Pressing enter will cause the form to move to the next record in the recordset whether or not it is an ADAMS record.
One way to deal with this is to change the code produced by the wizard, you'll have to open the form in design view to see the code, but change it so that it sets the Filter property of the form, rather than use the FindFirst method of the recordset. I can't write that code for you, but it will be something like this:
Me.Filter = "= '" & Me.NameOfComboBox & "'"
Me.FilterOn = True
NOW the recordset on the form will show just the ADAMS.
You'll also want to put a Remove Filter button on the form, too.
The other, where the user types in a name that is not in the list, is best handled by the Not In List event as I previously posted. You could if you wanted, put an Add New button on the form that opens a form where the user can type in the new record. Then when that form closes, requery the combo box on the first form so that the added record will be included.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.