Automating data entry via forms

[moggiemac]

Here's my situation:

I have two tables joined by a linking table.
TBL_Project to TBL_Contract_Match to TBL_Contracts.

The steps in the workflow are:

1) Enter Project information & a Project ID is created as an autonumber.
2) Look up the project ID later and edit / enter any contracts that relate. The Contract ID is unique so it is my PK in the Contracts table.
3) Have the Project ID and related Contract ID written to the Contracts match table as a record when the user fills out the Contract data entry form.

Right now I have a main form with a look up on the Project ID feeding to a subform created based on the fields in the Contract Match table. What I don't have is a way to see any contract information that exists and to write the two ID values to the matching table.

One of my frustrations is that I don't know VBA very well so I'm finding it hard to spot the solution in this forum.

Can any one help me with a lay man's explaination and a solution?

Thanks,
Moggie

 

[cityboy3]

Firstly, I'm assuming that there is a many-to-many relationship between your Projects and Contracts. Otherwise, if a given Contract only relates to a single Project, then you can create a one-to-many relationship between Projects and Contracts, eliminating your match table.

Given that assumption, your subform should automatically be filling in the Project ID in your match table, because if it's used as the linking field, Access will populate it automatically. Now you need to fill in your Contract ID. One way to do this is with a combo box. Create a combo box bound to your Contract ID field. The Row Source for the combo box should be a query based on your Contracts table. This will enable the user to select a contract to relate to the project that is displayed in the main form. Now, if you want to display any other info related to the contract, you can create calculated text fields and use them to look up values from your Contract table based on the Contract ID that the user selected from the combo box. Use the Dlookup function.

hope this helps.

 

[moggiemac]

Hi CityBoy3

Yes the Contract, Contract match and Project tables are in a many to many relationship. Sorry for not being more precise.

I have tried to implement your suggestion but I haven't managed to do it properly so I'd like to describe to you what I've done and then ask for your suggestions.

I've created a Mainform (MFRM_Project_lookup) that looks up a value in TBL_Project. It's using an action button. That works fine.

I've created a Subform (SFRM_Contract_match) based on TBL_Contract_Match and put it on a tab. That works fine and the Project_ID field is populated.

On the second tab, I've created a Subform (SFRM_Contract_Details) based on TBL_Contract.

I've created a Combo Box (Combo11)using the wizard on SFRM_Contract_match that accesses a query that contains only the Contract_ID feild from TBL_Contract. That works.
I've set the row source of the TBL_Contract_Match.Contract_ID control to "=[Combo11]". That works.

When I go to second Subform (SFRM_Contract_Details) and create a contract it appears in the combo box. I can select it and it populates the correct field.

So in theory all should be wonderful. I have the two required fields populated in the Linking table TBL_Contract_Match. When I go to save it how ever I get a message that says:

You an not add or change a record because a related record is required in TBL_Project"

I've double checked and the project ID does indeed exist in TBL_Project.

Can you help me figure out what I've done wrong?

Thanks,
Moggie

 

[cityboy3]

I'm having trouble understanding why you need 2 subforms to get this done. Please explain what you're trying to accomplish. Usually, when you see errors like that, it's because the main form record might not be saved yet when it tries saving the record in the sub-form. I always try to avoid messy form/sub-form stuff for that reason.

Also, what relationships have you set up between the three tables?