How to create an identifier to indicate a break in sequence?

[swen]

I am trying to modify a Access 2003 Sales report that pulls serial numbers assigned to sales orders. The modification needed is to add an identifier (anything, color, etc...) to show when there is a break in the serial number sequence.

example: Sales order # 46783
SN's assigned and shipped
4670110
4680110
**4700110
4710110
4720110
4730110
**4780110
4790110

** Identifies a break in the serial number sequence for this Sales order.

My dilemma is how to create this identifier on the report. The report is tied to an SQL statement in VBA.

I have limited knowledge of Access and VBA, so any help is greatly appreciated.

[TheSmileyCoder]

Dear Swen

I have uploaded a simple example of using the reports Detail_Format event to show or hide a line depending on whether or not the serial number is sequential. If you have any questions regarding the code, feel free to ask. I have also posted the code here.

Option Compare Database
Option Explicit
Private lngLast As Long
 
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'Format the appearance of the skipper line
    If lngLast = 0 Then
        'Special case, this is the first detail, before lngLast gets assigned a value.
        'Just assign value and exit
        lngLast = Me.tb_Serial
        Me.LineSkipper.Visible = False
        Exit Sub
    End If
 
 
    'Record the last value used in detail format. This is important as a detail can be formatted multiple times
    If lngLast = Me.tb_Serial Then
        'We are looking at the same detail as last time code ran, so just exit
        Exit Sub
    Else
        'We are looking at a new detail, lets compare it to the old one
        If Me.tb_Serial > lngLast + 10000 Then
            Me.LineSkipper.Visible = True
            lngLast = Me.tb_Serial
            Else
            Me.LineSkipper.Visible = False
            lngLast = Me.tb_Serial
        End If
    End If
End Sub

[swen]

TheSmileyOne

Thank you for the attachement and code. I changed the code to apply to the fields within my report and was not able to get it to work.

Here is the code that my report is generated from:

Private Sub Report_Open(Cancel As Integer)
 
If IsNull(Form_frm_RunRPT.BegSerial) And IsNull(Form_frm_RunRPT.EndSerial) And IsNull(Form_frm_RunRPT.SNso) = True Then
    MsgBox "The Serial Number range and Sales Order Number fields are blank." & vbCrLf & "Therefor, this report contains ALL Serial Numbers for this part."
End If
 
 
Dim lngLen As Long
Dim strSQL_FROM As String
Dim strSQL_WHERE As String
Dim strSQL As String
 
 
strSQL_FROM = "FROM TestCase "
 
strSQL_WHERE = "WHERE ((TestCase.TopAssyNo)=[Forms]![frm_RunRPT]![TopNo]) AND "
 
    If [Forms]![frm_RunRPT]![BegSerial] >= 0 Then
        strSQL_WHERE = strSQL_WHERE & "((TestCase.SerialNo) Between [Forms]![frm_RunRPT]![BegSerial] And [Forms]![frm_RunRPT]![EndSerial]) AND "
    End If
    If [Forms]![frm_RunRPT]![SNso] >= 0 Then
        strSQL_WHERE = strSQL_WHERE & "((TestCase.SONo) = [Forms]![frm_RunRPT]![SNso]) AND "
    End If
 
    'Use these values to process a final search string
    'There will be an extra " AND " at the end of the string, so we must compensate
    lngLen = Len(strSQL_WHERE) - 5      ' " AND " is 5 chars long
 
    If lngLen <= 6 Then     'Now we check the length to decide if we need to process
        'The where clause is NULL - Default to base recordsource
        Me.RecordSource = "Qry_SerialCkout2"
 
    Else
        'The where clause contained search info - we will process the string
        strSQL_WHERE = left$(strSQL_WHERE, lngLen)  'Trim off the last " AND " (5 chars)
 
        strSQL = strSQL_FROM & strSQL_WHERE     'Join the FROM and the final WHERE clauses
 
        Me.RecordSource = ("SELECT TestCase.SerialNoID, TestCase.TopAssyNo, TestCase.SerialNo, TestCase.Date, " & _
        "TestCase.CustPO, TestCase.DesignNotes, TestCase.PlateColor, TestCase.Line, TestCase.SONo, " & _
        "TestCase.CustName, TestCase.FullSN " & strSQL & " ORDER BY TestCase.SerialNo")
 
    End If
 
 
 
End Sub

The user inputs their required info on the "RunRpt" form and generates the report via the command btn. The data is pulled form the "TestCase" table that contains over 170,000 records via the above VBA code.

