Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can you use a combo box in a Select dialog? 1
- Thread starter migv1
- Start date
-
1
- #2
migv1
Yes it is possible. The combo / list box can be used for lots of things
- hit the current table to find a specific record or group or records. Example: Customer 101, Customers residing in New Yord.
- hit a related table to to link a parent and child table. For example, link an employee to an employee.
- restrict the list of choices the end user can use for a specific field either by referencing a related table, or by using a "value list". For example, and customer must exist before an invoice can be created, or the credit rating for a customer can be excellent, good, credit watch, hold and in default.
Lastly, you can use a combo / list box to build an event, choose the type of select clause, change the sort order of a record set, etc.
If you need more info, perhaps you can provide some specifics for us to work from.
Yes it is possible. The combo / list box can be used for lots of things
- hit the current table to find a specific record or group or records. Example: Customer 101, Customers residing in New Yord.
- hit a related table to to link a parent and child table. For example, link an employee to an employee.
- restrict the list of choices the end user can use for a specific field either by referencing a related table, or by using a "value list". For example, and customer must exist before an invoice can be created, or the credit rating for a customer can be excellent, good, credit watch, hold and in default.
Lastly, you can use a combo / list box to build an event, choose the type of select clause, change the sort order of a record set, etc.
If you need more info, perhaps you can provide some specifics for us to work from.
- Thread starter
- #3
Thanks for the info! I'm a new user, so please bear with me.
More specifics:
I have a table of companies that includes their addresses, phone numbers, and contact persons. When I create a select query to show specifics for a particular company, the entry [Select company] in the Criteria row of the Company name column causes the query to bring up a dialog box with a blank text box where the user must enter a valid company name.
Is it possible to replace the blank text box with a combo box containing valid company names that the user must choose from?
More specifics:
I have a table of companies that includes their addresses, phone numbers, and contact persons. When I create a select query to show specifics for a particular company, the entry [Select company] in the Criteria row of the Company name column causes the query to bring up a dialog box with a blank text box where the user must enter a valid company name.
Is it possible to replace the blank text box with a combo box containing valid company names that the user must choose from?
migv1
The first aforementioned example does exactly this.
Create an unbound combo box by clicking on the combo box tool and then pointing to a location on the form. When you place the combo box on the form, Access will invoke a wizard. As you go through the wizard, here are some selections to choose to obtain your outcome...
"How do you want your combo box to get its values?"
Select "Find a record on my form based on the value I selected in my combo box".
Next, you select the fields to include. I don't know your table design, but an example would be to select the customer name. (In this example, you do not have to choose the unique code such as the CustomerID). Remember, if you don't get what you want here, you can delete the combo box and re-create it.
On the next screen, you should see a list of values such as the customer names. Note also the check box - "Hide key column". In most cases, when you select the one field for your combo box, behind the scenes, Access will also select the primary key field, and it will hide it. An exception is if you use a CustomerCode which is also the primary key.
Done, just some tweaks left.
There are three key things I stress with combo boxes. Open the form up in design view, and make sure the property window is open. (Right click any where on the form and select the last option on the list - "property").
First, is the row source. Navigate to the data tab on the property window, and select the row source field. Click on the "..." graphic icon to the right to open up the query builder tool. One useful tweak is to sort your field, eg. customer name in ascending order, and close the query builder window. Save.
Below the row source is an important field is the bound column. This is key to tie the combo box to the form.
Second, is the formatting. Navigate to the format tab on the property window and review the Columns width. It will probably depict 0";1" . The 0" means that the first column, i.e. CustomerID, is not seen by the end user. The 1" defines the column width for the second column, i.e., customer name. You can tweak this for visibility.
Along with the column width field is the field column count (should be 2) and list width (will be 1", tweak if required).
Third, is the code. Access created visual basic code, VB, to support objective - select a specific customer record by selecting it from the combo box.
Navigate to the Event tab in the property window. You will see for the event "After Update" the description "[Event Procedure]". Select this field and then select the "..." graphical icon to the right. This will take you to the coding window. You may see something like...
in Access 2000 or 2002, soemthing a little different with Access 97 but does the same thing.
What the code does is takes the information selected in the combo box and selects the first matching record. If the user starts typing in the combo box, Access will auto-complete the text. A useful alternative is to use ALT-DownArrow key to open the list of names so the end user can scroll down to the desired cusotmer. This approach is necessary if there are similar names.
There is more of course, but you have enough here I suspect.
Thanks for the star.
Richard
The first aforementioned example does exactly this.
Create an unbound combo box by clicking on the combo box tool and then pointing to a location on the form. When you place the combo box on the form, Access will invoke a wizard. As you go through the wizard, here are some selections to choose to obtain your outcome...
"How do you want your combo box to get its values?"
Select "Find a record on my form based on the value I selected in my combo box".
Next, you select the fields to include. I don't know your table design, but an example would be to select the customer name. (In this example, you do not have to choose the unique code such as the CustomerID). Remember, if you don't get what you want here, you can delete the combo box and re-create it.
On the next screen, you should see a list of values such as the customer names. Note also the check box - "Hide key column". In most cases, when you select the one field for your combo box, behind the scenes, Access will also select the primary key field, and it will hide it. An exception is if you use a CustomerCode which is also the primary key.
Done, just some tweaks left.
There are three key things I stress with combo boxes. Open the form up in design view, and make sure the property window is open. (Right click any where on the form and select the last option on the list - "property").
First, is the row source. Navigate to the data tab on the property window, and select the row source field. Click on the "..." graphic icon to the right to open up the query builder tool. One useful tweak is to sort your field, eg. customer name in ascending order, and close the query builder window. Save.
Below the row source is an important field is the bound column. This is key to tie the combo box to the form.
Second, is the formatting. Navigate to the format tab on the property window and review the Columns width. It will probably depict 0";1" . The 0" means that the first column, i.e. CustomerID, is not seen by the end user. The 1" defines the column width for the second column, i.e., customer name. You can tweak this for visibility.
Along with the column width field is the field column count (should be 2) and list width (will be 1", tweak if required).
Third, is the code. Access created visual basic code, VB, to support objective - select a specific customer record by selecting it from the combo box.
Navigate to the Event tab in the property window. You will see for the event "After Update" the description "[Event Procedure]". Select this field and then select the "..." graphical icon to the right. This will take you to the coding window. You may see something like...
Code:
Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Me![Combo41])
Me.Bookmark = rs.Bookmark
End Subin Access 2000 or 2002, soemthing a little different with Access 97 but does the same thing.
What the code does is takes the information selected in the combo box and selects the first matching record. If the user starts typing in the combo box, Access will auto-complete the text. A useful alternative is to use ALT-DownArrow key to open the list of names so the end user can scroll down to the desired cusotmer. This approach is necessary if there are similar names.
There is more of course, but you have enough here I suspect.
Thanks for the star.
Richard
- Status
Similar threads
