Write to one form, view associated data from other forms

[AccessNut]

Write to one table from a form, view associated data from other tables on same form

I remember reading on this forum that if you try to access several tables from the same form then you cannot edit any records from the same form.

This is what I need to do:
Use a form as a data entry form for TableA, as well as to show the user what is already in TableA. All of the data entry controls--text boxes, ect, have their control source property set to TableA.

TableB contains other information associated with records in TableA and they are linked by a unique identifier, let's call it AssetID. For each record in TableA there may be one related record in TableB. I am not trying to write to TableB, but I want to see the information in TableB on the form. There are text boxes on the form to show data from TableB but they are disabled from data entry.

Problem: if I show the data in TableB on the the form via a query the form no longer lets me edit. The only thing seems to work is to do a dLookup, but that really slows down the application.

Any suggestions?

 

[boblarson]

Use a Main / Subform form. You should have Table A be the recordsource for the main form and then Table B be the subform's recordsource and then make sure that the appropriate Master / Child links are set to AssetID.

 

[AccessNut]

Thank you for the suggestion. I have done this and another issue popped up. The main form is opened by a button press on another form. When the button is pressed the main form loads but the subform is blank. I put a break in the subform's load and open events and the execution never went there. However, this is the interesting piece, when I start the main form in form view ( vs. opening it with VB code upon button press), the subform is there, opened and loaded, and everything is fine.

Here's the button press event code:


Dim stDocName As String
stDocName = "mainForm"
DoCmd.OpenForm stDocName


Any insights as to what is going on?

 

[boblarson]

That's interesting. I don't see anything in the code that would cause that.

 

[AccessNut]

OOPS I described it wrong. The mainForm is a subform on a ParentForm. And the ParentForm is the one that the button press opens. So when I open the ParentForm or the mainForm by themselves the subform works fine. It breaks when the ParentForm is opened via the button press.