My code works when it wants to?

[ewong1]

I have the following code that is causing some problems for me and I was hoping someone might be able to help. It works, sometimes, when it wants to.. and then there are other times when it processes my request once and repeats the same values over and over. What it is built to do is look into my batch table and generate a new batch number. if the max is 1 the number generated should be 2 and reports out as 2006-00002. As I had said, it works half of the time and doesn't the other half and i'm not sure why. Thanks for your help!

Public Sub AddNewWPI()
    
    Dim db As Database
    Dim rs As Recordset 'Batch Table
    Dim strSQLWhere As String
    Dim intNewBatch As Integer
    Dim txtNewBatch As String
    Dim intAddNewCount As Integer
    Dim txtListNewBatch As String
    Dim txtYear As String
    
    txtYear = CStr(Format(Date, "yyyy"))
    
    
    txtListNewBatch = "New WPI #s: " & vbCrLf
    
    intAddNewCount = 0
    strSQLWhere = "SELECT txtBatch FROM WPI_Batch WHERE format(dtmDateCreated, 'yyyy') = format(date(), 'yyyy')"
        
    If IsNull(Forms!frmMainMenu!intAddNewCount) Or Forms!frmMainMenu!intAddNewCount = 0 Then
        intAddNewCount = 1
    Else
        intAddNewCount = Forms!frmMainMenu!intAddNewCount
    End If
    
    '----------------'
    'Open Batch Table'
    '----------------'
    
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset(strSQLWhere, dbOpenDynaset)
    
    Do Until intAddNewCount = 0
        
    rs.MoveLast
    
    intAddNewCount = intAddNewCount - 1
    
    intNewBatch = rs!txtBatch * 1 + 1
    
    Dim vNewBatch As String
    vNewBatch = CStr(intNewBatch)
    
    If Len(vNewBatch) = 1 Then
        txtNewBatch = "0000" & vNewBatch
    Else
        If Len(vNewBatch) = 2 Then
            txtNewBatch = "000" & vNewBatch
        Else
            If Len(vNewBatch) = 3 Then
                txtNewBatch = "00" & vNewBatch
            Else
                If Len(vNewBatch) = 4 Then
                    txtNewBatch = "0" & vNewBatch
                Else
                    If Len(vNewBatch) = 5 Then
                        txtNewBatch = vNewBatch
                    Else
                    End If
                End If
            End If
        End If
    End If
    
    db.Execute "Insert Into WPI_Batch (idEmployee, txtBatch) Values (" & Forms!frmMainMenu!idEmployee & ", " & txtNewBatch & ")"
    
    txtListNewBatch = txtListNewBatch & txtYear & "-" & txtNewBatch & vbCrLf
    
    Forms!frmMainMenu!txtNewBatchList = txtListNewBatch
    
    rs.Requery

    Loop
    
    DoCmd.OutputTo acOutputReport, "rptNewWPI", acFormatRTF, "NewWPI.rtf", -1

    rs.Close
    db.Close
End Sub

 

[DB7]

It appears to be a refreshing/locking issue?
I see you have rs.Requery, in order to save the newly inserted record.
Is there a form open, using the same recordset, not allowing complete iteration through the table?
strSQLWhere is not sorted, could that make a difference?

Sorry for the lack of insight, but as a small digression, here's a similiar code I use to do approx. the same thing, using DMax & String().

'Auto Inv. Nº, minimum 4 characters
Dim iMax As String
iMax = Nz(DMax("txtInvoiceNumber", "tblService") + 1, 1)'since iMax is an integer, preceeding zeros are removed
If Len(iMax) < 4 Then
iMax = String(4 - Len(iMax), "0") & iMax
End If
txtInvoiceNumber = iMax

 

[DB7]

Nice Pat, didn't occur to me to use the Format property.
...wouldn't have anticipated such a result.
Thx!

 

[ewong1]

Thanks so much for your input! Both of your suggestions were effective. I ran into another problem I was hoping you might be able to help me out with as well.

I was running some tests and I noticed that I am not able to run the module with two users logged into the system because it creates duplicate batch numbers. I was hoping someone might have some insite as to how I am to prevent duplicate batch numbers or how else I might be able to prevent this issue.

Thanks again for your help!

 

[AngelsGuardian]

Here is a bit of code i use to create an event number for a log
The output text is ex.: 20060119003 as in (2006-01-19 event 003)

Dim strevent, eventDate, prtEvent, EventNumber As String
    
eventDate = Format(Date, "yyyymmdd")
strevent = DMax("[ID]", "tblLog") '*** Look in the tblLog table and search for the highest record number in the ID column***

prtEvent = Right(strevent, 3)
datenow = Format(Date, "yyyy-mm-dd")

If Left(strevent, 8) = eventDate Then
            
            EventNumber = strevent + 1
        
        Else
            
            EventNumber = eventDate & "000"

of course...the column is set to Text not number

Hope it helps