How to Update two fields bases on selection in a Combo Box?

PROBLEM
I am trying to automatically update the EmployeeNumber and EmployeeNumber fields in a form based on the selection made in the WorePump combo box?

WHAT I HAVE TRIED
1. Watched how to video on line - still got errors, not work
2. Tried modifying this code I found, still got errors, not work

Private Sub txtStore_AfterUpdate()
combo0.RowSource = "Select IP from Vendors where (Store=" & [Forms]![Project Details]![txtStore] & ")"
combo0.Requery
End Sub

3. I tried putting this code  I fould online in the on change event:
me!txtTextBox1 = me!cboComboBox1.column(1)

not work either

HOW TO RECREATE PROBLEM
1. Open application
2. Click the button that says "Sampling Setup"


ANY HELP IS GREATLY APPRECIATED
Silica_App---V5.accdb
Explaination-Image.jpg
cssc1Asked:
Who is Participating?
 
BitsqueezerConnect With a Mentor Commented:
Hi,

that can simply be made by extending the query for the combobox with the additional values you want to use. Number 3 of your tries is correct. You need of course a changed query which includes the two columns. So the combobox needs to be filled with:

SELECT IP, EmployeeNumber, EmployeeTitle FROM... 

Open in new window

You need to change the query in a way which includes these fields if they are not in your "Vendors" table.

Then you only need to adjust the combobox settings by changing the number of columns to "3" and set the additional column widths to "0" in the widths list so they do not appear visually in the combobox. After selecting a value (AfterUpdate event, not Change event) you can then access the columns 1 and 2 of the combobox and transfer the value into the two textboxes.

Cheers,

Christian
0
 
PatHartmanConnect With a Mentor Commented:
Typically, you would not copy data from one table to another.  You would use a query that joins the two tables using the foreign key and that would allow you to pick up the necessary columns from both tables.  I've attached a sample that will show you how to do this with and without copying the data.
FillFormFields.zip
0
 
cssc1Author Commented:
Dear Bitsqueezer,
I tried the suggested modification and the WorePump Combobox does not work and the EmployeeNumber and EmployeeTitle do not populate.

Can you please suggest a correction for what I did wrong, thank you
Display-of-what-I-chnaged.jpg
Silica_App---V5.accdb
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
BitsqueezerConnect With a Mentor Commented:
Hi,

no, I meant the settings of the combobox directly in the form, not in the table design. You should avoid entering comboboxes ("lookup fields") in the table design as you can never see the real value of the field when you look into the table directly as developer. Leave it a normal number field and create comboboxes only in forms.

Cheers,

Christian
0
 
cssc1Connect With a Mentor Author Commented:
So, I add an "Unbound" combo box to the form and use the function to populate the combo box?
0
 
BitsqueezerConnect With a Mentor Commented:
Hi,

no, you can use whatever RowSource you need to fill the combobox. Normally you would save the selected ID value in the target table so the combobox should be bound to the foreign key of the target table. The two text boxes should be unbound and show the additional values only. You can fill them also in the Form_Current event when browsing through the records.

Cheers,

Christian
0
 
PatHartmanConnect With a Mentor Commented:
I gave you a working sample.  Did you look at it?  I should have mentioned that the example gives you three techniques and two of them actually do copy data but again, copying data is unlikely to be correct in your situation.
0
 
cssc1Author Commented:
Thank you for helping me
0
 
PatHartmanCommented:
cssc1, You got a little click happy and selected all the answers including your own.  That means that no one who reads this question will have any idea what suggestion actually helped.  You do us all a disservice by closing questions without either choosing a SINGLE answer or explaining why you selected multiple answers.
0
 
cssc1Author Commented:
I am so sorry, I did not mean to do that. Not to make excuses, but I have several handicaps and I am constantly going in and out of hospitals, therapy and many care aids. Yesterday I had a 3 hour surgery to remove my right ear so the doctor could remove the cartlidge from it and use it in another part of my body. The ear was reattached. My fault, I will try harder. I don't mean to cause anyone any problems or stress. Please accept my apology.
0
 
PatHartmanCommented:
Hope you're feeling better.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.