Results 1 to 4 of 4

Thread: Code Help

  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Help

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help

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

    <pre>If strWhere = "" Then
    Set MySet = Nothing
    Set MyDB = Nothing
    Exit Function
    End If</pre>


    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.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Help

    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..

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code Help

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

    strResult = BuildMyLogSheet()

    then just add this:

    <pre>If strResult = "" Then
    Cancel = True
    End If</pre>

    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •