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

    Brain dead. Code Help? (Access 97)

    The code below works fine but with a minor bug. This code is by a clerk to create a delivery log sheet. The clerk enters in there receipt#'s and then when they are done they leave the input box blank and it prints thier log sheet. They problem is when the have a large amount of material it gives them an error message. Not to sure what the error is. They have been overcoming this by selecting less material. Is it soemthing with the code below. Maybe cancel as interger? Thanks for the help..


    Public Function BuildMyLogSheet() As String
    Dim Cancel As Integer
    Dim stSql As String
    Dim StWhere As String
    Dim Strwhere 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#")

    If StCriteria = "" Then
    BuildMyLogSheet = ""
    Exit Function
    End If

    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


    MySet.Close
    Set MySet = Nothing
    Set MyDB = Nothing
    StWhere = StWhere & ")"
    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: Brain dead. Code Help? (Access 97)

    Without knowing what error message they're getting, we're just guessing, but you may be running into limitations on string lengths. You need to find out specifically the error they get.
    Charlotte

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Brain dead. Code Help? (Access 97)

    Hi,
    Without knowing the exact error it's hard to be sure, but my guess would be that your WHERE clause gets too long for Access to handle. (I'm afraid I can't remember the limits offhand) You might try building a '[Receipt#] IN ('1', '2', '3'...)' clause rather than creating a criteria string like "[Receipt#] = '1' or [Receipt#] = '2'..." etc. Alternatively, you may need to write the receipt#s to a temporary table and use that to create your log sheet.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Brain dead. Code Help? (Access 97)

    The maximum length of the where condition argument is 32,768 characters in VB, or
    256 if used in a macro.
    Rory is correct about using the IN operator to build the criteria instead of a big honking string of "OR" statements repeating the field name each time.
    I noticed that this BuildMyLogSheet function returns the final SQL statement. If the SQL being return is being used in a macro, this of course will get blown out at the 256 Len limitation of a Macro's criteria.

    Your function opens a recordset (MySet) containing all records in the ReceiptNumbers table.
    This is a bit overboard when you are really looking for specific records.

    I see another minor problem. You are appending the "Receipt#" to your stWhere before you have checked to see if the number is in the table or not. You then do the .FindFirst and message the user if the NoMatch is true which is fine but you have already added it the the stWhere which you are then passing this to some other function outside this one. This may cause you trouble if the subsequent process that gets passed the final SQL criteria expects to find all records in the where clause. Logically you should do the .FindFirst to determine if the record can be found. If it is found, then append the Receipt# to your stWhere.

    Take a look at this, its a bit cleaner;

    Public Function BuildMyLogSheet() As String
    Dim stSql As String, stWhere As String, stCriteria As String, strMessage as String
    Dim stUpdateSql As String
    Dim MyDB As DAO.Database
    stSql = "Select [Group],[Receipt#],[To],[Room #],[Signature],[Descrip],[Recvd],[Group#] "
    stSql = stSql & "From [qryIDCLogsheet] "

    stCriteria = InputBox("Enter Receipt#.", "Receipt#")

    If Len(stCriteria) Then
    Set MyDB = CurrentDb()

    strMessage = "Enter Receipt# You Wish To Print. Leave Field Blank And Hit Enter "
    strMessage = strMessage & "Or Click OK / If All Receipt Numbers Are Entered."

    stUpdateSql = "UPDATE [ReceiptNumbers] SET [Recvd] =-1, [Printed] =-1, [RecvdTime] = Now() "

    Do While stCriteria <> ""
    MyDB.Execute (stUpdateSql & " WHERE [Receipt#] = '" & stCriteria & "' ")

    If MyDB.RecordsAffected > 0 Then ' we updated a record.
    If Len(stWhere) Then ' Append the Receipt# to the stWhere
    stWhere = stWhere & ", " & "'" & stCriteria & "'"
    Else ' This is the first receipt number entered...
    stWhere = "WHERE [Receipt#] IN(" & "'" & stCriteria & "'"
    End If
    Else
    ' No record was found matching the stCriteria
    MsgBox ("Receipt# " & stCriteria & " not found.")
    End If

    stCriteria = InputBox( strMessage, "Receipt#")
    Loop

    Set MyDB = Nothing
    ' if user entered a bad receipt# at the first InPutBox and then entered nothing
    ' at the second prompting for a #, we may still not have a stWhere.
    If Len(stWhere) Then
    stWhere = stWhere & ")"
    BuildMyLogSheet = stSql & stWhere
    End If
    End If

    End Function

    Test it before you use this with your live data.
    Later,
    Bruce

Posting Permissions

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