I have a form where 3 fields (ECNAnalyst, StartDate and StopDate) are passed to a query as criteria. If any of the 3 fields are empty I want to stop the procedure. My code is working if ALL 3 fields are empty but if 1 contains data it proceeds. What am I doing wrong?


Private Sub lblPrintButton_Click()
On Error GoTo lblPrintButton_Click_Err
If Not ECNAnalyst.Value = "" Then
Beep
MsgBox "Press ""OK"" to print the ECN's By Analyst And Date report. This form will close automatically when printing is completed.", vbInformation, "ECN's By Analyst And Date Report"
DoCmd.OpenReport "rptECNByAnalystAndDate", acNormal, "", ""
DoCmd.Close acForm, "frmDates2"
DoCmd.Close acForm, "frmprint"
Else: MsgBox "You must select an ECN Analyst before printing."
End If
If Not StartDate.Value = "" Then
Beep
MsgBox "Press ""OK"" to print the ECN's By Analyst And Date report. This form will close automatically when printing is completed.", vbInformation, "ECN's By Analyst And Date Report"
DoCmd.OpenReport "rptECNByAnalystAndDate", acNormal, "", ""
DoCmd.Close acForm, "frmDates2"
DoCmd.Close acForm, "frmprint"
Else: MsgBox "You must specify a start date before printing."
End If
If Not StopDate.Value = "" Then
Beep
MsgBox "Press ""OK"" to print the ECN's By Analyst And Date report. This form will close automatically when printing is completed.", vbInformation, "ECN's By Analyst And Date Report"
DoCmd.OpenReport "rptECNByAnalystAndDate", acNormal, "", ""
DoCmd.Close acForm, "frmDates2"
DoCmd.Close acForm, "frmprint"
Else: MsgBox "You must specify an end date before printing."
End If

lblPrintButton_Click_Exit:
Exit Sub

lblPrintButton_Click_Err:
MsgBox Error$
Resume lblPrintButton_Click_Exit

End Sub

Code should be something like:



Private Sub lblPrintButton_Click()
On Error GoTo lblPrintButton_Click_Err
If ECNAnalyst.Value = "" OR StartDate.Value = "" OR StopDate.Value = "" Then

MsgBox "You must specify an analyst, start date and stop date before printing."
Else
Beep
MsgBox "Press ""OK"" to print the ECN's By Analyst And Date report. This form will close automatically when printing is completed.", vbInformation, "ECN's By Analyst And Date Report"
DoCmd.OpenReport "rptECNByAnalystAndDate", acNormal, "", ""
DoCmd.Close acForm, "frmDates2"
DoCmd.Close acForm, "frmprint"
End If

lblPrintButton_Click_Exit:
Exit Sub

lblPrintButton_Click_Err:
MsgBox Error$
Resume lblPrintButton_Click_Exit

End Sub



If you test fields independently you will get independent results.

Almost...

Once the form is opened if I press the "OK" button it proceeds to print a blank report without halting for the error message. However, I select only a name without selecting the dates the code does stop. Any help would be appreciated.

Copied from the my VBA screen


Private Sub lblPrintButton_Click()
On Error GoTo lblPrintButton_Click_Err
If Not ECNAnalyst.Value = "" Or StartDate.Value = "" Or StopDate.Value = "" Then
MsgBox "All fields (ECN Analyst, Start and Stop Date) must be filled before printing."
Else
Beep
MsgBox "Press ""OK"" to print the ECN's By Analyst And Date report. This form will close automatically when printing is completed.", vbInformation, "ECN's By Analyst And Date Report"
DoCmd.OpenReport "rptECNByAnalystAndDate", acNormal, "", ""
DoCmd.Close acForm, "frmDates2"
DoCmd.Close acForm, "frmprint"

End If

lblPrintButton_Click_Exit:
Exit Sub

lblPrintButton_Click_Err:
MsgBox Error$
Resume lblPrintButton_Click_Exit

End Sub

You have IF NOT

i.e

If Me.ECNAnalyst = vbNullString Or Me.StartDate = vbNullString Or Me.StopDate = vbNullString Then

Oops! Fornation flipped my code. Found it just as you were posting Mile. Here is what I have now but still having problems. I flipped the Then and Else. I get the error message when appropriate but even with all 3 fields complete the code is returning the error message, i.e. I never can print. AAUUGHH

code:--------------------------------------------------------------------------------
Private Sub lblPrintButton_Click()
On Error GoTo lblPrintButton_Click_Err
If Not ECNAnalyst.Value = "" Or StartDate.Value = "" Or StopDate.Value = "" Then 'checks to see if all fields have values, if so proceeds, if any field empty goes to error

Beep
MsgBox "Press ""OK"" to print the ECN's By Analyst And Date report. This form will close automatically when printing is completed.", vbInformation, "ECN's By Analyst And Date Report"
DoCmd.OpenReport "rptECNByAnalystAndDate", acNormal, "", ""
DoCmd.Close acForm, "frmDates2"
DoCmd.Close acForm, "frmprint"

Else 'if values not present in every field then error
MsgBox "All fields (ECN Analyst, Start and Stop Date) must be filled before printing."

End If

lblPrintButton_Click_Exit:
Exit Sub

lblPrintButton_Click_Err:
MsgBox Error$
Resume lblPrintButton_Click_Exit

End Sub

Got a quick mock database of it?

I'll try. Be back in a minute.

Thanks Mile. I hope it works ok for you as it is in A2K version normally but saved to 97 for you. There are normally calendar buttons on the form but I didn't think that they would work in 97 so they have been removed. To get the report to work change the code as previous postings indicate.

Mile.

Just thought of a mistake I made. I manually entered the table info and put in the wrong names in the ECN Analyst field. Please put in Ken Perman for the first 2 records and Bobbie Romans for the 3rd. Then the report whould kick out the results.

Change the IF line to:

If Not IsNull(Me.ECNAnalyst) And Not IsNull(Me.Start_Date) And Not IsNull(Me.Stop_Date) Then

Thank you Mile. Works perfectly now!

Now I've got to bin all these reports your code made me print off... :p