Access VBADiscuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
Let me make sure I understand what you're asking. You have a dropdown based on a query with several fields. When you select an item from the dropdown, you'd like to populate other text boxes on the form with additional information from the dropdown.
Here is how I've accomplished this. Rather than refering to the query behind the dropdown, I simply do a one-time query on the original table. I'm somewhat new at Access, too. (Forced to convert from Visual FoxPro.) There may be a way to do the same thing with the dropdown's query.
This is an Access 2003 example. I don't know if the DAO.recordset type translates to newer or older versions.
For the After Update event on the dropdown add the following code:
Private Sub Dropdown1_AfterUpdate()
Dim dbTemp As Database
Dim rsTemp As DAO.Recordset
'Open connection to current Access database and perform the search
Set dbTemp = CurrentDb()
Set rsTemp = dbTemp.OpenRecordset( _
"SELECT LastName,FirstName FROM Employee_List where " _
& "EmployeeCode = " & Str(me.Dropdown1.value))
'Update other fields if data is found
If rsTemp.EOF = False Then
me.LastName.value = rsTemp("LastName")
me.FirstName.value = rsTemp("FirstName")
me.LastName.value = ""
me.FirstName.value = ""
Greg, thank you that code worked perfectly, although I am not the original poster I have been trying to make this work in my database and now it does.
I only have one big issue.
I have a combobox for the "customers" last name, when selected it autofills the rest of the information about the customer into other fields on the same form. My issue is the database was set up on a fly and the primary key is "order number" so the customers name is listed multiple times. When the user selects the combobox there are hundreds of smiths. Do I have to make a new form with a subform so it's done right or can I just add a new field to somehow limit the customer information to one record? The thought of having to redo the relationships throughout the entire database is killing me.
Originally Posted by SerranoG
Hi, David. Welcome to P2P. Here are two ways to tackle this.
1) Mike is correct that you can populate the combobox with field1 and field2 from the table. You can hide the second column of the combobox (or not). In the combobox's AfterUpdate event, you'd have.
Me.TextBox = Me.ComboBox.Column(1)
This assumes the first column (i.e. Column(0)) has the visible dropdown values, and the second column (i.e. Column(1)) has the desired values.
2) If you have only one column in the dropdown, then you can get the same effect this way.
When the user selects the combobox there are hundreds of smiths. Do I have to make a new form with a subform so it's done right or can I just add a new field to somehow limit the customer information to one record?
I'm not sure of what you're asking. Are you saying that the user selects a last name and then you want to look up and auto-populate the rest of the fields?
If so, doing it on last name only is not enough. To auto-populate, you need to narrow the search so that it finds just one record. To do that, you can only do that for order number.
If you did it via, say, last & first names then you might get one record, but then again that person may have lots of order numbers. If you wanted to pick one, you'd have to choose both a first and last name and then show a subform that lists all the orders for that name (realizing that two different people could have the same first and last names). To differentiate among all users with the same last & first name, the subform could have a column with a customer ID that shows you which "John Smith" is which, for example.
Michigan Dept. of Environmental Quality
Air Quality Division