Create Record in 2nd Table when Record Added in First Table (Conditionally) (1 Viewer)

[padlocked17]

Good Evening,

I have a form called "SystemDetailModal" that is bound to a table, "tblSystem".

I have a continuous subform that is bound to "tblEvent" and it allows for Events to be added in connection to it's respective System (parent form), but those Events need to be assigned to a LineOfEffort with a specific LineOfEffortType that the subform is designed around. So when a new Event is added, I would need to check if a LineOfEffort record exists for that System, and if it doesn't, create it for linkage to the Event that is being added where the LineOfEffortType equals a certain value based on the design of the subform.

My table layout looks like this:

tblSystem
SystemID

tblEvent
EventID
SystemID
LineOfEffortID

tblLineofEffort
LineOfEffortID
LineOfEffortCategoryID

tblLineofEffortType
LineOfEffortCategoryID

Any thoughts on how to handle this or a better way to do it? I know I could write some VBA to handle the if/then, but I'd prefer to keep it to SQL or Form design if at all possible. TIA!

 

[JonXL]

If you want to stick with SQL, you should be able to use INSERT with WHERE to add a record to the related table only when one doesn't already EXISTS.

Throw something like this into a search engine and you should get more than enough results to get you to the right place... "ms access sql add record where one doesn't exist".

To reference your fields in the subform, you can use this method right inside the SQL:

Referencing a field in a subform in ms access

in ms access I have a form with a subform. In the subform in a field called "End_Date" in the main form I have a textbox that displays the end date if there is one and then "available" if there isn...

stackoverflow.com

Also, you might need another table just relating systems and lines of effort, but I'm a little unclear on your explanation around how the data relate so can't really say for sure...

 

[arnelgp]

you do not Need to check for the existence of LineofEffort (in tblEvent?).
create a compound index on table tblEvent on :

SystemID
LineOfEffortID

with No duplicate.

now, since your subform is bound (to tblEvent?), you do not need to do any SQL insert, etc.
the form will save the record when you move to another record.

 

[Pat Hartman]

with a specific LineOfEffortType

I'm not sure you need that table at all. Why not just add LineOfEffortType to tblEvent instead of the ID? You can then group tblEffort record by type as necessary.