Many to Many Subforms

[WorkHardPlayHard]

I need a way to add multiple boxes to one truck, as well as I way to split a box into multple trucks.

I can't make it work with subforms because of "the changes you requested to the table were not successful because they would create duplicate.." error.

Currently the subforms show the boxes that belong to each truck, but I cannot add another, and same for trucks to boxes.

I have a many to many relationship with case information and truck information.

frmTruckinfo has a subform with sfrmCaseInfo
and
frmCaseInfo has a subform with sfrmTruckInfo.

The key is to have a way to add boxes to one truck, and a box to multiple trucks. The fashion in which that is done is not important, but I thought I would try subforms.

(Update) I was able to add them boxes to a truck and vice versa, but when I delete them it has an error message "The Microsoft Access database engine cannot find a record in the table <name> with key matching field(s) <name>. (Error 3101)"



Thanks!

 

[rzw0wr]

Sounds like you need to normalize your tables.

Take a look at this.

http://symplebyte.com/microsoft_office/access/how_to_normalize_data_access.html

Dale

 

[jdraw]

As rzw0wr mentions, you may have to Normalize your tables.

I'd like to hear the plain English definition of Truck, Case and Box. They obviously mean something to you and your business, but we know nothing about your business.

What exactly does "split a box into multiple trucks" mean?

What is the unit thing that you are splitting? Can you give us an example so we're on the same page?

From my view, based on your post, you could have 10 Cases of Corn Flakes, 24 boxes per Case and 3 semi Trucks. I can visualize putting 6 cases on1 truck and 4 on another.
But I can't visualize (practically), splitting a "box", so I need some guidance.

The readers can not offer focused advice/suggestions until we understand your situation.

 

[WorkHardPlayHard]

Thanks for the replies!

UPDATE: My tables are normalized. I changed it so my subform data comes from my intermediate table (Currently only the Case# and Truck#) This allows me to add the trucks to cases, and vice versa, BUT if I go to delete the change (i.e. remove the case from the truck) by backspacing it used to come up with an error (in my original post) but that's because my fields were linked to the tblCaseInfo instead of my intermediate tblTruckCasemm. Now the problem is if I backspace to delete it, it won't remove it, it will only delete the case number from the truck, so the truck# is in the list with nothing attached, how can I get it to the whole record?


I will work on normalizing my tables, however I thought they were normalized already.


Anyway, JDraw here is the plain English Version. I tried to simplify it as much as possible for the intial post.


frmHomeScreen has four list boxes: lstTrucks, lstTruckCases, lstVessels, lstVesselCases. When you single click on a truck in the list, lstTruckCases populates with the cases on that truck. When you double click on a case it opens frmCaseInfo. When you double click on a truck in lstTrucks, it opens frmTruckInfo. Every thing is the same for Vessels. However the cases could be on a vessel and a truck. (Shipped from Europe to the US, then transported by truck to destination)


tblCaseInfo has a unique CaseNumberPK (not AutoNumber) and tblTruckInfo has a unique TruckNumberPK (not AutoNumber).


I can't share the exact details of mine, but I will NASA and Cape Canaveral as an example because it's identical to my situation. I will leave out the vessels for this because it will be the exact same as the trucks.

There are 50 truck numbers and 300 case numbers.

Each truck comes from Denver, Houston, San Diego, or Atlanta and ends up in Cape Canaveral, FL.


Case numbers correspond to part, which can be attached to one or many boxes. Each part can be single or multiple, big or small, but is unique to that part and all parts identical to it. For example, a guage for space shuttle cabin is case# 12-43F. It's unique, small and single. However the seats for the SS Cabin, are case# 23-40A, there are three seats, medium size, but identical and unique. There is also sheet metal and paint for the exterior, which are case# 10-12A and 40-12B respectively. the sheet metal is large, and there are several sheets per box, but they are identical and same with paint.


Truck# JPL-12 coming from San Diego has room for 2 of 3 boxes for seats(23-40A), and can fit the guage (12-43F). Truck# DEN-7 has room for half of the sheet metal (10-12A) and the last seat (23-30A). The remaining sheet metal and all the paint will be on truck# HSTN-4.


Say you double click on case# 23-40A from frmHomeScreen, frmCaseInfo will come up saying that those are seats, they are 5 feet by 3 feet by 3 feet, they weight 200 lbs, and it was made in San Diego. Then in a subform it will show that it's on Truck# JPL-12 AND DEN-7.


And If go to frmHomeScreen and double click on truck# DEN-7 it will say that it's a sterile truck (or FlatBed, or FedEx), coming from Denver and left on 6/23/13, and will arrive in CC, FL on 7/1/13. In the subform it will show that on that truck is case# 10-12A and 23-30A (the sheet metal and last seat).


On frmCaseInfo I need to be able to add a case to more than one truck, and on frmTruckInfo, I need to be able to add multiple cases to one truck.

An important note: The number of each case# on a truck is not important. Just there is that case on that truck.


The purpose is to show "Oh, Truck JPL-12 got to CC, FL on 6/25/13 with the seats and guage. But we are waiting for DEN-7 to get here with some of the sheet metal and last seat on 7/1/13."

 

[WorkHardPlayHard]

Okay! I Got it all working...sort of. I can add and delete trucks from cases and cases from trucks. Me new problem arrises when I add a new record.

I need the new record to go to tblCaseInfo, to keep my tables normalized, but I need it in my intermediate table as well so that I can continue to add or delete trucks from cases and vice versa.

I am able to get an Append query to run and add the data to both, however it's kind of speratic. The only constant is that if i delete part of the code it's a part of, it will work.

Here is my code:

Private Sub cmdReturnHome_Click()
resp = MsgBox("Do you wish to save this record?", vbYesNoCancel)
If resp = vbNo Then
Me.Undo
DoCmd.Close acForm, "frmAddCaseInfo"
Else
If resp = vbCancel Then
Exit Sub
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAddNew", acViewNormal, acEdit
DoCmd.SetWarnings True
End If
DoCmd.Close acForm, "frmAddCaseInfo"
End If
[Forms]![fdlgHomeScreen].Refresh
[Forms]![fdlgHomeScreen].Requery
End Sub


I usually comment out DoCmd.Close acForm, "frmAddCaseInfo" first, but if that is already commented out, I comment out the refresh/requery.

Other than that my only problem is a form opening blank (all white) if there isn't a truck or vessel associated with it. Which is a problem, because that's one of the ways I add a truck or vessel to it. (It won't show up in the combobox under the truck or vessel info, because its not associated yet)

Thanks,
Ian