Solved New to Access, little SQL knowledge - cascading combo boxes query (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:30
Joined
May 7, 2009
Messages
19,169
see Query1 in design view.
also see the code on the Form's Current event and on AfterUpdate of combo, cbDevCustID.
 

Attachments

  • test_Jim6.accdb
    1.3 MB · Views: 95

Jimis

New member
Local time
Tomorrow, 00:30
Joined
Feb 15, 2022
Messages
28
see Query1 in design view.
also see the code on the Form's Current event and on AfterUpdate of combo, cbDevCustID.
Thanks that clarifies a lot :)
I guess the Row source doesn't need to be SELECT ....
1645104489642.png

it can be Query1 since the selection is already inside Query1

I have another question if you would like to answer. I want the values of the customer_JoinT to allow only unique combinations of DevCustID and CustERPID. Do I join them both as the primary key or something ,but then how do I refer to the customer_JoinT to connect it to another table if I want ?
 

GPGeorge

Grover Park George
Local time
Today, 15:30
Joined
Nov 25, 2004
Messages
1,776
Hi all
I have a problem setting up a relational cascading combo box. The join table is confusing me on how to do this.
I want the "CustomerERP" combo to populate/depend on whatever the user selects on "Device customer" combo. I have setup query1 which is not doing what I want. My SQL knowledge is basic so I was trying to do it with the Access Query designer. I attach my db

Thanks in advance
The way one should handle a many-to-many relationship is to create a main form/subform design.

The main form is bound to one of the one side tables; in your case that should probably be the customer table, CustomersT.

The subform is bound to the junction table; in your case that should be Customer_joinT.

Use the master/child linking fields of the subform control to filter the records in the subform to show only the records in the junction table where the CustomerID is the same as the CustomerID currently in the main form.

You can use a combo box on the subform to provide the rows for the other FK in the junction table; in your case That would be the CustERPID so that the rows are provided by the CustomersERP_T table.

Here is a link to a properly designed sample accdb illustrating this design.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:30
Joined
Feb 19, 2002
Messages
42,976
If you don't have time to get the data entry right. You have crappy data that you can never depend on AND you have forced yourself into a poor table schema.
 

June7

AWF VIP
Local time
Today, 14:30
Joined
Mar 9, 2014
Messages
5,423
You should start your own thread instead of hijacking an old one, especially one already marked SOLVED. New threads get more attention.
 

Jimis

New member
Local time
Tomorrow, 00:30
Joined
Feb 15, 2022
Messages
28
You should start your own thread instead of hijacking an old one, especially one already marked SOLVED. New threads get more attention.
I thought it's not good to open many threads when there is already another relevant


The way one should handle a many-to-many relationship is to create a main form/subform design.

The main form is bound to one of the one side tables; in your case that should probably be the customer table, CustomersT.

The subform is bound to the junction table; in your case that should be Customer_joinT.

Use the master/child linking fields of the subform control to filter the records in the subform to show only the records in the junction table where the CustomerID is the same as the CustomerID currently in the main form.

You can use a combo box on the subform to provide the rows for the other FK in the junction table; in your case That would be the CustERPID so that the rows are provided by the CustomersERP_T table.

Here is a link to a properly designed sample accdb illustrating this design.
That's my understanding so far, I'm glad I got this right ;)
If you don't have time to get the data entry right. You have crappy data that you can never depend on AND you have forced yourself into a poor table schema.
Data entry will be a pain for me that's why I need to experiment and make sure I understand everything beforehand. Imagine that I have to pull data from an Sage ACT! database 2010 in text format. I will need to do a lot of update queries before going to my final tables format
 

June7

AWF VIP
Local time
Today, 14:30
Joined
Mar 9, 2014
Messages
5,423
I thought it's not good to open many threads when there is already another relevant
No. If you think existing thread is relevant then include a link to it in your question.
 

Users who are viewing this thread

Top Bottom