Auto populate Text box from Combo.

[dave ainsworth]

Hi, thanks in advance for any advice or support.

I have an Access database with two tables:

tblProducts
tblDataEntry

In the tblProducts table there are two fields ProductCode and ProductName.

I would like to have a combo box on a form which contains the contents of the ProductCode field, and when selected auto populates the ProductName field with the correct product name. When the form is saved it will write the contents to the tblDataEntry which can be used for reports/querys.

I am quite a beginner to Access so not really sure where to start.

Cheers

Dave.

 

[pbaldy]

You can display the name with a textbox control source of:

=ComboBoxName.Column(1)

You should not save the name in the data entry table, since it can be derived from the product code, which would be saved there.

 

[dave ainsworth]

Thanks for your suggestion.

I have added the combo box onto a form - ProductNo. I have then created a Text Box and set the Control Source as you have said.

When I try entering in different Product numbers using the combo box it does not enter any text into the text box.

Cheers

Dave

 

[pbaldy]

What is the row source of the combo? I assumed it had 2 fields, the second of which was the product name.

 

[dave ainsworth]

Hi Paul

You are correct, the table which contains the Product details has two fields (ProductCode, ProductName).

On the Form I have added the tblProduct.ProductCode field and the other fields from the tblDataEntry, then I have created a text box with the information you have supplied.

When I open the form the text box is empty.

Cheers

Dave

When I use your suggestion on the Form it does not show anything in the text box.

 

[pbaldy]

You say the table has those fields, but does the combo box row source? Can you post a sample db?

 

[dave ainsworth]

Hi Paul

Attached copy of the database.

Cheers

Dave

 

[Alansidman]

Dave,
Go to this site. There are three tutorials on combo boxes. They might help you in the future in using them.

http://www.datapigtechnologies.com/AccessMain.htm

Alan

 

[pbaldy]

As I mentioned, the combo row source needs to include the name field, and it does not. Try this:

SELECT [tblProducts].[ProductNo], [tblProducts].[ProductDesc] FROM tblProducts ORDER BY [tblProducts].[ProductNo];

along with changing the column count to 2 and the column widths as desired.

 

[dave ainsworth]

Hi Paul

Thank you for your assistance with this little problem. I understand the way the form should work now and I have managed to setup the Form to show the information I need.

Thank you sir.

Cheers

Dave

 

[pbaldy]

No problem Dave, and welcome to the site by the way.

 

[james5478]

Hi Dave and Everyone.

I am new to the forum. I have experience with Access and other apps, but haven't had to use it in a while (working more with SQL and other reporting apps). Recently, I have found need to leverage Access again (2010 specifically) and need to build some forms for users to input their data.

The issue I am experiencing is very much like Dave’s-I have a form with some combo boxes that the user can select and I wish to auto-populate some textboxes based on what they select in the combobox dropdown. After they make their selections, I wish to have a command button at the bottom insert the data into the table of record.

The comboboxes are based on queries from a different table which houses all historical data (I want to make sure the dropdowns have all of the appropriate selections) and I want to input that data into the blank table where I am going to track the information from the form.

I have tried using dlookups and some other advice, but to no avail.

Can anyone help? Dave, if you found a solution, would it be possible to share that information/db?

Thanks Everyone!

 

[pbaldy]

Does this help?

http://www.baldyweb.com/Autofill.htm

 

[james5478]

Hi Paul,

Thank you for responding so quick! I actually figured it out a few minutes ago and did exactly what you mentioned in the link.

What I did was create a combo box based on a query (so the list is dynamic and can change when needed based on the data) and pull all fields desired from the query. This was where I was getting a little hung up because I was just pulling in the ID of the field I wanted-not the description. A cool design work-around is to reduce the field size of the adjacent columns to 0, so they won't show in your combo. I then created x amount of text fields I needed and referenced those in the Change Event procedure in the VBE so they would populate based on my selection.

On this note, thanks again Sir.

I did notice something though as a side tanget-when I toggle the combo list (selecting one option or another to test), these selections and the associated text boxes were added to the table (record source) without me clicking the add command button. Is there a reason behind this and what can I do to inhibit this from happening?

Thanks!

 

[pbaldy]

That's the nature of using bound forms. You can use Undo.

 

[james5478]

Hi Paul,

I have another question I thought you might be able to help me with.

On the form in addition to the text boxes populating off of combos, I have a couple text boxes I need to populate based on another text box and one text box that needs to be a concatenation of a text box and a combo.

On the two text boxes that populate based on another text box, I want the user to enter an employee name in the format "LastName,FirstName". I then have two text boxes (txtbox.LastName and txtbox.FirstName) that should pop based on the entry. I need code to parse this out.
I thought about something like the below but I need to account for the null and am running into issues on that one:
Me.LastName = Left(Employee_Name, InStr(Employee_Name, ",") - 1)

Associated, I have another field that should be a concatenation of the LastName and the combo box so I can create a unique identifier to be used later in queries. I have this code associated to an event procedure when they click the combo and enter the lastname, but it doesn't account for a null in the LastName field.
Me.LastName_ID= LastName & "_" & ID.

Thoughts on how I can deal with the nulls?

Thanks Paul!

 

[pbaldy]

Does the Nz() function do what you want?

 

[james5478]

I thought about the Nz() function, but when I tried to integrate it into the code I already have, I ran into issues. Can you give me an example?

Thanks Paul.