Code Help

Topic

New Reply

The code below basically is used to make a report. I have this run on the on Open event of the report. What the code does is prompt the user, using an input box, for a certaind Receipt#. Everything works well except when I hit the cancel button on the input box. This will display all the records in the report instead of the selected records. Is there a statement I can add to this that says “If canel button on form is hit to stop the code.” Thanks!!

Public Function BuildMyLogSheet() As String
Dim Cancel As Integer
Dim stSQL As String
Dim StWhere As String
Dim StCriteria As String
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset

Set MyDB = CurrentDb()
Set MySet = MyDB.OpenRecordset(“ReceiptNumbers”, dbOpenDynaset)

stSQL = “Select [Group],[Receipt#],[To],[Room #],[Signature],[Descrip],[Recvd],[Group#] From [qryIDCLogsheet] ”

StCriteria = InputBox(“Enter Receipt#.”, “Receipt#”)

Do While StCriteria “”
If StWhere = “” Then
StWhere = “Where ([Receipt#] = ” & “‘” & StCriteria & “‘”
Else
StWhere = StWhere & ” or [Receipt#] =” & “‘” & StCriteria & “‘”
End If

MySet.MoveFirst
MySet.FindFirst “[Receipt#] = ” & “‘” & StCriteria & “‘”
If MySet.NoMatch Then
MsgBox (“Receipt# ” & StCriteria & ” not found.”)
Else
MySet.Edit
MySet(“Recvd”) = -1
MySet(“Printed”) = -1
MySet(“RecvdTime”) = Now()
MySet.Update
End If
StCriteria = InputBox(“Enter Receipt# You Wish To Print…….. Leave Field Blank And Hit Enter Or Click OK / If All Receipt Numbers Are Entered.”, “Receipt#”)
Loop

If StWhere “” Then
StWhere = StWhere & “)”
End If

MySet.Close
Set MySet = Nothing
Set MyDB = Nothing

BuildMyLogSheet = stSQL & StWhere

End Function

Reply | Quote

Replies

Before your Do While … Loop, put in something like this:

If strWhere = "" Then
  Set MySet = Nothing
  Set MyDB = Nothing
  Exit Function
End If

I assume you have something in the Open event to test for an empty string as the result of the function, and that’s what will be returned this way.

Reply | Quote

Charlotte thanks for the help.. I do not have anything in the On Open event to check for an empty string. How can I check for the empty string in the On open.. Not sure how to do that. Thanks again..

Reply | Quote

How are you calling the function? If its something like this…

strResult = BuildMyLogSheet()

then just add this:

If strResult = "" Then
  Cancel = True
End If

Reply | Quote