Displaying values from Tables using ComboBox - Reg

[manthiralaya]

Dear All,

This is regarding getting values from different table.

I have three Tables named Table1, Table2, Table3.
Fields of Table1 are ID (Primary), MDate, Name, PerID
Fields of Table2 are ID (Primary), MDate, Position, Address
Fields of Table3 are ID (Primary), MDate, City, County

I have a form contiains MDate as ComboBox named cmbMDate and Name, PerID, Position, Address, City, County as TextBox named txtName, txtPerID, txtPosition, txtAddress, txtCity, txtCounty respectively.

In the form, I have made the following setting in the MDate ComboBox properties to display the values from the Table1: (and it displays the values of ‘MDate’ from the Table1)
RowSourceType=Table/Query
RowSource=Table1
ColumnCount=4
ColumnWidths=0cm;4cm;0cm;0cm
BoundColumn=1

I made the following AfterUpdate() even procedure for cmbMDate. (and it too works well to display the Name and PerID)

[FONT=Arial]Private Sub cmbMDate_AfterUpdate()[/FONT]
[FONT=Arial]txtName = cmbMDate.Column(2)[/FONT]
[FONT=Arial]txtPerID = cmbMDate.Column(3)[/FONT]
[FONT=Arial]End Sub[/FONT]

Now my question is how should I modify the code or linking table or relationship or something else to get the values displayed for txtPosition, txtAddress, txtCity, txtCounty textboxes.

I have also attached the image of table and the databse for your reference.


Thanks in advance for your kind help.

Regards,
manthiralaya
View attachment ForForum251010.zip

 

[namliam]

Make a 'normal' query to link your data and use that as source for your combobox, then it should be easy going from there on in.

 

[manthiralaya]

Thanks MailMan.....

I tried the same as you said. It works well. Thanks for that.

However, then I tried to do make query with 100 fields in Table1, 80 fields in Table2 and 90 fields in Table3.

The error says that table are not related. I have attached the image of the error displayed.

I would be grateful if you advise how to relate the table in the example database which I had attached earlier. In my example database, I want to make the 'MDate' is same for all three tables.

Please suggest to get this.....

Thanks in advance.......

Regards
manthiralaya

 

[namliam]

SELECT Table1.*, Table2.*, Table3.*
FROM (Table1 
INNER JOIN Table2 ON Table1.ID = Table2.ID) 
INNER JOIN Table3 ON Table1.ID = Table3.ID;

Seems to work just fine
# of fields shouldnt factor into this though 270 fields in just 3 tables really does sound like a design issue ....