Pushing Data From one form to another

[zipmaster07]

Yeah, it's a single view. The form itself is bound to the "lead" table (that's what the form is for, adding a lead) and all the textboxes are bound to fields on the "lead" table. I'll play around with it to see if I was doing something weird. If I can't find anything i'll probably upload the project.

[zipmaster07]

Okay, I think I got it fixed. My button on the first form wasn't passing any data, it was simply opening the form. I put a where clause in the macro and it passes the correct lead_id when opening the second form.

I can't seem to get the subform on Form A to refresh however. There are three tables involved. I have a "lead" table, contractor table, and contractor_lead table. the contractor_lead table has the lead_id and the contractor_id as it's primary key (and then it has other data). When you open Form B the system passes the lead_id that you are currently on to the form and then asks for a contractor_name, which gets translated into the contractor_id. The subform on Form A lists this data you selected from Form B. However, after I hit OK/Select on Form B (which brings you back to Form A) the subform on Form A still doesn't show anything. I have to close the form, reopen it, and find that lead again to see the updated data. I've tried a requery statement in several places but this doesn't seem to work. My guess is that i'm either using the wrong function or I don't have it in the right place to be called at the right time. I essentially just need the subform to pull data in realtime

[June7]

You don't want to enter the data into subform because it is in Datasheet view?

Must requery the subform. Unfortunately, when you do that the cursor will move to the first record of the subform.

Show the code that opens Form B.

[zipmaster07]

Yes it's a datasheet view. It lists the contractors that are associated to a lead (that's what you pick on Form B). And then you can select and deselect the ones that you've chosen from the subform.

Where does the requery go? Would it be after update? When you select the contractor from Form B it closes the form, so you get directed back to Form A (I'm not telling it to go back to Form A, it's just the only thing left open after it closes the other form). I'm fine with it placing the cursor on the first record, each form will only have a handful of contractors, so it will be rare that scrolling on the subform will be needed.

The code that opens Form B is simply a macro with the OpenForm command and that's it:

OpenForm:
- Form Name: Select Contractor
- View: Form
- Filter Name:
- Where Condition: = ="[lead_id]=" & [txtNewLeadId]
- Data Mode:
- Window Mode: Dialog

[zipmaster07]

so I ended up just putting a refresh button on Form A, it servers it's purpose well enough. I do have an additional question though (yes another one ).

When I click my button to open Form B (the macro from the last post), if the lead already has a contractor/contractors then Form B defaults to first one in the list. I want it to default to selecting a new contractor no matter what. In order to do this now, I have to click the new button at the bottom of the form (next to the record navigation buttons and the Filtered button).

[June7]

I don't use macros, only VBA, so don't know how you could replicate this in macro.

I would open Form B in Dialog mode (you do that now with the macro). This suspends code execution by Form A until Form B closes. When Form B closes the next line of code in Form A would be the Requery of subform.

If you want Form B to always open to new record, can set the Data Entry property to yes. Or you can open to new record with an argument of the DoCmd.OpenForm.

[zipmaster07]

Cool thanks, I was able to get it all setup and working correctly.