How to open a pop-up form and pass key field from current record

[Cutthroat]

Qualifier: My database is used to document field data collected from salmon spawning ground surveys, and yes, we collect biological samples from stinky dead fish. I am also barely familiar w/ VBA so prefer to use Macros whenever possible.



Anyway, I have a main form (frm_SurveyInfo) which contains a subform (subfrm_CarcassDetail) in datasheet view. In the subform I created a field w/ a hotlink to open a pop-up form to record tags and marks (popfrm_TagsMarks) for individual records (carcasses) in the subfrm_CarcassDetail. So, I need to pass the primary key in subfrm_CarcassDetail (Carcass_ID) to the foreign key in popfrm_TagsMarks (Carcass_FK) for the current record so that I can create a record of tags and marks that relates back to the current active carcass record. Please note, it is important to know that I am trying to open the pop-up from an active record while it maintains focus because the hotlink operation works fine when the current active record loses and subsequently regains focus. Again, my specific problem is getting the pop-up form to open w/ the primary key value being passed to create a new related record; I've tried the Requery operation w/out success. Basically, w/ the Requery command in the macro, it returns the Carcass_ID for the first record in subfrm_CarcassDetail instead of for the active record. I've also experienced the exact same issues w/ getting buttons to work properly.

So, I have an OnClick event that runs the following macro when the hyperlink (AddTagsMarks) from the active record in subfrm_CarcassDetail is selected:


SetTempVar
Name tmpCarcass_ID
Expression = [Carcass_ID]

OpenForm
Form Name popfrm_TagsMarks
View Form
Filter Name
Where Condition =="[Carcass_ID]=" & [Carcass_ID]
Data Mode Edit
Window Mode Dialog


Then, I have an OnLoad event in popfrm_TagsMarks as follows:


SetProperty
Control Name Carcass_FK
Property Value
Value =[TempVars]![tmpCarcass_ID]


Clear as mud? Any thoughts on what is missing to make this work properly? Any and all help is welcome and very much appreciated. thx

[ItsMe]

Since it is a pop-up, the original form is likely still open. So you could just reference the subformcontrol from your pop-up form. The following should open a message box and display the value if I typed it correctly.

msgbox Forms!frm_SurveyInfo!SubformControlName.Form!Carcass_ID

[Cutthroat]

Hmm, interesting I think I see what you are trying to do, but like I said VBA might as well be a foreign language to me so I'm not sure where to insert that expression, although from your description it would be somewhere in the pop-up form. So as for the msgbox, where would I input that code? I'm guessing I could also use that code as an expression in the 'Default Value' for the foreign key field (Carcass_FK) on the pop-up form or somewhere under the form properties??? Would that work?

[ItsMe]

Yeah, so you could place all of that code in your pop-up form's module. Within the module you can create a bunch of different event handlers. Maybe the Form's OnLoad event would be one place you could place all of the code. This would work as a demonstration. You just need to change the code to match the name of your objects. For instance, the control that contains your subform object would need to be changed.

msgbox Forms!frm_SurveyInfo!SubformControlName.Form!Carcass_ID

To create a new event handler, you can start by opening your form in Design View. Make sure your property sheet is visible and the Form Object is listed in the puldown(Form) of the property sheet. Make sure you are in the Event Tab. Click on the ellipses(...) next to the appropriate event (On Load). After you click on the ellipses, choose the Code Builder option. The VBA editor will open and your cursor will be in the middle of your Event Handler/Procedure. Paste your code there, in the middle.
.

[Cutthroat]

I couldn't get that code to work and my lack of VBA background left me feeling helpless whenever I tried to insert any type of code, ugh.

Anyway, I'm an idiot. As I pointed out earlier, the fact that the current record is in edit mode (as evidenced by pencil symbol in record selectors) means the data in all fields for that record were in a state of limbo and not yet appended to the appropriate table so that when I opened the pop-up form there wasn't a corresponding value to pass btwn forms. Since everything else worked as expected (see macros in original post) and w/out error when I lost focus on the current record and then re-focused on that record by selecting the hotlink that opens the pop-up form, all I had to do was include a SaveRecord operation in the OnClick event to occur before any other operation - which worked!!!

I really appreciate you taking the time to address my questions, thank you kindly.

[ItsMe]

As you now understand, saving the current record will commit that data to the table(s). Then, it will be available for future queries, including a form bound directly to the table.

I was not trying to deviate from your question. Many folks here that offer help do it best using VBA. If you can create a simple event handler, it will pay dividends now and in the future. When I look at Macros, I am equally overwhelmed as you are when you look at the VBA editor. Let us know how we can help.