subforms again

[Gitche Gumee]

I have this code in the Form_Current event of subform [frm Project
Information].

Me.cboProduct = DLookup("Product", "tblProductGroupFunction",
"ProductGroupID = Forms!frmMain![frm Project
Information].Form.[ProductGroupSubform].Form!ProductGroupID")

My problem is that when I close the Main form, I get error 2001 pointing to
this code. How do I get around that?

 

[Allen Browne]

Is cboProduct bound to a field, or unbound?

If unbound, you may be able solve the problem by removing the code, and
putting an expression like this into its ControlSource:
=DLookup("Product", "tblProductGroupFunction",
"ProductGroupID = " & Nz([ProductGroupID], 0))

Or, if you want to use the code:
If IsNull(Me.ProductGroupID) Then
Me.cboProduct = Null
Else
Me.cboProduct = DLookup("Product", "tblProductGroupFunction", _
"ProductGroupID = " & Me.ProductGroupID)
End If

If cboProduct is bound, it probably doesn't make sense to assign a value in
Form_Current.

 

[Gitche Gumee]

I found the problem: I had a requery command in the Close event of the [frm
Project Information] subform. Don't know why....

cboProduct is unbound and is a control on a subform ([frm Project
Information]). I need to assign its value from a subform of the subform.

I notice that the DLookup does not like the
"Me.[ProductGroupSubform].Form!ProductGroupID" reference. It requires the
full reference from MainForm to Subform to SubformSubform. Can you explain
the reason for that?

Is cboProduct bound to a field, or unbound?

If unbound, you may be able solve the problem by removing the code, and
putting an expression like this into its ControlSource:
=DLookup("Product", "tblProductGroupFunction",
"ProductGroupID = " & Nz([ProductGroupID], 0))

Or, if you want to use the code:
If IsNull(Me.ProductGroupID) Then
Me.cboProduct = Null
Else
Me.cboProduct = DLookup("Product", "tblProductGroupFunction", _
"ProductGroupID = " & Me.ProductGroupID)
End If

If cboProduct is bound, it probably doesn't make sense to assign a value in
Form_Current.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have this code in the Form_Current event of subform [frm Project
Information].

Me.cboProduct = DLookup("Product", "tblProductGroupFunction",
"ProductGroupID = Forms!frmMain![frm Project
Information].Form.[ProductGroupSubform].Form!ProductGroupID")

My problem is that when I close the Main form, I get error 2001 pointing
to
this code. How do I get around that?

 

[Allen Browne]

Where is this?

In the main form's module, that should work.

In the subform's module, Me is the subform, and the subform doesn't have
anything on it named ProductGroupSubform.

In the ControlSource of a control, Me is meaningless.

 

[Gitche Gumee]

****
Me.cboProduct = DLookup("Product", "tblProductGroupFunction",
"ProductGroupID = Forms!frmMain![frm Project
Information].Form.[ProductGroupSubform].Form!ProductGroupID")
****

frmMain is the Main form. [frm Project Information] is a subform control on
frmMain. cboProduct is a control on [frm Project Information] and my code is
for the [frm Project Information] module. ProductGroupSubform is a subform
control on [frm Project Information]. In the code above, "Me.cboProduct"
works, but within the DLookup I have to use the full reference. Using
"Me.[ProductGroupSubform].Form!ProductGroupID" does not work.

Where is this?

In the main form's module, that should work.

In the subform's module, Me is the subform, and the subform doesn't have
anything on it named ProductGroupSubform.

In the ControlSource of a control, Me is meaningless.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I notice that the DLookup does not like the
"Me.[ProductGroupSubform].Form!ProductGroupID" reference. It requires the
full reference from MainForm to Subform to SubformSubform. Can you explain
the reason for that?

 

[Allen Browne]

The way you have it, the expression:
Forms!frmMain![frm Project
Information].Form.[ProductGroupSubform].Form!ProductGroupID
is passed as a string to DLookup(), which passes it to the Expression
Service (ES), which looks for the combo on a sub-sub-form. You cannot use
"Me" inside the string that gets passed to the ES, because the ES has no
such context.

It will work differently if you concatenate the value onto the string, as
shown previously.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

****
Me.cboProduct = DLookup("Product", "tblProductGroupFunction",
"ProductGroupID = Forms!frmMain![frm Project
Information].Form.[ProductGroupSubform].Form!ProductGroupID")
****

frmMain is the Main form. [frm Project Information] is a subform control
on
frmMain. cboProduct is a control on [frm Project Information] and my code
is
for the [frm Project Information] module. ProductGroupSubform is a subform
control on [frm Project Information]. In the code above, "Me.cboProduct"
works, but within the DLookup I have to use the full reference. Using
"Me.[ProductGroupSubform].Form!ProductGroupID" does not work.

Where is this?

In the main form's module, that should work.

In the subform's module, Me is the subform, and the subform doesn't have
anything on it named ProductGroupSubform.

In the ControlSource of a control, Me is meaningless.