Run Macro if Query is Not Null

[CDF]

Hi,

I have a form where a user can tick which addresses he/she wants to
print onto sticky labels with a button that opens a confirmation form
when the user is ready to print.

The cofirmation form tells the user to place x amount of sheets of
paper into the printer before pressing OK or Cancel. OK prints the job
(sticky labels), cancel goes back to the original form.

This form has a txt box with the following calculation in it's Control
Source

=Sum(([Count]/24)+0.5)

The forms record source refers to a query which counts how many
addresses in the dbase, therefore the pop up form would count the
number of addresses and work out how many sheets the user needs to put
in the printer. If there were 5 addresses (labels) to print the
message would show:

Please place 1 sheet of blank sticky labels in the printer etc.. OK ,
Cancel.

My problem:
If the original form where you choose the addresses is left blank, ie
no addresses are ticked, then the query would obviously be Null. If
the user presses the button to pop up the confirmation form when it is
NULL, it just shows a blank box and you have to ctrl+Alt+del to get
out of access.

I need to run an if statement in the buttons onclick event procedure
saying 'if the query is null, cancel (or put a message box stating to
choose at least 1 address), else open the confirmation form'.

The query has nothing to do with the form that the button is on, so
I'm not sure how to reference it.

How do I write the code as I'm not a programmer, but I understand it
if told what to write.

If this helps the forms and queries are as follows:
Query: qry_CountOfShowAddress
SELECT tbl_Customers.Customer_AddressShow, Count
(tbl_Customers.Customer_AddressShow) AS [Count]
FROM tbl_Customers
GROUP BY tbl_Customers.Customer_AddressShow
HAVING (((tbl_Customers.Customer_AddressShow)<>No));

confirmation form: frmCountOfAddressLabelSheets

The button is on a form called: frmChooseMailingLabels
Button Name: PrintAddressLabels

Any help would be appreciated.

 

[UpRider]

Add this code to the confirmation button. It counts the number of records
returned by the query so that you can take appropriate action.

if DCount("*", "QueryName") = 0 then
msgbox "No records meet your criteria, so nothing will be printed", _
vbokonly," S E L E C T I O N E R R O R "
exit sub
endif

HTH, UpRider

 

[Tom Wickerath]

Hi CDF,

Have a look at one of my QBF (Query By Form) sample databases, where I
enable or disable a command button with caption "Export to Excel", based on
records found in the search. If no records meet the search criteria, this
command button is disabled. Here is a link to a zipped copy of this sample
database:

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

And here is a snippet of the code (not the complete procedure) that is
causing this funtionality to work:

Option Compare Database
Option Explicit

Public Function RequerySubform()
On Error GoTo ProcError

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim intRecordCount As Integer

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMovieSelections")
:
:
:
qdf.SQL = strFullSQL
Set rs = db.OpenRecordset("qryMovieSelections")
intRecordCount = rs.RecordCount

If intRecordCount = 0 Then
cmdExportToExcel.Enabled = False
Else
cmdExportToExcel.Enabled = True
End If
:
:
:
ExitProc: 'Clean up
On Error Resume Next
qdf.Close: Set qdf = Nothing
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in RequerySubform event procedure..."
Resume ExitProc
End Function


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi,

I have a form where a user can tick which addresses he/she wants to
print onto sticky labels with a button that opens a confirmation form
when the user is ready to print.

The cofirmation form tells the user to place x amount of sheets of
paper into the printer before pressing OK or Cancel. OK prints the job
(sticky labels), cancel goes back to the original form.

This form has a txt box with the following calculation in it's Control
Source

=Sum(([Count]/24)+0.5)

The forms record source refers to a query which counts how many
addresses in the dbase, therefore the pop up form would count the
number of addresses and work out how many sheets the user needs to put
in the printer. If there were 5 addresses (labels) to print the
message would show:

Please place 1 sheet of blank sticky labels in the printer etc.. OK ,
Cancel.

My problem:
If the original form where you choose the addresses is left blank, ie
no addresses are ticked, then the query would obviously be Null. If
the user presses the button to pop up the confirmation form when it is
NULL, it just shows a blank box and you have to ctrl+Alt+del to get
out of access.

I need to run an if statement in the buttons onclick event procedure
saying 'if the query is null, cancel (or put a message box stating to
choose at least 1 address), else open the confirmation form'.

The query has nothing to do with the form that the button is on, so
I'm not sure how to reference it.

How do I write the code as I'm not a programmer, but I understand it
if told what to write.

If this helps the forms and queries are as follows:
Query: qry_CountOfShowAddress
SELECT tbl_Customers.Customer_AddressShow, Count
(tbl_Customers.Customer_AddressShow) AS [Count]
FROM tbl_Customers
GROUP BY tbl_Customers.Customer_AddressShow
HAVING (((tbl_Customers.Customer_AddressShow)<>No));

confirmation form: frmCountOfAddressLabelSheets

The button is on a form called: frmChooseMailingLabels
Button Name: PrintAddressLabels

Any help would be appreciated.

 

[CDF]

Uprider and Tom thank you very much for your responses.

Tom I tried Upriders suggestion first as it was simpler for me to
understand and it worked so I didn't get a chance to try yours, but
thanks for your effort. Hopefully someone will get use from it.

Uprider I tried the following in the buttons onclick event and it
works perfectly. Thank you.

Private Sub PrintAddressLabels_Click()

DoCmd.Requery ""
If DCount("*", "qry_CountOfShowAddress") = 0 Then
MsgBox "There are no addresses selected. Please tick at least 1
address then try again.", vbExclamation + vbOKOnly, "Warning"
Exit Sub
End If
DoCmd.RunMacro "mac_PrintLibrary.ShowAddressLabelSheetsForm"

End Sub

 

[Tom Wickerath]

Yep, DCount works fine as well, and is certainly easier.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________