Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss 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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 31st, 2007, 07:12 AM
Registered User
 
Join Date: Jan 2007
Location: , , United Kingdom.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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!
__________________
Thanks!
Reply With Quote
  #2 (permalink)  
Old January 31st, 2007, 02:01 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old February 2nd, 2007, 10:12 AM
Registered User
 
Join Date: Jan 2007
Location: Portsmouth, VA, USA.
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old February 17th, 2008, 11:04 AM
Registered User
 
Join Date: Feb 2008
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old February 17th, 2008, 11:08 AM
Registered User
 
Join Date: Feb 2008
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old February 21st, 2008, 08:39 AM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
  #7 (permalink)  
Old February 3rd, 2012, 08:07 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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 View Post
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
Reply With Quote
  #8 (permalink)  
Old February 6th, 2012, 01:24 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
Originally Posted by STEFFANI View Post
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.
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count in combo box(display results in text box) mboyisis Access 4 April 4th, 2008 07:08 AM
Populate a combo box from database w/vbscript morituri VBScript 1 March 1st, 2005 06:06 PM
Populate Combo Box phil.t Access 3 November 18th, 2004 02:39 AM
Populate combo box with access db with using ADO neo_jakey Pro VB Databases 1 October 23rd, 2004 10:11 AM
Populate List Box by Combo Box Selection mmcdonal Access 2 June 15th, 2004 12:08 PM



All times are GMT -4. The time now is 11:17 AM.


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