Adding a new record from form

[Thompyt]

I am trying to get a new record when opening a form from another form to use as a reference ID in order to update that referenced ID table and other tables in the process. I have tried setting the data flow in both directions for multiple ways. In some the Airfare would be Name? FLID is the same. I Know it's some simple thin. Yes. I know you don't store calculated values in the table, but it's a trouble shooting thing, like the CON_ID/FLID/BAGID fields visible on the forms.



Basically, I fill out Form 1 FrmProjCosts and select Add flight button to add flight info. The new form 2 FrmAirAvg opens to input data # of Bags, Flight Cost and the Airline. I would want to store the Airline, Airfare and # Bags in TbleFlights, that would later show on Form 1.
TVLTest.zip
TVLTest.zip

[davegri]

I think your table design is wrong.
See if this makes more sense and is easier to work with:

Attachment 52324

[Thompyt]

davegri,
I tried without avail.



TblBag has 3 Bag entries as there are norms for the Airlines to charge different amounts for the first/second/third and beyond baggage. I had the airline included in TblBag as the carrier would coincide on the same row as the bags and you could get the results from that. I gather ina roundabout way that The carrierID is connected to the BagID by: TblBag.CARRID to TblFlights.CARRID across to TblConsolidate.FlightID across to TblConsolidate.BagID to TblBag.BagID. TblBag is only to be read and not to add data. I am using TblConsolidate as the Junction table. I think the difference was my one-to-many relationships was that I did not have the relationships enforced. ANd the placement of BagCost, ExcessBagCost, and BagNum. I believe that your BagTag was for BagNum and BagVal was the sum of the Bag Costs. In the above you have TblFlights.CONID, so I am thinking that was where you would have TblConsolidate.CONID one-to-many relationship?

Thus, the need for the Junction Table? TblJUNC now adds to the complexity and I cannot add to the table through the forms. This is what I have at this point.

[davegri]

In my design, TblConsolidate is removed and replaced by the junction table and CONID goes away.
TblBag would have a record for each bag.
However, if you want to go the bag1, bag2, bag3 in a single record, you don't need the bag table. You could just move your TblBag fields to the junction table.

[Micron]

I think there's been a mistake of some sort. I didn't post anything in this thread (or did I and it was deleted?). Sure does not look familiar.

[Thompyt]

@ Micron, you are correct. I called davegri your name. I apologize to davegri and will correct.

[Thompyt]

This was the final working output with the VBA in the background.

Thank you davgri