Many-to-many - Joint table and form

[Jimis]

I must have messed something in my db that gives me a popup window

The sFrm_customer_JoinT works fine on its own but in the subform it seems it has an issue. The value from the cbDevCustID which is asked is supposed to update as soon as the Frm_Assets is loaded and then the combo cbCustERP requeries and updates it's data.

I can't seem to find the problem. The query runs fine as long as I have the sFrm_customer_JoinT loaded on its own

 

[mike60smart]

You based the Combobox for selecting the CustERPID on a Query, the query also had a Form reference to the Combobox, the Form also had a requery of the Combobox for selecting the CustERPID???

I removed all of these referencesz and just based the Combbox on the CustERP table

It now works as it should

 

[Jimis]

You based the Combobox for selecting the CustERPID on a Query, the query also had a Form reference to the Combobox, the Form also had a requery of the Combobox for selecting the CustERPID???

I removed all of these referencesz and just based the Combbox on the CustERP table

It now works as it should

The query is for the cbCustERP combo to populate according to what is set in cbDevCustID combo. You must not delete it

 

[mike60smart]

Jim

What has CustomersERP_T table got to do with CustomersT ???

What you are trying to do is use Cascading Combobox's - this usually means if I select a Customer using the 1st Combobox
then only data related to that selected Customer would appear in the 2nd Combobox.

In your case there is currently no relationship between the 2 tables.

 

[arnelgp]

i think CustomersERP_T table is already the Junction table.

 

[Jimis]

It's the customer_joinT that shows the relationship.
customerERP can have many DeviceCustomers but not the opposite. So when user selects DevCustID it will update the list of possible selectable custERPID. That's why the query is needed

Keep in mind that it works fine if you run the sFrm_customer_joinT. It just doesn't work when it is called as a subform inside Frm_Assets

 

[mike60smart]

This is the Relationship Diagram

CustomerERP _T is NOT the linking Table in the uploaded Database

 

[Jimis]

This is the Relationship Diagram

CustomerERP _T is NOT the linking Table in the uploaded Database

Why is it working on sFrm_customer_joinT when you run it ?

 

[mike60smart]

But it is not working

 

[Jimis]

I don't have an issue with it. When I select a customer I get the correct match below on the CustomerERP

 

[Jimis]

This is funny. I don't get the pop-up window when the sFrm_customer_JoinT is open as standalone which means that the subform inside the Frm_Assests is looking for the combo box data from the form outside not the one inside as a subform. Why is that ?

 

[Jimis]

Any suggestions on how to solve this problem ?

 

[mike60smart]

I don't have an issue with it. When I select a customer I get the correct match below on the CustomerERP

Any suggestions on how to solve this problem ?

Hi Jim
I have made suggested solutions which you appear to ignore completely. I will now bow out and luck with the problem.

 

[Jimis]

Thx for the effort Mike but as I explained your suggestion to delete the query is not helpful for me since that will remove the function that I'm trying to implement.

 

[arnelgp]

your "form" now has become a "subform", so a change on the Criteria on Query1 is necessary.
see Query1 in design view.

 

[Jimis]

your "form" now has become a "subform", so a change on the Criteria on Query1 is necessary.
see Query1 in design view.

Nice ! That was the problem. Thanks a lot

 

[Pat Hartman]

The only reason for having an autonumber in a table is for it to serve as the PK. If you are not going to use the autonumber in the junction table as the PK, it is best to just remove it.

 

[mike60smart]

Hi
Can anyone else confirm that in the database uploaded by arnelgp none of the Forms listed below actually work?

frrmAssets
frmDevices
sfrmCustomerJoin

 

[Pat Hartman]

Yep. They don't work. I guess no one bothered to look at the working example I posted early on.

 

[Jimis]

I downloaded the db and they work fine for me. I got Access 2016

The only reason for having an autonumber in a table is for it to serve as the PK. If you are not going to use the autonumber in the junction table as the PK, it is best to just remove it.

I was planning on changing the relationship of customer_JoinT and AssestsT. It should be the other way around. I will also keep in mind what you said and my change fixes that issue also.

New relationship is this. Removed the AssestsID field from Customer_JoinT.

I think the setup is fine now and all forms work ok. My problem is still focused on how to make the user select combo DevCustID then the CustERPID will populate with Query1 according to what user selected, so the user can select only what is offered. The Problem is that I get an error when I try to input values even though the selections are shown correctly because Access tries to add a new record instead of using the existing.
Here is an example. When I select BIOTRANS customer, I get BIOTRANS AE in customerERP but the problem is that this is added as a new record 111 (above) instead of using the existing one which is record ID 3 in customer_JoinT.

Ofc I wasn't expecting it to be that easy. So I tried to simplify selection by using the ID of customer_JoinT and not the devCustID and custERP separately, something like this below. But it's read only can't actually change it. Note that it's linked with the Frm_Assets