Pass information from form to QUERY CRITERIA

[kealaz]

I have a macro [mcPO] that executes when I click on a command button [Issue
Purchase Order]. This macro saves and closes a form [frmPO_ISSUE] and then
opens a report [rptPO].

rptPO is based on a query [qryPOCopy].

When qryPOCopy is run, a pop-up window appears asking for a PO Number (
Criteria = [Enter PO Number:] ). Then it searches two tables [tblPOHIST and
tblBUYHIST] to find the information it needs to create the report.

My question is...

Since the PO number [text box name: P_O_NO] I want to use for the
report/query is in the form that I am save/closing, can I pass that
information automatically to the query by either adding some code to the ON
CLICK event of my command button [Issue Purchase Order] or by adding to the
macro that calls the report?

Thank you very much for any and all help you can provide. I greatly
appreciate all the help I get from this forum.

 

[Danny J. Lesandrini]

Most everyone will tell you to put a reference in the query to the form control.
This works well, so long as the form is open, which quite likely is always the
case. The SQL WHERE clause would look something like this ...

SELECT * FROM tblPurchaseOrder
WHERE [PONum] = Forms![frmPO_ISSUE]![P_O_NO]

Personally, I like creating a pair of functions that manage these kind of IDs.
Something like this, saved in a public module ...

Private m_lngPONum AS Long
Public Function SetPONum(ByVal lngValue As Long) As Boolean
m_lngPONum = lngValue
End Function
Public Function GetPONum() As Long
GetPONum = m_lngPONum
End Function

You set the value in the form's Current() event. Every time a user navigates
to a new record, you call this ...

SetPONum( Nz(Me![P_O_NO], 0)


Then the WHERE clause in the SQL above becomes ...
SELECT * FROM tblPurchaseOrder
WHERE [PONum] = GetPoNum()


There are 2 advantages to this. First, the query ALWAYS runs, even when
the form isn't open. Maybe that's important, maybe not. I simply like it for
debugging and maintenance of objects if they always open without errors.

Second, you can supply defaults when variables aren't initialized. Let's say
you want to supply a default value, either the Last record created or something
saved in a history table or the registry? You can do that in the function if the
initial value is zero. If you use this for date params, like FromDate and ThruDate,
you can initialize them to the first of the month or year, etc.

 

[Ken Sheridan]

Firstly, why is the macro saving the form? This is only necessary if you've
amended the form design, not its underlying data, which is saved
automatically on closure.

As far as restricting the report to the current purchase order instead of
including a parameter in the query you can filter the report by means of the
WhereCondition argument of the OpenReport method. So, instead of opening the
report with a macro you can do it all in the Click event procedure of the
button:

Const conREPORTNAME = "rptPO"
Dim strCriteria As String

strCriteria = "P_O_NO = " & Forms("frmPO_ISSUE").P_O_NO

' close form
DoCmd.Close, acForm, "frmPO_ISSUE"

' open report in print preview
DoCmd.OpenReport conREPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria

If the P_O_NO field is text data type rather than a number data type wrap
the value in quotes characters:

strCriteria = " P_O_NO = """ & Forms("frmPO_ISSUE").P_O_NO & """"

If you are changing the form design on the fly and really do need to save it
use:

DoCmd.Close, acForm, "frmPO_ISSUE", acSaveYes

Ken Sheridan
Stafford, England

 

[kealaz]

Firstly, why is the macro saving the form? This is only necessary if you've

amended the form design, not its underlying data, which is saved
automatically on closure.

I didn't know. Thanks. I'm learning as I go, and in the Command Button
wizard, there is a SAVE command button, so I have been using it on other
forms, thinking that if it is there, it must be necessary. I wanted the
command button for this form to also open a report and found out about
macros. So, instead of having one button save, one button open a report and
one button close the report, I wrote a macro to do all three.

So, instead of opening the
report with a macro you can do it all in the Click event procedure of the
button:

THANKS KEN! That is exactly what I want to do.

I had....
******************
Private Sub ISSUE_PO_Click()
On Error GoTo Err_ISSUE_PO_Click

Dim stDocName As String

stDocName = "mcPO"
DoCmd.RunMacro stDocName

Exit_ISSUE_PO_Click:
Exit Sub

Err_ISSUE_PO_Click:
MsgBox Err.Description
Resume Exit_ISSUE_PO_Click

End Sub
******************

and changed it to


******************
Private Sub ISSUE_PO_Click()
On Error GoTo Err_ISSUE_PO_Click

Const conREPORTNAME = "rptPO"
Dim strCriteria As String

strCriteria = " P_O_NO = """ & Forms("frmPO_ISSUE").P_O_NO & """"

' close form
DoCmd.Close , acForm, "frmPO_ISSUE"

' open report in print preview
DoCmd.OpenReport conREPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria

Exit_ISSUE_PO_Click:
Exit Sub

Err_ISSUE_PO_Click:
MsgBox Err.Description
Resume Exit_ISSUE_PO_Click

End Sub
******************

I'm getting an error message: Type mismatch

It is not opening the debugger window. I'm not sure where to look to
correct this error message.

THANK YOU VERY MUCH for all of your help!!! I really, really appreciate it!!!

 

[Ken Sheridan]

As you are, quite rightly, handling errors you won't get the usual system
error message and the opportunity to 'debug'. Temporarily turn off the error
handling by commenting out the first line with:

' On Error GoTo Err_ISSUE_PO_Click

You should then be able to identify the line causing the error.

I notice that an unwanted comma has crept in (my mistake!) in the following
line:

DoCmd.Close , acForm, "frmPO_ISSUE"

which should be:

DoCmd.Close acForm, "frmPO_ISSUE"

That's almost certainly the cause of the problem, but if it doesn't cure it,
as it’s a type mismatch error the most likely culprit is that the P_O_NO
field in the report's underlying recordset is a number data type rather than
text. If this is the case, however, the error will occur when the OpenReport
method is called, but the line you'll need to correct is the one which builds
the string for the WhereCondition, so you'd need to change:

strCriteria = "P_O_NO = """ & Forms("frmPO_ISSUE").P_O_NO & """"

to:

strCriteria = "P_O_NO = " & Forms("frmPO_ISSUE").P_O_NO

Ken Sheridan
Stafford, England

 

[kealaz]

Hi Ken,

Thanks, that took care of the error 'type mismatch'. I also commented out
the error handling lines, so that I can receive the errors if there are
anymore.

Now, when I fill out the form and click on my command button, I don't get
any errors, however, I do get TWO pop-up windows, one after the other. The
first one [Enter PO Number:] matches what I had before when I was calling the
report with the macro (which I no longer am). The query still has (Criteria
= [Enter PO Number:] )... should I have changed this? The second one
[P_O_NO] is new. Both of these pop-up windows have a field for me to enter
text into and an ok button. I entered the P.O. Number that I was working on,
and it brought up the report fine... so that part is working. What I'm
trying to do is make it so that I don't have to enter a P.O. number into that
field and it can just open the report with the info that is in the text box
[P_O_NO]

Thank you so much for your patience with me.

 

[kealaz]

Ken,

THANKS SO MUCH for your help. I had a problem with my query, but found it.
It's all working great now.

Thanks again!!!