none
Run and Preview give different results RRS feed

  • Question

  • I am working on an Access database that creates serial numbers for our products. On a form you enter the Order number and the Stock Code and click a button that says Generate SN's or a button that says Reprint. If we Generate the SN's or Reprint the report, it leaves out the sub-report. If I go in to design view and click Preview the entire report previews properly, with the sub-report. When I use the form and Generate or Reprint the serial numbers I enter the order number in one box and the stock code into another. The code behind the button combines those two fields with a / between them and uses it as a reference number. The query behind the report then uses Left and Right functions to separate those numbers back out. This is only happening right now for one particular order and we can't figure out why. All other orders are printing fine.

    The sales order number is 000000000062367

    The stock code is DA0030

    The reference created would be 000000000062367/DA0030

    The query says the Sales Order number is SO: Left([reference],15) - meaning I want the first 15 characters to be the SO.

    And the Stock Code is SC: Right([reference],Len([reference])-16) - meaning I want to remove the first 16 characters and whatever remains is the SC. The stock code could be anywhere from 4 to 15 characters.

    For the case that I am looking at it is only 6.

    Here is the code for Generate SN's (Do_it_Click()):

                                                                                                                             

    Private Sub Do_it_Click()
    Dim Counter As Integer, Complete As String
    Dim BOQTY As Integer, OrderQTY As Integer, QTY As Integer
    Dim Reference As String
    On Error GoTo Err_Do_it_Click

    If IsNull(Me![SO]) Then
      MsgBox "You must enter a number in the Sales Order field!", _
      vbExclamation, "No Sales Order"
      Me![SO].SetFocus
      GoTo Exit_Do_it_Click
    End If

    If IsNull(Me![Stock_Code]) Then
      MsgBox "You must enter a number in the Stock Code field first!", _
             vbExclamation, "No Stock Code"
      Me![Stock_Code].SetFocus
      GoTo Exit_Do_it_Click
    End If

    If SOCheck() And SCCheck() Then
      Me![Reference] = Me![SO] & "/" & Me![Stock_Code]
      Reference = Me![Reference]
      Me![ReprintStatus] = False
    Else
      GoTo Exit_Do_it_Click
    End If

    'check for previous issue to so/stock
    If DCount("[serialNo]", "issued_serial_numbers", "[reference]= '" & Reference & "'") > 0 Then
      MsgBox "Serial numbers have already been issued to this SO/Stock." _
             & "  Please use the reprint or maintenance utilities.", _
             vbInformation
      Me![SO].SetFocus
      GoTo Exit_Do_it_Click
    End If

    ' check for completion of SalesOrder DSUM
    OrderQTY = DSum("[morderqty]", "so check list", "[salesorder]='" & Me![SO] _
               & "' and [mstockcode]='" & Me![Stock_Code] & "'")

    BOQTY = DSum("[mbackorderqty]", "so check list", "[salesorder]='" & Me![SO] _
            & "' and [mstockcode]='" & Me![Stock_Code] & "'")

    If BOQTY = 0 Then
      MsgBox "Please change operation to reprint.  The SO item" _
             & " you have entered is fulfilled.", vbExclamation, _
             "Item Fulfilled"
      Me![Stock_Code].SetFocus
      GoTo Exit_Do_it_Click
    End If

    ' all ok populate sn
    For Counter = 1 To 5

      Complete = SerialNo(OrderQTY, Reference, Me![SO])

      If Complete = Reference Then ' print report
        Select Case Left(Me![Stock_Code], 2)

          Case "CA":
            DoCmd.OpenReport "Tritium Backflush Issue TO Order ticket", acViewNormal

          Case Else:
            If DCount("[stockcode]", "cleanroom devices", "[stockcode]= '" & _
               Me![Stock_Code] & "'") > 0 Then
              DoCmd.OpenReport "StaticControl Backflush Issue TO Order ticket cleanroom", acViewNormal
            Else
              DoCmd.OpenReport "StaticControl Backflush Issue TO Order ticket", acViewNormal
            End If
        End Select

        MsgBox "Check printer for your report.", vbInformation, "Done"
        Exit For
      End If

    Next

    If Complete = "incomplete" Then
      MsgBox "SN generator is not working.  Please contact" _
             & " Computer Support", vbCritical, "Error!"
      GoTo Exit_Do_it_Click
    End If

    If Complete = "Busy" Then
      MsgBox "SN generator is busy.  Please contact" _
             & " Computer Support", vbCritical, "Error!"
      GoTo Exit_Do_it_Click
    End If

    Exit_Do_it_Click:
        Exit Sub

    Err_Do_it_Click:
        MsgBox Err & ": " & Err.Description
        Resume Exit_Do_it_Click

    End Sub

    And here is the code for Reprint (Reprint_Click()):

                                                               

    Private Sub Reprint_Click()
    Dim Counter As Integer, dbs As Database
    Dim JobInfo As QueryDef, BOQTY As Integer, OrderQTY As Integer, QTY As Integer
    Dim Reference As String
    On Error GoTo Err_Reprint_Click

    If IsNull(Me![SO]) Then
      MsgBox "You must enter a number in the Sales Order field!", _
             vbExclamation, "No Sales Order"
      Me![SO].SetFocus
      GoTo Exit_Reprint_Click
    End If

    If IsNull(Me![Stock_Code]) Then
      MsgBox "You must enter a number in the Stock Code field first!", _
             vbExclamation, "No Stock Code"
      Me![Stock_Code].SetFocus
      GoTo Exit_Reprint_Click
     End If

    Me![Reference] = Me![SO] & "/" & Me![Stock_Code]

    Reference = Me![Reference]
    Me![ReprintStatus] = True

    'check for previous issue to so/stock
    If DCount("[serialNo]", "issued_serial_numbers", "[reference]= '" & Reference & "'") < 0 Then
      MsgBox "Serial numbers have not been issued to this SO/Stock." _
             & "  Please use the Generate SN's button.", _
             vbInformation
      Me![Do_it].SetFocus
      GoTo Exit_Reprint_Click
    End If

    ' print report
    Select Case Left(Me![Stock_Code], 2)

      Case "CA":
        DoCmd.OpenReport "Tritium Backflush Issue TO Order ticket", acViewNormal

      Case Else:
        If DCount("[stockcode]", "cleanroom devices", "[stockcode]= '" & _
           Me![Stock_Code] & "'") > 0 Then
          DoCmd.OpenReport "StaticControl Backflush Issue TO Order ticket cleanroom", acViewNormal
        Else
          DoCmd.OpenReport "StaticControl Backflush Issue TO Order ticket", acViewNormal
        End If

    End Select

    MsgBox "Check printer for your report.", vbInformation, "Done"

    Exit_Reprint_Click:
    Exit Sub

    Err_Reprint_Click:
    MsgBox Err & ": " & Err.Description
    Resume Exit_Reprint_Click

    End Sub


    When the report prints it shows the reference number as 000000000062367/DA0.

    I can't find anywhere in the code where it would cause the reference number to cut off like this.

    Any ideas? Please let me know if more information is required.

    Thanks,

    Jessica

    Tuesday, August 16, 2016 3:21 PM