If the user is needing a range of serial numbers, the report is queried via the "SerialNo" field but the report shows the "FullSN" field. (FullSN = (SerialNo + Date(format mm/yy)). The "FullSN" field type is "number" on the table, so I'm not sure why your code did not work.

I am new to Access and VBA, but learn quickly when things are explained to me. Again, any help is greatly appreciated.

[swen]

I got the "Line skipper" to work. I had to add the "SerialNo" field to the report, change the code to look at that field, and calculate "+1".

Now I get a debug message "you entered an expression that has no value" on "lngLast = Me.SerialNo". This happens when the user does not enter any search criteria which invokes the msgbox code on the report open.

Do I need to change the location of the "Line skipper" code or am I missing something simple?

[TheSmileyCoder]

Can you post your code and tell me which line the code stops on? Sorry, I seem to have missed your first reply, thats why I have not returned earlier.

[swen]

When the report opens and the user did not input a criteria on the "RunRPT" form, the msgbox code on line "3" appears.(This is correct). After the user selects the "OK" to this msgbox, the debug error appears "you entered an expression that has no value" and focuses on line "54" of the below code. (The below code is the only code on this report).

Thank you for your assistance with this.

Private Sub Report_Open(Cancel As Integer)
If IsNull(Form_frm_RunRPT.BegSerial) And IsNull(Form_frm_RunRPT.EndSerial) And IsNull(Form_frm_RunRPT.SNso) = True Then
    MsgBox "The Serial Number range and Sales Order Number fields are blank." & vbCrLf & "Therefor, this report contains ALL Serial Numbers for this part."
    Exit Sub
End If
 
 
Dim lngLen As Long
Dim strSQL_FROM As String
Dim strSQL_WHERE As String
Dim strSQL As String
 
 
strSQL_FROM = "FROM TestCase "
 
strSQL_WHERE = "WHERE ((TestCase.TopAssyNo)=[Forms]![frm_RunRPT]![TopNo]) AND "
 
    If [Forms]![frm_RunRPT]![BegSerial] >= 0 Then
        strSQL_WHERE = strSQL_WHERE & "((TestCase.SerialNo) Between [Forms]![frm_RunRPT]![BegSerial] And [Forms]![frm_RunRPT]![EndSerial]) AND "
    End If
    If [Forms]![frm_RunRPT]![SNso] >= 0 Then
        strSQL_WHERE = strSQL_WHERE & "((TestCase.SONo) = [Forms]![frm_RunRPT]![SNso]) AND "
    End If
 
    'Use these values to process a final search string
    'There will be an extra " AND " at the end of the string, so we must compensate
    lngLen = Len(strSQL_WHERE) - 5      ' " AND " is 5 chars long
 
    If lngLen <= 6 Then     'Now we check the length to decide if we need to process
        'The where clause is NULL - Default to base recordsource
        Me.RecordSource = "Qry_SerialCkout2"
 
    Else
        'The where clause contained search info - we will process the string
        strSQL_WHERE = left$(strSQL_WHERE, lngLen)  'Trim off the last " AND " (5 chars)
 
        strSQL = strSQL_FROM & strSQL_WHERE     'Join the FROM and the final WHERE clauses
 
        Me.RecordSource = ("SELECT TestCase.SerialNoID, TestCase.TopAssyNo, TestCase.SerialNo, TestCase.Date, " & _
        "TestCase.CustPO, TestCase.DesignNotes, TestCase.PlateColor, TestCase.Line1, TestCase.SONo, " & _
        "TestCase.CustName, TestCase.FullSN " & strSQL & " ORDER BY TestCase.SerialNo")
 
 
    End If
 
 
End Sub
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'Format the appearance of the skipper line
    If lngLast = 0 Then
        'Special case, this is the first detail, before lngLast gets assigned a value.
        'Just assign value and exit
        lngLast = Me.SerialNo
        Me.LineSkipper.Visible = False
        Exit Sub
    End If
 
 
    'Record the last value used in detail format. This is important as a detail can be formatted multiple times
    If lngLast = Me.SerialNo Then
        'We are looking at the same detail as last time code ran, so just exit
        Exit Sub
    Else
        'We are looking at a new detail, lets compare it to the old one
        If Me.SerialNo > lngLast + 1 Then
            Me.LineSkipper.Visible = True
            lngLast = Me.SerialNo
            Else
            Me.LineSkipper.Visible = False
            lngLast = Me.SerialNo
        End If
    End If
 
End Sub
 

[TheSmileyCoder]

Okay, I see now what is happening.

Id be willing to bet that in your table, you have a record, where the Serial Nr has not been filled in. It is essentially Null which is not the same as 0. Your only seing this when your user does not enter a input range, because when he does enter a input range, the null records gets filtered away.

First find the record (you can usually find it by opening the table, and filtering for Null, and decide whether or not its meant to be there, or if its an error.

If its not meant to be there, delete it, and make sure your system prevents you from entering records without a serial number. (You can set the field to Required)

If its meant to be there, we need to handle the cases where we might meet a Null.

The way to handle this is to add an expression to catch the Nulls and turn them into something harmless. An example could be Nz (built in access function). It has 2 arguments, the argument we want to check (SerialNo) and the argument we want to use if SerialNo is null.
Example use:

lngLast=Nz(Me.SerialNo,-10)

Now if SerialNo is null, the null will be replaced by a harmless -10. The reason I choose -10 is because its not something that will occur naturally in your values, and because I've allready assigned another meaning to the case lngLast=0.

Now everywhere you use Me.SerialNo you should replace it with Nz(Me.SerialNo,-10)

[swen]

Quick question... would I replace Line 54 with "lngLast=Nz(Me.SerialNo,-10)"? Or insert it somewhere?

[swen]

Total newbe... disregard the previous question. I replaced every instance of "Me.SerialNo" but still get the same debug error message on the same line of code.

I verified that the SerialNo field on the base table is "Required" and that there are not any records with a Null value.

Any ideas?

[TheSmileyCoder]

Hmm....
Whats the SQL of Qry_SerialCkout2?

[swen]

The default query "Qry_SerialCkout2" (code below) only pulls data via a Serial range. Where as the code on the report includes the "SNso" (sales order) criteria.

Qry_SerialCkout2 code:

SELECT TestCase.SerialNoID, TestCase.TopAssyNo, TestCase.SerialNo, TestCase.Date, (IIf(IsNull([Line1]),[SONo],[SONo] & "-" & [Line1])) AS [Sales Order], TestCase.CustName, TestCase.CustPO, TestCase.DesignNotes, TestCase.PlateColor, TestCase.SONo, TestCase.Line1, TestCase.FullSN
FROM TestCase
WHERE (((TestCase.TopAssyNo)=[Forms]![frm_RunRPT]![TopNo]) AND ((TestCase.SerialNo) Between [Forms]![frm_RunRPT]![BegSerial] And [Forms]![frm_RunRPT]![EndSerial]) AND (([Forms]![frm_RunRPT]![BegSerial]) Is Not Null) AND (([Forms]![frm_RunRPT]![endSerial]) Is Not Null)) OR (((TestCase.TopAssyNo)=[Forms]![frm_RunRPT]![TopNo]) AND ((TestCase.SerialNo) Is Null) AND (([Forms]![frm_RunRPT]![BegSerial]) Is Null) AND (([Forms]![frm_RunRPT]![endSerial]) Is Null))
ORDER BY TestCase.SerialNo, TestCase.Date, TestCase.SerialNo;
 

[TheSmileyCoder]

Well I don't really have anything good to offer at this time.

Your welcome to upload the database here, or mail it to me, if you want it private. It can be hard to offer debugging assistance by distance. :P

The other thing to attempt is to make sure that I am correct in assuming that a Null Value is causing the problem.
Try adding above line 54:

If IsNull(Me.SerialNo) then
  Msgbox "Me.SerialNo is Null",vbexclamation
End If

If you get the msgbox then a Null is indead the source of the problem.

[TheSmileyCoder]

I would suggest reading this great articly by one of our experts:
Debugging VBA
Used correctly you can save loads of time, by knowing how to properly debug.

[swen]

Thank you for the article info... very informative.

I inserted the "if null" statement prior to line 54... It blows past the new "if null" statement with out giving the msgbox statement and then errors out with the same debug statement on the same line of code.

The DB is too large (compressed) to attach or email at this time. I'll try to rip out all but this section.

I appreciate all the help you are providing, everything helps.

[swen]

New development (realization)...
As I was ripping out this section of the DB to attach and verifying all the appropriate tables, qrys, etc. are intact. I ran the report with arbitrary criteria in ALL the fields of the "RunRPT" form for this report.

Guess what... I got the same debug msg (previous in the string) as if the criteria fields were blank.

I verified that the arbitrary info was/not in the table. It seems as though, regardless if there is criteria entered, if the qry/sql retrieves “0” records, this debug error occurs.

If this is plausible, how do I fix this? Do I need to start a new string for this?

All info is appreciated.

[swen]

Attached is the rip out of the DB.
Form "RunRPT" launches several reports.
The "Report Type" on RunRPT (for this string) is "Serial No(s) Checked Out"

Please let me know your thoughts and possible solutions. ALL are welcome.

[Prakash Gnana]

Thanks for info..

[TheSmileyCoder]

Your control SerialNo has the same name as its controlsource. Now Access defaults to giving controls the same names as their controlsource, which can cause alot of grief. I changed the NAME of the CONTROL from SerialNo to tb_SerialNo (tb for textbox) and the report ran fine.

EDIT: Seems I jumped the gun, still having issues. Will Look into it more.

[TheSmileyCoder]

I have looked at it and still can't seem to wrap my head around it. I don't know why access wants to format the detail when there is no detail. Its seems that the error message only occurs when you have no records (which makes sense, thats when the textbox has no value.)

So if all else fails, ignore it. Replace the line 54 with this:

        'Ignore the error, and proceed.
         'The error is still recorded,
         'which is why we can see it in err.Number
        On Error Resume Next
        lngLast = Nz(Me.tb_SerialNo, -10)
        If err.Number = -2147352567 Then
            'Empty detail, no need to proceed
            Me.LineSkipper.Visible = False
            'Resume normal error handling
            On Error GoTo 0
            Exit Sub
        End If
 
        If err.Number <> 0 Then
            'Another unexpected error occured
            MsgBox "Error [" & err.Number & "] occured." & vbNewLine & vbNewLine & err.Description, vbExclamation + vbOKOnly
        End If
 
        'Resume normal error handling
        On Error GoTo 0

[swen]

Thank you. Thank you for ALL your help.

This code works. Now I can review other requested changes for this DB.

I will review ALL the reports and forms for the use of the same name for the Control Name / controlsource. This may explain some other periodic glitches that have occurred with this DB.