Form relationships and sharing data between tables.

[jasdem]

I have 2 tables, one that contains information related to a purchase and the second that contains information regarding bids. I have a one - to - one relationship, as each transaction needs only one set of bids. Users begin by opening a form that allows them to enter purchasing information, a new record is created in the parent table. If the transaction needs bids than users can open a bidding form that allows them to enter vendor information.



**Problem**

When the user opens the bidding form I am having trouble figuring out how to have the bid table take the value of the purchasing ID record and store it in a field. I also want the user to be able to open the bid form with the same button and go to the record related to the purchase ID.

I know that sub-forms do this easily but the bid form is large and adding it to the purchasing form would make it cumbersome. I'd like to keep the forms seperate.

[June7]

Did you consider a Tab control?

Several ways to pass the ID to the bid form. The ID can be passed with OpenArgs argument of DoCmd.OpenForm. Then code in the bid form Current event can grab the ID:

If Me.NewRecord Then
Me.PurchaseID = Me.OpenArgs
End If

or the code can reference the purchases form:

If Me.NewRecord Then
Me.PurchaseID = Forms!PurchaseForm.ID
End If

[jasdem]

I was using the second and able to populate the purchase ID in the bid form. What I need to have happen is when that id is brought forward to the bid form, it is saved with the rest of the bidding information in the bid table.

[June7]

So this is solved?

[jasdem]

No not entirely, I can bring the value forward but the value doesn't save to the other table.

[June7]

The form is bound to table?

I know both techniques can work. I don't know your db so can't be more specific.

If you want one button to serve to open existing record (if exists) as well as new record option, then code needs to first check if ID exists in bid table and open form appropriately - either with filter criteria or to new record. This means an If Then Else code structure.

I presume not every purchase will have bid?

[Missinglinq]

In Form Design View, on the second Form

• Right-Click on the Control that will hold the passed PurchaseID
• Click on Properties
• Go to the Data Tab
• What is in the Control Source Property?

Linq ;0)>

[jasdem]

The ideal is that the user opens the purchasing form (bound to purchasing table) and creates new record. If bids are necessary, the user hits button that opens the bid form (bound to bid table) and creates new bid record. When new bid record is created purchasing ID is saved with bid record on bid table. Any time the user returns to the purchasing record and opens the bid form, the correct bid record is opened. The problem has been that I cannot get the purchasing ID to stick to the bid record. I could have the user manually enter the information in the table, but I know that this process can be automated. I appreciate the help.

[June7]

I don't know what 'cannot get the purchasing ID to stick' means - error message, wrong results, nothing happens? What code have you attempted? We need something to analyze. The suggested code methods are proven.

How could the correct bid record be opened when purchasing record is revisited if the ID is not 'sticking'?

Did you do what Linq suggested?