Passing variable from subform to popup form (1 Viewer)

[David Eagar]

I have a form with a (continuous) subform - against each item in the subform I have a control button to open a pop up form related to that record. All works well except the foreign key in the pop up is not being populated.

I have had this problem before and was solved by setting the default value of the foreing key by [Forms]![3frmSites]![3frmPProductSubsub]![Forms]![prid].

It worked before, but it isn't in the current project. Any clues as to what I am missing here?

Thanks & happy new year

 

[John Big Booty]

Try;

[Forms]![3frmSites]![3frmPProductSubsub][B][COLOR="Red"].[/COLOR][/B][Forms]![prid]

You could also pass this value in the OpenArgs portion of the DoCmd.OpenForm

 

[David Eagar]

Thanks very much for that, but 1st attempt not successful and I'm decidedly dodgy in using the VBA - the example shown there leaves me confused as to what I need to add and what to leave out

 

[John Big Booty]

In the DoCmd.OpenForm, you would put say Me.YourRecordID (or whatever value you wish to pass) in the OpenArgs portion.

Then in the On Load event of the form being called you might put something along the lines of;

Me.RecordID = OpenArgs

 

[David Eagar]

At the risk of sounding ungrateful, any ideas why [Forms]![3frmSites]![3frmPProductSubsub].[Forms]![prid] didn't work?

 

[John Big Booty]

Sorry no. You could try using the expression builder to create the link.

 

[boblarson]

At the risk of sounding ungrateful, any ideas why [Forms]![3frmSites]![3frmPProductSubsub].[Forms]![prid] didn't work?

Yeah, because

1. The second Form part shouldn't have an S:
[Forms]![3frmSites]![3frmPProductSubsub].[Forms]![prid]
Should be
=[Forms]![3frmSites]![3frmPProductSubsub]![Form]![prid]

If it is a control source. (also the control that houses the subform on the main form needs to be referenced and not the name of the subform, so if 3frmPProductSubsub is not the name of the subform control then it needs to be.

If you want to set the value to be stored in the popup then you could use the OpenArgs to pass the value and then in the form's On Load event set the value:

So in the subform control's click event:

DoCmd.OpenForm "FormNameHere", OpenArgs:= Me.prid

And then in the load event of the form:

If OpenArgs <> "" Then
    Me.pid = CLng(OpenArgs)
End If

 

[David Eagar]

Thanks Bob as always, but =[Forms]![3frmSites]![3frmPProductSubsub]![Form]![prid] is till not working for me - I'm baffled by the behaviour of this. As i said it worked once before, but not now

 

[vbaInet]

Is Prid the name of the field? Is the control that is linked to Prid also called Prid?

If the control is called the same name, change it and refer to the control instead of the field.

 

[boblarson]

are you sure that the subform CONTROL is named 3frmPProductSubsub? And JBB's comment about using the builder is a good one for this. Check out this for how to do it.

 

[David Eagar]

[prid] is the primary key of the item I wisk to store. The default value for [prl] is set to =[Forms]![3frmSites]![3frmPProductSub]![Form]![prid] and is where I wish to store it

 

[David Eagar]

If it helps, db attached

 

[boblarson]

Here's the fixed version (I made the control on the Audit form visible just so you can see it populating correctly).

 

[vbaInet]

You had the wrong subform control name there.

You had:
=[Forms]![3frmSites]![3frmPProductSubSub]![Form]![prid]

It should be:
=[Forms]![3frmSites]![3frmPProductSub]![prid]

If you don't make the textbox visible, how would you figure out whether there is an error or not? If it was visible you would have most likely seen #?Name (or something to that effect).

 

[David Eagar]

Thanks ever so much gents - I often wonder why I'm such a bafoon sometimes

 

[vbaInet]

Thanks ever so much gents - I often wonder why I'm such a bafoon sometimes

At least you can see why it wasn't working

 

[ulieq]

I thought this topic could help me solve the issue of putting the foreign key in a popup form, but reading through, then looking at the database, I simply cannot figure why
3frmPProductSubsub
is in any of the expressions. Can someone help me out? Is 3frmPProductSubsub needed to make it work? What is the difference between FORMS and FORM? Thanks.