MS Access 2003 - Auto Check Numbering in Sequence

Question

Hi everyone. I am building a form that prints Pay Checks and I am trying to automatically number the checks.  I am trying to find a way to input a "Starting Check No." in a text box and then click a button to automatically number the first record with the Starting Check No. and each subsequent record with the No. assigned + 1.  Can anyone please help on how I can accomplish this. Thanks.

Answer 1

Hi

 

Are your checks held in a table? Do you need just a unique number?  If you have a table with one row for each check you can set up an auto number field in the table design, and Access will look after this for you. If you want to use a specific numbering scheme you probalby need to add a field to the record to hold this number against pay check data. To advise on how to do this we need to know a little more about you data structure(s).

 

Regards

 

ADG

Answer 2

Hi ADG, nice to hear from you again. Hope you are well and doing fine.  By the way, the calculating loan help you gave turned out great, thanks again.

I have a table that contains 7 fields: PayCheckID, RepID, RepName, PayDate, CheckAmt, PayYN, CheckNo, and CkReprint. These are for commission checks earned by Reps where PayYN is a Yes/No check box that allows me to print or not print a Rep's check. Each row in the table is for a single Rep's total commision check for a particular Pay Date. 

On the form there is a Combo Box that selects the PayDate.  On the Sub Form's Header I have labels: Rep, Check Total, Pay Y/N, Check No.and an unbound text box labeled "Starting Check #" and a button labeled "Add".

In the Header Detail I have the fields: RepID, RepName, CheckAmt, PayYN, and CheckNo.

My goal is to be able check the "Pay Y/N" check box for any Rep I want to print a check for, enter a "Starting Check #", then click the "Add" button which would enter in the first CheckNo field the Starting Check # and move to the next record and add one to the CheckNo field for all the reamining records.
I do not know how to write code, but I am beginning to learn. Here is the code I was trying that I got from studying different code that I thought might be close.    

 

Private Sub Add_Click()
    Dim db As DAO.Database
    Dim rsCkNo As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    Set db = CurrentDb
    i = 0
    strSQL = "SELECT tblPayCheckLedger.CheckNo FROM tblPayCheckLedger WHERE PayDate = Me.PayDate"
    Set rsCkNo = db.OpenRecordset(strSQL)
    Do While Not rsCkNo.EOF
    If Me.CheckNo = "" Then
        Me.CheckNo = Me.SelectCheckNo.Value
    Else
        rsCkNo = (CheckNo) + i
        Me.CheckNo = Me.CheckNo + i
        i = i + 1
        rsCkNo.MoveNext
    Loop

End Sub

 

Thank you for taking the time to look at this.

Regards, Steve

Answer 3

Hi

 

Your on the right tracks. Me.checkno will be referring to your control on the form. To change the CheckNo in the table ( using the recordset) use:

 

rsChkNo.edit

rsChkNo!CheckNo = me.checkno +1

rsChkNo.update

 

You are also missing an end if above.

 

Regards

 

ADG

Answer 4

Hi ADG,

 

I placed the code in here:

 

Private Sub Add_Click()
    Dim db As DAO.Database
    Dim rsCkNo As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    Set db = CurrentDb

    strSQL = "SELECT * FROM tblPayCheckLedger WHERE PayDate = [Forms]![frmPayChecks]![PayDate]"
    Set rsCkNo = db.OpenRecordset(strSQL)
    Do While Not rsCkNo.EOF
    If Me.CheckNo = "" Then
        Me.CheckNo = Me.SelectCheckNo.Value
    Else
        rsChkNo.Edit
        rsChkNo!CheckNo = Me.CheckNo + 1
        rsChkNo.Update
    End If
        rsCkNo.MoveNext
    Loop

End Sub

 

However, a "run-time error '3061': Too few parameters. Expected 1."  occurs.

When I click the "Debug" button the code highlighted in yellow above appears.

 

Is ther an easier way? Or am I close?

 

Thanks, Steve

Answer 5

Hi

 

Your close. Change the below:

 

strSQL = "SELECT * FROM tblPayCheckLedger WHERE PayDate = [Forms]![frmPayChecks]![PayDate]"

to

 

strSQL = "SELECT * FROM tblPayCheckLedger WHERE PayDate = #" & format( [Forms]![frmPayChecks]![PayDate],"MM-DD-YYYY") & "#"

 

I think you need to change:

 

If Me.CheckNo = "" Then
        Me.CheckNo = Me.SelectCheckNo.Value
    Else
        rsChkNo.Edit
        rsChkNo!CheckNo = Me.CheckNo + 1
        rsChkNo.Update
    End If

to

 

If Me.CheckNo = "" Then Me.CheckNo = Me.SelectCheckNo.Value

rsChkNo.Edit

rsChkNo!CheckNo = Me.CheckNo

Me.CheckNo  = Me.CheckNo + 1
rsChkNo.Update

 

Regards

 

ADG

Answer 6

Hi ADG,

 

I placed the code and we got passed the strSQL error, but it keeps erring at the "If Then" statement. Here is where we are:

 

Private Sub Add_Click()
    Dim db As DAO.Database
    Dim rsCkNo As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    Set db = CurrentDb
   
    strSQL = "SELECT * FROM tblPayCheckLedger WHERE PayDate = #" & Format([Forms]![frmPayChecks]![PayDate], "MM-DD-YYYY") & "#"
    Set rsCkNo = db.OpenRecordset(strSQL)
    Do While Not rsCkNo.EOF
        If Me.CheckNo = "" Then Me.CheckNo = Me.SelectCheckNo.Value
            rsChkNo.Edit
            rsChkNo!CheckNo = Me.CheckNo
            Me.CheckNo = Me.CheckNo + 1
            rsChkNo.Update

         End If
    rsCkNo.MoveNext
    Loop

 

End Sub

 

I arranged the code in many different places and indentations. Mostly I get a compile error "End If without block If."

I tried placing the "End If" and "Else" in different places, but nothing.

What do you think?

Answer 7

Hi

 

Sorry did not make it clear, delete the end if as I changed the If statement to be one line.

Answer 8

Eureka!

 

Thanks ADG, I really appreciate your help, it finally works. Thanks a million.  BTW, what city do you live in and do you do any freelance work?

 

Here's the final code:

 

Dim db As DAO.Database
    Dim rsCkNo As DAO.Recordset
    Dim strSql As String
    Dim i As Integer
   
    Set db = CurrentDb
   
    i = Me.SelectCheckNo
    strSQL = "SELECT *  FROM tblPayCheckLedger WHERE PayDate =#" & format([Forms]!{frmPayChecks]![PayDate],"MM-DD-YYY") & "#"
    Set rsCkNo = db.OpenRecordset(strSQL)
    Do While Not rsCkNo.EOF
        rsCkNo.Edit
          rsCkNo("CheckNo") = i
        rsCkNo.Update
        i = i + 1
        rsCkNo.MoveNext
    Loop
    Me.Refresh

 

Answer 9

Hi Steve

 

Glad to hear it works OK. I live in rural Shropshire in England, although I am working 3-4 days a week near Paris at the moment. I do some freelance work, but I am currently overstretched ..

 

Regards

 

ADG 

Answer 10

HI ADG,

Glad to hear you're doing fine.  Thanks again for your help.  I feel like I need to repay you some how. Is there anything I can do for you? Thanks a million.

Respectfully,

Steve