please help, second request

  • Thread starter Thread starter wayne zabel
  • Start date Start date
W

wayne zabel

I currently have a working database with forms and subforms. My problem is
in the coding to enable me to display a subform. The application is for
equipment, which is displayed on form1, and equipment components which are
displayed on a related subform1. I have a need to doubleclick on field in
the subform1 and have subform2 popup and display detailed information
relative to just the item appearing in the field I just doubleclicked.

I have attempted to use a variable from subform1 as a filter for subform2
but can't seem to get the syntax right.

The data in the field I click is text data. My code is as follows:

Private Sub Part_ID_DblClick(Cancel As Integer)


Dim stPartID As String

Set stPartID = Forms![equipment list recipe]![recipe ingriediants
subform]!Form![part id]





DoCmd.OpenForm "parts table subform", acNormal, "", "[part id] = stPartID"



End Sub

All help will be most appreciated.
 
I currently have a working database with forms and subforms. My problem is
in the coding to enable me to display a subform. The application is for
equipment, which is displayed on form1, and equipment components which are
displayed on a related subform1. I have a need to doubleclick on field in
the subform1 and have subform2 popup and display detailed information
relative to just the item appearing in the field I just doubleclicked.
Click to expand...

A Subform and a popup form are two QUITE DIFFERENT THINGS. This isn't
really a "subform"; a subform is displayed in a subform control on a
form, and you don't - can't - open it. It's just right there on the
form.
I have attempted to use a variable from subform1 as a filter for subform2
but can't seem to get the syntax right.

The data in the field I click is text data. My code is as follows:

Private Sub Part_ID_DblClick(Cancel As Integer)


Dim stPartID As String

Set stPartID = Forms![equipment list recipe]![recipe ingriediants
subform]!Form![part id]
Click to expand...

Remove the word Set from this line. Set applies to Object variables -
strPartID is just a string variable, not an object.
DoCmd.OpenForm "parts table subform", acNormal, "", "[part id] = stPartID"
Click to expand...

And here, you're searching for part id's equal to the text string
"stPartID". There won't be any of course. Instead, concatenate the
*value* of stPartID into the WhereCriterion string:

DoCmd.OpenForm "[parts table subform]", acNormal, , "[part id] = " &
stPartID

Note the brackets around the form name and the consecutive commas
rather than passing an empty string.


John W. Vinson[MVP]
(no longer chatting for now)
 
John
Thanks for the quick reply. I tried what you suggested, but now I get the
message that it can't find the field 'form' in my expression.

John Vinson said:
I currently have a working database with forms and subforms. My problem is
in the coding to enable me to display a subform. The application is for
equipment, which is displayed on form1, and equipment components which are
displayed on a related subform1. I have a need to doubleclick on field in
the subform1 and have subform2 popup and display detailed information
relative to just the item appearing in the field I just doubleclicked.
Click to expand...

A Subform and a popup form are two QUITE DIFFERENT THINGS. This isn't
really a "subform"; a subform is displayed in a subform control on a
form, and you don't - can't - open it. It's just right there on the
form.
I have attempted to use a variable from subform1 as a filter for subform2
but can't seem to get the syntax right.

The data in the field I click is text data. My code is as follows:

Private Sub Part_ID_DblClick(Cancel As Integer)


Dim stPartID As String

Set stPartID = Forms![equipment list recipe]![recipe ingriediants
subform]!Form![part id]
Click to expand...

Remove the word Set from this line. Set applies to Object variables -
strPartID is just a string variable, not an object.
DoCmd.OpenForm "parts table subform", acNormal, "", "[part id] = stPartID"
Click to expand...

And here, you're searching for part id's equal to the text string
"stPartID". There won't be any of course. Instead, concatenate the
*value* of stPartID into the WhereCriterion string:

DoCmd.OpenForm "[parts table subform]", acNormal, , "[part id] = " &
stPartID

Note the brackets around the form name and the consecutive commas
rather than passing an empty string.


John W. Vinson[MVP]
(no longer chatting for now)
Click to expand...
 
John
Thanks for the quick reply. I tried what you suggested, but now I get the
message that it can't find the field 'form' in my expression.
Click to expand...

Oops... overlooked another error. Change !Form to .Form:

Forms![equipment list recipe]![recipe ingriediants subform].Form![part
id]

(you might also want to spell it "ingredients"

John W. Vinson[MVP]
(no longer chatting for now)
 
Wayne,

Take a lokk at how the Customer Orders form with two subforms is done in
Northwind. It does just what you describe. Look at the Current event of
subform1; it is the key to syncing subform2 to subform1.
 
Back
Top