p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Populate a text box from a combo box value (http://p2p.wrox.com/showthread.php?t=53429)

dnf999 January 31st, 2007 08:12 AM

Populate a text box from a combo box value
 
Hi

I'm very new to Access and I need help on populating a text box based on a where clause from a combo box value.

i.e.
textbox.text = select field2 from table where field1 = field1.value (value from the combobox value)

So in summary. When I change the value in the combo box, the text box should have a value based on the combo box value where condition..

Thanks!

David

Thanks!

echovue January 31st, 2007 03:01 PM

There might be a better way of doing this...

Set the recordsource of your combo-box to field1, field2 from the table.

Then set the source of the text box to combobox.column(1) or some similar syntax...

Mike
EchoVue.com

ltdavecass February 2nd, 2007 11:12 AM

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")
    Else
       me.LastName.value = ""
       me.FirstName.value = ""
    End If

    rsTemp.Close
    Set rsTemp = Nothing

End Sub


spider236 February 17th, 2008 12:04 PM

Quote:

quote:Originally posted by echovue
 There might be a better way of doing this...

Set the recordsource of your combo-box to field1, field2 from the table.

Then set the source of the text box to combobox.column(1) or some similar syntax...

Mike
EchoVue.com

spider236 February 17th, 2008 12:08 PM

Quote:

quote:Originally posted by echovue
 There might be a better way of doing this...

Set the recordsource of your combo-box to field1, field2 from the table.

Then set the source of the text box to combobox.column(1) or some similar syntax...

Mike
EchoVue.com
I got your method to work, but I can't get it to update the underlying table. It does not actually create a new record for me.


SerranoG February 21st, 2008 09:39 AM

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.

Me.TextBox = DLookUp("[Field2]", "MyTable", "[Field1] = '" & Me.ComboBox & "'")

I'm using fake names for everything. Substitute your real ones.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

STEFFANI February 3rd, 2012 09:07 PM

One more thing
 
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.

Thanks
Steffani

Quote:

Originally Posted by SerranoG (Post 217828)
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.

Me.TextBox = DLookUp("[Field2]", "MyTable", "[Field1] = '" & Me.ComboBox & "'")

I'm using fake names for everything. Substitute your real ones.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division


SerranoG February 6th, 2012 02:24 PM

Quote:

Originally Posted by STEFFANI (Post 281070)
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.


All times are GMT -4. The time now is 09:12 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.