Insert Statement Problem

[Happy2]

Hi Everyone,

I have a problem that need your help please.

I have a main form links with sub form by LicenseID.

I have a table called tblLicenses. This table has LicenseID as the primary key and AgentID as the secondary key. One Agent can have licenses in many states and one state has many loan programs. For this reason, I created another table called tblLoanProgram with ProgramID as a primary key. LicenseID from the tblLicenses table is a secondary key in the tblLoanProgram.

When an Agent is approved to do a certain loan program, that loan program is approved for all states that Agent is licensed in. An approved loan program has a loan type and an approved number. I have a problem that when a user assigns approved loan program to an Agent, I have to copy the first record and paste to all the rest of states that Agent is licensed in. I don’t have much experience about it. I am stuck on it for couple days already. Can anyone help me as soon as possible I would very appreciate your help.

Thanks!

 

[montrose]

Happy2, "One Agent can have licenses in many states and one state has many loan programs"-you may need to modify your tbl structure. Access cannot resolve many-to-many relationships until you break them down into one-to-many relationships. This is done through the use of a junction/resolver/relationship tbl. Example:

tblLicense
LicenseID PK
other fields describing license

tblAgent
AgentID PK
other fields describing agent

trelLicAgent (junction/resolver/relationship tbl)
LicenseID FK, tblLicense
AgentID FK, tblAgent

Do that for all your many-to-many. The 'insert' will happy when you design your queries and forms, using the relationship tbl in each of your forms/subforms, etc. HTH, Montrose Learn what you can and share what you know.