Auto update combobox

[omegads]

Good day, today i come with yet another noob question.

1 have 3 fields in access on a table called engineers




i have two combo boxes, one showing employee number and the other showing the engineer name.... both can be filled using the Engineers table as source and in row source i use this to display the names on a drop down list

SELECT Engineers.Employee_Number FROM Engineers; for the employee number on the drop down list
SELECT Engineers.Employee_Name FROM Engineers; for the employee name on the drop down list.

i would like to know if there's a way to use those lists and auto fill each other... what i mean is having the above to give me each drop down lists, if i choose one employee number on the first box, it automatically puts the corresponding employee name on the second box, and then vice versa if i choose an employee name first on the second box, it automatically puts the corresponding employee number on the first box

i hope the way i explain it is not very confusing. if so please tell me to try and explain myself better

Best regards to all on the forum

[CJ_London]

easy to do

in the cboEmpNumber combo after update event put

cboEmpName=cboEmpNumber

and in the cboEmpName combo after update event put

cboEmpNumber=cboEmpName

[omegads]

i did not work

[CJ_London]

just realised your rowsources are incorrect - they should be

SELECT ID, Engineers.Employee_Number FROM Engineers
SELECT ID, Engineers.Employee_Name FROM Engineers

[omegads]

nope if i put the rows you mentioned, i returns me the id, 1 to 11 and not the employee number or employee name

[CJ_London]

do you know how comboboxes work?

in this case your column count should be 2 and the column widths 0

[omegads]

i'm sorry, like i told before i'm a noob to access, i have like 2 days working with it.. this is the firs time i start a project using formulas and vba on access, what i'm doing is on the go of what i find over the internet.

thanks anyway
regards

[CJ_London]

did you try what I said you needed to do in this case? - column count should be 2 and the column widths 0

[omegads]

Hi again,

Yes i tried, i also created a new database just to try what you told me.

1 Created a table with default naming created 3 record just for testing


2 created a form according to the records using combo boxes for field1 and field2


3 Formated field1 with the setting you gave me

* Control Source: Field1
* Row Source: SELECT Personal.ID, Personal.Field1 FROM Personal;
* Row Source Type: Table/Query
* Properties to combo box
* added the VBA code to the after update event

Private Sub Field1_AfterUpdate()
cbofield2 = cbofield1
End Sub

at the end i have the listing of field1 from the table

[omegads]

4 Formated field2 with the setting you gave me

* Control Source: Field2
* Row Source: SELECT Personal.ID, Personal.Field2 FROM Personal;
* Row Source Type: Table/Query
* Properties to combo box
* added the VBA code to the after update event

Private Sub Field2_AfterUpdate()
cbofield1 = cbofield2
End Sub

at the end i have the listing of field2 from the table


now returning to the form, when i choose a employee number on field1, field2 stays blank and does not auto update with the corresponding name to the employee number


and when i choose an employee on field2, field 1 stays blank and does not auto update with the corresponding number to the employee


i don't know what's wrong, possibly is something i'm not seeing, like i told you, i'm new and don't have to much experience on how all access works, i just know what i find over the web.

Regards

[CJ_London]

there should not be a " in your column widths

Have you included the code in each combo after update event

Also both combo's need to be unbound - from your contro names I suspect they are not

see attached example db

dblcombo.accdb

[omegads]

ok managed to do it, i really don't know why the " is after the 0, but looking at yours it has 0";1", why did you put the 1?

i checked your code against mine an noticed that i had

Private Sub Field1_AfterUpdate()
cbofield2 = cbofield1
End Sub

and you had

Private Sub Field1_AfterUpdate()
field2 = field1
End Sub

the problem was the "cbo" before the the name of the fields

thank you very much, now my database is working like a charm.

[CJ_London]

looking at yours it has 0";1"

strange - I see 0cm;2.54cm

perhaps something to do with different languages

and code wise I have

Option Compare Database
Option Explicit

Private Sub Combo3_AfterUpdate()
Combo7 = Combo3
End Sub

Private Sub Combo7_AfterUpdate()
Combo3 = Combo7
End Sub

But glad we got there in the end

[omegads]

can i ask you one more thing,

i just noticed something, i'm using that coding to populate a form right, and then those values are stored in a second table named "LOG table", when i do the above it displays the number and the name on the form as we expected to do, but when i look into the fields on the log table, i see the ID number instead of the number and Name...

Do you know whats happening?

[CJ_London]

not without knowing your table structure - field names and datatypes. My guess is it is storing the ID (which I expect it should do). Tables are for storing data, not for viewing - which is what forms and reports are for