Many-to-many - Joint table and form

[Jimis]

I agree with what you say Pat.
I was trying to keep it simple cause most might have a hard time following my case that's why I tried to be specific to the solution I want to apply so I could get an answer.But since you want to know I'm sending my db striped of most unnecessary stuff.

The main Table is AssetsT then we have CustomersT and CustomersERP. Forget about the rest of the tables. One Asset can have only one
CustomerID which is comprised of the combination of CustomersT and CustomersERP_T (unique combinations). I could just put in AssetsT the two fields instead of creating the 2 Customers tables but an Asset might not have a customer and that would be waste of space, isn't that what normalization is about?

I also want the user to be able to see "Device Customer" and "customerERP" fields from inside the Form Assets and change them via a subform or another way. I used a subform like this.

Let me make a note here. I see a lot of people talking about the "best practice" etc. If I have 10 tables and I want a user to update 1 or 2 fields in each of them do I need to open 10 forms to do that? This might be best practice for the db expert but not for a user(s) who has limited knowledge and time. That's why I want from my Assets form to be able to access customers easily and ofc other fields later the same way.
My understanding so far is that you can get away with tables connected directly, by using subforms to change data (or nested subforms which is not very nice in my opinion), but when it comes to join tables where you deal with IDs that's a problem (my case).

Anyway you can see that I have finally made a button (New) for the user to open a form which manipulates the data in CustomersT and CustomersERP_T and after he has input the new data (if required) then he can select them from the combo boxes above.
By selecting the 2 combos the custID should change (102 above) I tried using a query and update the (linked) custID in AssetsT. I couldn't make this work.

I would appreciate a specific answer to my problem.

Thanks in advance

 

[SHANEMAC51]

I would appreciate a specific answer to my problem.

Thanks in advance

I would do this
- there is a main ribbon form with dynamic search for the value of any field (conditions in green fields, the code is not written)
- - the yellow fields on the form should be protected from correction, they are only for selection
- pink fields for calling subordinate forms (there is a code)

replacing substitution with calling dialog forms to select/add/adjust the required element is a separate conversation

 

[Jimis]

Well this is not the specific answer to my problem that I was looking for.
You are not telling me which is the unnecessary table and why. What exactly are you proposing ?
If we for argument say accept that these are the tables is it not possible to do what I'm trying to do ?

 

[Jimis]

Pat
I was clear about this "The main Table is AssetsT then we have CustomersT and CustomersERP. Forget about the rest of the tables."
If you read my message I explain exactly where my problem is. I can't be more precise than this. I have included my db so you can look at whatever you want.

 

[mike60smart]

Pat
I was clear about this "The main Table is AssetsT then we have CustomersT and CustomersERP. Forget about the rest of the tables."
If you read my message I explain exactly where my problem is. I can't be more precise than this. I have included my db so you can look at whatever you want.

Jim

See the attached.

You are trying to update a Main Form using a Subform. Not the way Access works

 

[Jimis]

Jim

See the attached.

You are trying to update a Main Form using a Subform. Not the way Access works

Mike
I figured that out and I was trying to find a way around it.
What I want to do is let the user select DevCustID and CustomerERP by opening a form or subform, then find the ID (PK) from the table customer_joinT based on his selection, pass the value to a global var for example and then change CustID in AssetsT in the mainForm(Frm_Assets).

I was trying to do it with a subform (with empty master/child links) but I still have a few issues. When I select DevCustID and CustomerERP it's like selecting these values from the table and I don't want to do that because it tries to add new values to the table (any way to avoid that?).

I tried running a query based on the user selections and update a text box/combo box with the ID to pass that to mainform but couldn't make it work

I also tried to populate the combo boxes with a query (with no control source) instead of using their table's field as their control source no luck either.

I could use some help with that. I have no problem using VBA if it's needed.

I attach the db striped of anything unnecessary (the query is not working as it should)

 

[mike60smart]

Hi Tim

Why not just leave the selection of a CustID to the usual Combobox and use that to change as necessary?

 

[Jimis]

I finally did it.

 

[mike60smart]

I cannot for the life of me see how your solution is an improvement on a standard Many to Many.

If you want to go this route then do carry on.

 

[Jimis]

I was not looking to improve any standard.
I was trying to make something easy for the user to handle in a productive manner.
Now he doesn't need to open a form select the right combination of Customer and customerERP remember the ID and input that in mainform. It's all done automatically.