none
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.

    Monday, July 07, 2008 11:43 PM

Answers

  • Hi

     

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

    Thursday, July 10, 2008 11:15 AM

All replies

  • 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

    Tuesday, July 08, 2008 8:21 AM
  • 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

    Tuesday, July 08, 2008 8:06 PM
  • 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

    Wednesday, July 09, 2008 9:14 AM
  • 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

    Thursday, July 10, 2008 1:06 AM
  • 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

    Thursday, July 10, 2008 7:05 AM
  • 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?

    Thursday, July 10, 2008 8:05 AM
  • Hi

     

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

    Thursday, July 10, 2008 11:15 AM
  • 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

     

    Friday, July 11, 2008 4:55 AM
  • 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 

    Friday, July 11, 2008 6:01 PM
  • 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

     

    Saturday, July 12, 2008 5:57 AM