I am creating an inventory database and was wanting to know how to make a combo box or any box auto populate information from tables. I would like to have a combo box so that you can do a search inside of it and find the necessary product, select it, and all the product specs will be displayed. I have created the combo box with this information, but no luck getting it to populate on the form itself. Thanks in advance for your help.
Making a combo box auto populate
- Thread starter kslezak
- Start date
FoFa
Registered User.
- Local time
- Today, 08:54
- Joined
- Jan 29, 2003
- Messages
- 3,672
If I understand correctly, you can set the control (I assume 1 or more text boxes) to do a DLOOKUP function based on the value selected from the combobox. Generally on the click event of the combobox I will then requery those controls so they update with the new selection from the combobox.
Summerwind
Registered User.
- Local time
- Today, 06:54
- Joined
- Aug 25, 2005
- Messages
- 91
I love combos and use them all the time to do what you are asking. I should tell you that I never use bound forms.
To do what you want, have 2 or columns in the combo (I usually have 2) with the first column hidden. The first column contains the key field in the underlying table, the second the description of your invoice item.
When the form opens, or the combo receives the focus run some code like this:
Dim StrSource As String
strSource = "SELECT KeyField,ProductName FROM MyTable ORDER BY ProductName"
With Me.MyCombo
.RowSource = strSource
.SetFocus
.Dropdown
End With
At the appropriate time the combo will drop with all of your inventory items showing in alphabetic order. If the part you want isn't showing, typing in the first few characters will bring it up.
In the code for the AfterUpdate event of the combo, create a recordset of the data you need for your form based on the key nymber of the inventory item you have chosen. Then put the relevant data into the text boxes.
To do what you want, have 2 or columns in the combo (I usually have 2) with the first column hidden. The first column contains the key field in the underlying table, the second the description of your invoice item.
When the form opens, or the combo receives the focus run some code like this:
Dim StrSource As String
strSource = "SELECT KeyField,ProductName FROM MyTable ORDER BY ProductName"
With Me.MyCombo
.RowSource = strSource
.SetFocus
.Dropdown
End With
At the appropriate time the combo will drop with all of your inventory items showing in alphabetic order. If the part you want isn't showing, typing in the first few characters will bring it up.
In the code for the AfterUpdate event of the combo, create a recordset of the data you need for your form based on the key nymber of the inventory item you have chosen. Then put the relevant data into the text boxes.
I am not quite that familiar with Access as well as the programming necessary. I will possibly have to find an alternate route to make this work or simply do something different. If their are any other ideas, please let me know. Thanks.
auto populating a combo box
I am following Summerwind's example below to auto populate a combo box.
The fields I have on my form are:
cboCompany
cboLocation
cboCompany is based on tblCompany( with fields CompanyName, Location)
When I update the cboCompany field, I want the cboLocation combo box to populate with all locations for the company that was entered into the cboCompany combo box on the form. E.g. The company "Visions" may be exist in the tblCompany with 3 different locations: Visions (Las Vegas), Visions (Ohio), Visions(California). When I pick the company "Visions" from the cboCompany combo box, I want the cboLocation combobox to be populated with "Las Vegas", "Ohio", and "California".
The code is as follows but returns empty records in cboLocation:
Can anyone help with this...thank you.
I am following Summerwind's example below to auto populate a combo box.
The fields I have on my form are:
cboCompany
cboLocation
cboCompany is based on tblCompany( with fields CompanyName, Location)
When I update the cboCompany field, I want the cboLocation combo box to populate with all locations for the company that was entered into the cboCompany combo box on the form. E.g. The company "Visions" may be exist in the tblCompany with 3 different locations: Visions (Las Vegas), Visions (Ohio), Visions(California). When I pick the company "Visions" from the cboCompany combo box, I want the cboLocation combobox to be populated with "Las Vegas", "Ohio", and "California".
The code is as follows but returns empty records in cboLocation:
Code:
Dim StrSource As String
StrSource = "SELECT Location FROM tblCompany WHERE tblCompany.CompanyName Like " & Me.cboCompany.Column(1)
With Me.cboLocation
.RowSource = StrSource
.SetFocus
.Dropdown
End WithCan anyone help with this...thank you.
