Where Condition confusion

[Ernie Sersen]

I have checked multiple postings on Where Condition and have tried several
code strings all to no avail. Here is what I need:

Main Form Name: PNmain (field in a SQL Function pulled from a SQL table)
Main Form Field: PN
Secondary Form Name: PNsec (field in a SQL Function pulled from a SQL table)
Secondary Form Fields: PN, Description, Cost, Vendor, etc

When I click a button on the main form to open the secondary form, I want
the secondary form to display the part details of the main form part number
(PN). I am trying to use the following code from Microsoft which is not
working at present:

Dim stDocName As String
stDocName = "PNsec"
DoCmd.OpenForm stDocName, acFormDS, , [PN] = Forms![PNmain]![PN]

What am I missing? {thanks}

 

[Stefan Hoffmann]

hi Ernie,

Dim stDocName As String
stDocName = "PNsec"
DoCmd.OpenForm stDocName, acFormDS, , [PN] = Forms![PNmain]![PN]
What am I missing? {thanks}

The condition must be a string with quotes, e.g.:

DoCmd.OpenForm "PNsec", , , "[PN] = " & Me![PN]


mfG
--> stefan <--

 

[Ernie Sersen]

Thanks Stefan. Can you please expand your suggestion? Not sure where to put
the Me!

DoCmd.OpenForm stDocName, acFormDS, , "[PN] = " Forms![PNmain]![PN]

Where does the &Me![PN] go in this string?

hi Ernie,

Dim stDocName As String
stDocName = "PNsec"
DoCmd.OpenForm stDocName, acFormDS, , [PN] = Forms![PNmain]![PN]
What am I missing? {thanks}

The condition must be a string with quotes, e.g.:

DoCmd.OpenForm "PNsec", , , "[PN] = " & Me![PN]


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Ernie,

Ernie Sersen write:

Thanks Stefan. Can you please expand your suggestion? Not sure where to put
the Me!
DoCmd.OpenForm stDocName, acFormDS, , "[PN] = " Forms![PNmain]![PN]

You have to concatenate the strings:

DoCmd.OpenForm stDocName, acFormDS, , "[PN] = " & Forms![PNmain]![PN]

Where does the &Me![PN] go in this string?

Me is a reference to the form itself. So Forms![PNmain] is equivalent to
Me![PN] when you are using this code on the PNmain form with a button
called cmdOpenDetails:

Private Sub cmdOpenDetails_Click

DoCmd.OpenForm "PNsec", , ,"[PN] = " & Me![PN]

End Sub

Dim stDocName As String
stDocName = "PNsec"
DoCmd.OpenForm stDocName, acFormDS, , [PN] = Forms![PNmain]![PN]
What am I missing? {thanks}

The condition must be a string with quotes, e.g.:

DoCmd.OpenForm "PNsec", , , "[PN] = " & Me![PN]

mfG
--> stefan <--

 

[Ernie Sersen]

Thanks again, Stefan. Still not working. I don't know if this matters but
my Access front end is an ADP (Data Project) linked to a SQL back end. My
data sources are SQL Functions. I have tried both of your suggestions
Forms![PNmain]![PN] and Me![PN] and both return a full (non-filtered) record
set when I click my OpenDetails button on the main form. Is there any other
way to do what I am trying to do? There is a Filter box on the PNsec form.
Can I put a criteria string in there? Thanks for trying.

hi Ernie,

Ernie Sersen write:

Thanks Stefan. Can you please expand your suggestion? Not sure where to put
the Me!
DoCmd.OpenForm stDocName, acFormDS, , "[PN] = " Forms![PNmain]![PN]

You have to concatenate the strings:

DoCmd.OpenForm stDocName, acFormDS, , "[PN] = " & Forms![PNmain]![PN]

Where does the &Me![PN] go in this string?

Me is a reference to the form itself. So Forms![PNmain] is equivalent to
Me![PN] when you are using this code on the PNmain form with a button
called cmdOpenDetails:

Private Sub cmdOpenDetails_Click

DoCmd.OpenForm "PNsec", , ,"[PN] = " & Me![PN]

End Sub

Ernie Sersen wrote:
Dim stDocName As String
stDocName = "PNsec"
DoCmd.OpenForm stDocName, acFormDS, , [PN] = Forms![PNmain]![PN]
What am I missing? {thanks}
The condition must be a string with quotes, e.g.:

DoCmd.OpenForm "PNsec", , , "[PN] = " & Me![PN]

mfG
--> stefan <--

 

[Ernie Sersen]

Solved it! For some reason, Access doesn't like filtering on SQL Function
datasets. I created a duplicate dataset using a SQL view and it worked like
a champ! Just thought I'd pass along what I found. Again, thanks.

hi Ernie,

Ernie Sersen write:

Thanks Stefan. Can you please expand your suggestion? Not sure where to put
the Me!
DoCmd.OpenForm stDocName, acFormDS, , "[PN] = " Forms![PNmain]![PN]

You have to concatenate the strings:

DoCmd.OpenForm stDocName, acFormDS, , "[PN] = " & Forms![PNmain]![PN]

Where does the &Me![PN] go in this string?

Me is a reference to the form itself. So Forms![PNmain] is equivalent to
Me![PN] when you are using this code on the PNmain form with a button
called cmdOpenDetails:

Private Sub cmdOpenDetails_Click

DoCmd.OpenForm "PNsec", , ,"[PN] = " & Me![PN]

End Sub

Ernie Sersen wrote:
Dim stDocName As String
stDocName = "PNsec"
DoCmd.OpenForm stDocName, acFormDS, , [PN] = Forms![PNmain]![PN]
What am I missing? {thanks}
The condition must be a string with quotes, e.g.:

DoCmd.OpenForm "PNsec", , , "[PN] = " & Me![PN]

mfG
--> stefan <--