Record source from two tables (1 Viewer)

[hardik_088]

Hi ,
I am getting problem with record source property.

suppose i have one form that contains 4 combo box and i am trying for cascading combos.

first combobox is country_name from table1
second combobox is province_name from table1

third combobox is department_name from table2
forth combo box is First_name from table2

when i select record source from table1 then first two combo box are getting value but third and fourth i am getting #name error

and when i select record source from table2 then third and fourth working but first two getting #name error. so i listen that you can do by JOIN but how i dont know can anybody tell me how to do?

thanks a lot

 

[MSAccessRookie]

The record source can be any valid Table or Query, so what you will need to do is create a query that JOINs Table1 and Table2 and SELECTs the required Fields from the two Tables.

Hi ,
I am getting problem with record source property.

suppose i have one form that contains 4 combo box and i am trying for cascading combos.

first combobox is country_name from table1
second combobox is province_name from table1

third combobox is department_name from table2
forth combo box is First_name from table2

when i select record source from table1 then first two combo box are getting value but third and fourth i am getting #name error

and when i select record source from table2 then third and fourth working but first two getting #name error. so i listen that you can do by JOIN but how i dont know can anybody tell me how to do?

thanks a lot

 

[Guus2005]

The first table makes sense in a normalization kind of way, the second doesn't.
The firstname and the department name in the same table is a problem if the department changes name, which they often tend to do.

Create four comboboxes. (Combo1 thru Combo4)
Enable the first and disable the rest.
Set the first recordsource to "Select distinct CountryName from table1"
Set the second recordsource to "Select provincename from table1 where countryname = me.combo1"
Set the third recordsource to "Select distinct departmentname from table2"
Set the fourth recordsource to "Select firstname from table2 where departmentname = me.combo3"

You may need to change "me" to the name of your form. example: Forms("frmname").combo1

program the "afterupdate" events behind the comboboxes:

combo1_afterupdate:
me.combo2.requery
me.combo2.enabled=true

combo2_afterupdate:
me.combo3.enabled=true

combo3_afterupdate:
me.combo4.requery
me.combo4.enabled=true

You can add some checks to see if the previous input was valid before requerying and enabling.

Enjoy!