Answers

  • Ok so this was all for nothing...

    My issue was the stock codes were clean room stock codes. They had to follow this part of the code...

    Case Else:
        If DCount("[stockcode]", "cleanroom devices", "[stockcode]= '" & _
           Me![Stock_Code] & "'") > 0 Then
          DoCmd.OpenReport "StaticControl Backflush Issue TO Order ticket cleanroom", acViewNormal

    I hadn't corrected the query in that report to be -16. It was still -7 so was not pulling the needed information. 

    The reason it worked when I clicked preview was because I was previewing the regular report instead of the clean room report and it was asking me for the reference number and I was entering it correctly. 

    I am sooo sorry that I didn't catch this sooner. 

    I do appreciate everyone who tried to help me. Thank you sooo much.

    Jessica

    Thursday, August 18, 2016 3:58 PM

All replies

  • Hi Jessicadd,

    is this issue is only occur with 1 Record in the Table? if so then check the data entry in the Table for this record.

    the other thing you had mentioned that ,"And the Stock Code is SC: Right([reference],Len([reference])-16) - meaning I want to remove the first 16 characters and whatever remains is the SC. The stock code could be anywhere from 4 to 15 characters."

    in the function you had remove the 16 characters from the length. but you had mentioned that the stock code could be anywhere from 4 to 15 characters then how you identify it?

    First I would recommend you to debug the code and post the code only that creates the problem. because we can see that the code you had posted above has many unnecessary  lines that's not the part of the issue. so it can confuse the community members.

    also it is better that you also provide your table design, stored data and output along with the code.

    so we can get clear idea about the issue.

    please try to provide the above mentioned details so that we can try to help you further.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 17, 2016 1:30 AM
    Moderator
  • I cannot provide stored data. Only samples due to the nature of our business.

    This had only happened with one record at the time of posting but I was preparing for more. As of this morning there are now two orders that this is occurring with.

    I don't know how to debug.

    The reference number is the Sales Order number and the Stock code. The sales order number is ALWAYS 15 characters (10 zeros and 5 numbers). Then there is a backslash and the stock code. The -16 is for the 15 characters in the sales order number and the backslash. Whatever is left is the stock code. All of the characters after the backslash are the stock code.

    Am I using the function incorrectly? 

    Wednesday, August 17, 2016 12:46 PM
  • This is a reasonably good tutorial on using Access' VBA debugger:

    http://www.techonthenet.com/access/tutorials/vbadebug2010/debug01.php

    Does the control on your report which displays the reference number have an input mask or format property set?

    -Bruce


    Wednesday, August 17, 2016 2:46 PM
  • I'm not sure. The report doesn't display the reference number as the reference number. It breaks it back down into Sales Order number and Stock Code to display on the report. I don't know why it was done this way. Seems redundant to me to combine them then separate them but this was created way before I started the job.

    I will try to follow the link you provided to debug the code. The tutorial looks familiar and if I recall I had trouble understanding it. But I will try and let you know what I come up with.

    Thank you

    Jessica :)

    Wednesday, August 17, 2016 3:25 PM
  • Hi Jessicaadd,

    you had mentioned," I don't know why it was done this way. Seems redundant to me to combine them then separate them"

    yes, I also had this question.

    if in the table they are separately stored then what's the purpose to combine them ? and after combining why again separate them.

     because I don't know your table design and how data stored in it.

    I can simply give you an example like below.

    in which I have save that combine data in the table and on the report I will display it separately.

    so my data in table are as below.

    my query will be like below.

    SELECT Left([refdata],15) as Order_Number, Right([refdata],6) as Stock_Code from orderdata ;
    

    output will be like below.

    so you can implement same logic in your code to work correctly.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 18, 2016 3:12 AM
    Moderator
  • That would work great but that is only one stock code. We have hundreds of stock codes and they aren't all 6 characters long. I had to modify the query because our Sales Order number increased in length with the last upgrade of our ERP. The query originally said "SC: Right([reference],Len([reference])-7)" and ran fine. I changed it to -16 to accommodate the additional leading zeros. The only reason it is not working is because for some unknown reason for these (now) 2 orders are showing the reference number as 000000000062367/DA0. It is not like that in the table. 

    Why wouldn't it pull the entire reference field like it does for every other order?

    And why would it break it down and print the report correctly when I am in design view and click preview, but it won't print correctly when we just run the report or reprint it from the form?

    Thanks,

    Jessica

    Thursday, August 18, 2016 12:52 PM
  • Ok so this was all for nothing...

    My issue was the stock codes were clean room stock codes. They had to follow this part of the code...

    Case Else:
        If DCount("[stockcode]", "cleanroom devices", "[stockcode]= '" & _
           Me![Stock_Code] & "'") > 0 Then
          DoCmd.OpenReport "StaticControl Backflush Issue TO Order ticket cleanroom", acViewNormal

    I hadn't corrected the query in that report to be -16. It was still -7 so was not pulling the needed information. 

    The reason it worked when I clicked preview was because I was previewing the regular report instead of the clean room report and it was asking me for the reference number and I was entering it correctly. 

    I am sooo sorry that I didn't catch this sooner. 

    I do appreciate everyone who tried to help me. Thank you sooo much.

    Jessica

    Thursday, August 18, 2016 3:58 PM