DoCmd.OpenForm

  • Thread starter Thread starter JGR
  • Start date Start date
J

JGR

I have the 2 following Double Click Private subs:

MainForm
Private Sub BBP_CommNo_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMasterList", , , "[CommodityID] =
Forms![frmBBP_BOM]![BBP_CommNo]" 'works!!!
End Sub

SubForm
Private Sub Part_CommNo_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMasterList", , , "[CommodityID] =
Forms![frmBBP_BOMSubform]![Part_CommNo]"
End Sub

The MainForm works fine but the Subform always ask for
number (Part_CommNo). When I step thru the Macro the value
for Part_ConmmNo is there but it always ask for this
value.

One more question..
Why do I need "[CommodityID] = Forms![frmBBP_BOM]!
[BBP_CommNo]" instead of "[CommodityID] = [BBP_CommNo]"

When I step thru using the shorter of the two it also asks
for the value.

Thanks
 
The first one works, but I would recommend against it. Depending on what
you're doing, this syntax may cause you problems. It is better to just
concatenate the value into the statement. This also should answer your last
question.

Example:
DoCmd.OpenForm "frmMasterList", , , "[CommodityID] = " &
Forms![frmBBP_BOMSubform]![Part_CommNo]

For the second OpenForm that doesn't work, make the same change as above.
Also, your syntax to get to the subform needs to be changed. First, making
the change above should allow you to use the shorter syntax as you mentioned
in your last question. You may need to add the Me keyword.

Example:
"[CommodityID] = " & Me![BBP_CommNo]

If the control is on the subform and this code is running from the subform,
this should be sufficient. However, if you need to use the full path to the
control on the subform, it is:
Forms!frmMainForm!ctlSubformControl.Form!ctlNameOfControl

The ctlSubformControl is a control on the main form, not the subform itself.
This control is what actually contains the subform. To get the name of this
control, open the main form in design mode, open the Properties sheet, and
click on the subform ONE time. The Properties sheet should show the name of
the subform control. If you click on the subform a second time you will be
in the subform, not the control holding it, and the Properties sheet will
show the name of the subform itself.
 
Change the third line in the subform code to:
Forms![frmBBP_BOM]![frmBBP_BOMSubform]![Part_CommNo]"
 
JGR said:
I have the 2 following Double Click Private subs:

MainForm
Private Sub BBP_CommNo_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMasterList", , , "[CommodityID] =
Forms![frmBBP_BOM]![BBP_CommNo]" 'works!!!
End Sub

SubForm
Private Sub Part_CommNo_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMasterList", , , "[CommodityID] =
Forms![frmBBP_BOMSubform]![Part_CommNo]"
End Sub

The MainForm works fine but the Subform always ask for
number (Part_CommNo). When I step thru the Macro the value
for Part_ConmmNo is there but it always ask for this
value.

One more question..
Why do I need "[CommodityID] = Forms![frmBBP_BOM]!
[BBP_CommNo]" instead of "[CommodityID] = [BBP_CommNo]"

When I step thru using the shorter of the two it also asks
for the value.
Click to expand...


I don't know why any of those work, maybe there's something
new in the later versions of Access. The "proper" way to do
this is to concatenate the value of the form control into
the WhereCondition string instead of using a reference to
the control. Assuming CommodityID is a numeric type field:

MainForm
Private Sub BBP_CommNo_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMasterList", , , _
"CommodityID=" & Forms![frmBBP_BOM]![BBP_CommNo]
End Sub

If CommodityID is a Text field, then the value has to be in
quotes:
"CommodityID """ & Forms![frmBBP_BOM]![BBP_CommNo] & """"


If [frmBBP_BOM] is the main form running this code, then you
can use the shorter reference to the control:

in the main form:
"CommodityID=" & Me![BBP_CommNo]
or even:
"CommodityID=" & [BBP_CommNo]

and in the subform, you can use:
"CommodityID=" & Parent![BBP_CommNo]
 
Marshall Barton said:
I don't know why any of those work, maybe there's something
new in the later versions of Access. The "proper" way to do
this is to concatenate the value of the form control into
the WhereCondition string instead of using a reference to
the control.
Click to expand...

I don't think there's anything particularly new about Access accepting

DoCmd.OpenForm "FormName", , , _
"FieldName=Forms!FormName!ControlName"

The form/control reference is evaluated at open time. As far as I know,
Access has been accepting this at least since Access 97.
 
"Marshall Barton" wrote
Dirk said:
I don't think there's anything particularly new about Access accepting

DoCmd.OpenForm "FormName", , , _
"FieldName=Forms!FormName!ControlName"

The form/control reference is evaluated at open time. As far as I know,
Access has been accepting this at least since Access 97.
Click to expand...


Well I'll be dipped ;-) I guess I've never actually tried
to it that way. Thanks for pointing it out Dirk.
 
Back
Top