Run and Preview give different results

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

Answer 1

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.

Answer 2

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? 

Answer 3

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

Answer 4

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 :)

Answer 5

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.

Answer 6

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

Answer 7

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