One to Many Issue when adding data via Forms.

[erich_18]

Hi All,

I created a small database (personal project) to calculate transportation costs. its real simple, pol+pod combination (called a Lane) is subject to a specific slotrate or a slotcost.

1 lane can only have 1 slotrate or 1 slot cost (the "one" side) assigned to it.
while 1 slotrate or 1 slotcost can have 1 or more lane(s) (the "many" side) assigned to it.

attached capture.jpg file with the access relationship for more details.

When I need to create a Lane I would then need to assign a pre-established slotcost or slotrate to said Lane. I created a form to enter data into the Lanes table, but when I try to assign the new Lane a Slot Rate ID or Slot Cost ID in the Lanes Table I get this error:

"You cannot add or change a record because a related record is required in table 'tblSlotCosts or tblSlotRates"

I've googled this error and found a lot of information, the last suggestion I tried was changing the FK field properties in the Lanes table default value to nothing (removing the 0) and required to "No" but this did not work. I still get the same error.

see attached capture2.jpg



I understand that I'm entering the data into the Child table (tblLanes first), but this is something I need to do. I need the ability to create a Lane based on a pol and pod combination when required and then assign the SlotRate or SlotCost applicable.

any help would be appreciated.

tks

Eric

[June7]

This is a split form? Is the RecordSource just tblLanes?

[erich_18]

Hi June7,

yes this is a splitform with only tblLanes as the recordsource.

tks

Eric

[June7]

Then I don't understand why issue occurs. Selecting item in combobox list should work. If you want to provide db for analysis follow instructions at bottom of my post.

[ArviLaanemets]

Attached is an example of DB how I would do this.
I haven't a clue, for what are all those fields in tables tblSlotRates and tblSlotCosts, and what kind of values are in those fields, so I created dummy tables with same names instead. You can display any number of return values from either of tables on your form (when you add according fields to tables), but there is no need to save them in tblLanes table (or anywhere else) - you calculate them whenever you need them, or you query them.

To keep it simple, I used previously stored values for POL, POD and Owner from tblLanes instead of separate registry tables.

[isladogs]

I have no idea what 'pol' and 'pod' mean though obviously these are locations.
However in order to add a new record to tblLanes, you need to specify both the slot rate and the slot cost values from the respective tables.
From your screenshot it appears that you have only supplied one of these so a new record cannot be added

[erich_18]

thanks everyone. so let me explain the data on the table.

so to move a box from point A (POL) to point B (POD) there's either a rate or a cost (rate = our own truck, cost = buying space from a third party truck) but neither both.

the rates and costs are pre-established and there's only a handful of them:

Rates = (SlotrateID) only 5 different rates (ID'd from 201 to 205)
Costs = (SlotcostID) only 2 different rates (ID'd from 301 to 302)

what determines if a rate or cost is applied? the POL+POD combination (Lane).

when I create a lane I have to tell the database which rate or cost will apply to the new lane.

since this is just a personal project there's really nothing confidential here. I've attached copy of the DB I'm working on. the frmLaneManagement is the form I'm trying to use to create a new Lane and establish if the lane will either use a rate or costs depending on the ID I enter in the table. to keep it simple I'v removed some of the other forms.

[June7]

This is bizarre. Removing relationship between tblLanes and tblSlotCosts allows data entry.

[davegri]

In the realtionship diagram, edit the links and remove the referential integrity.

[June7]

But why is this necessary? tblSlotRates does not have this issue. Referential integrity should be appropriate setting.

[erich_18]

Thanks June7 and Davegri,

June7, it is very bizarre. without tblLanescost it actually works quite well with tblSlotRates. I'll just have to keep trying or figure out how to rearrange the tables maybe if I join both costs and rates into one General Slot table then figure out from there how to make it work.

Davegri, I tried that, and it saves the data in the tblLanes table but does not match the Lane with the Slotcost. Below images: (LanedID 9 should show under tblSlotRates ID # 302 but it doesnt).

[isladogs]

The reason is perfectly simple.
The PK field in tblLaneCosts is autonumber formatted as \300.
So record showing 301 is actually 1 and 302 is 2.
If you try and add either value from the combo it fails because 301 & 302 don't exist in tblLaneCosts.

Similarly if you remove RI from that link, it won't let you restore it.

If you remove the formatting and use 1 & 2, it should work (not tested).
If you need the existing values, do the following:
- delete the link, delete the PK field, replace with number datatype, repopulate manually, restore the link and RI

NOTE Before restoring RI, you'll need to MANUALLY enter values for SlotCostID in the table

After all of this, the form will then work perfectly (tested)

[erich_18]

thanks to everyone who participated in this thread. a lot of your suggestions helped in making my project better.

Ridders52 that solved my issue, I totally overlooked the auto-number configuration I had, despite having gone through the field properties so many times. appreciate it ! thanks.

[June7]

Ahhh, I also missed the formatting. So either don't format or use 1 and 2 as the key values in tblSlotCosts.

Good eye Ridders. Glad is resolved.

[isladogs]

You're welcome.
Out of interest which solution did you go with for that field?