Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatic counting number by script (2003)

    The code below was already given by Francis.
    But I had to change it. Because the fact number has to exist in two part.
    Year - number
    Everything is working fine when there is already data in it.
    But when I start with an empty database, I'll get the folowing error:

    run-time error 3021:
    no current record

    This the code that I use:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim JaarNu, Verschil
    If Me.NewRecord Then
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qrymaxnrfactuur", dbOpenDynaset)
    If IsNull(rst![factnr]) Then
    Me![txtFactnr] = 1 'de 1 hier kunt gij vervangen door gelijk welke beginwaarde
    Else
    JaarNu = Year(Now())
    rst.MoveFirst
    Verschil = JaarNu - rst!jaarfact
    If Verschil > 0 Or Verschil = "" Then
    'MsgBox JaarNu & "-" & rst!jaarfact & "-" & Verschil
    Me![txtFactnr] = 1
    Else
    Me![txtFactnr] = rst!factnr + 1
    End If
    End If
    rst.Close
    Set db = Nothing
    End If

    I'll hope that some one can help me?

    greetings Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatic counting number by script (2003)

    Does it work better if you change

    Set rst = db.OpenRecordset("qrymaxnrfactuur", dbOpenDynaset)
    If IsNull(rst![factnr]) Then
    ...

    to

    Set rst = db.OpenRecordset("qrymaxnrfactuur", dbOpenDynaset)
    If rst.EOF Then
    Me![txtFactnr] = 1
    ElseIf IsNull(rst![factnr]) Then
    ...

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic counting number by script (2003)

    Many thanks, that works just fine if there is indeed no record in it jet.
    But when there is already a record, he does not add +1 to previous record :-(
    Greetings Nancy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatic counting number by script (2003)

    I'm afraid I don't know enough about the setup to know how it works. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic counting number by script (2003)

    Hello,
    I upload with this post my zip file within test.mdb
    There is now 1 record in tblfactuur. My first problem (when there was no records) is solved.
    But when there is a record, it will not fill in the field "txtfactnr" in the form "FrmFacturatieAdd"
    Could you please help me here?
    greetings Nancy
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatic counting number by script (2003)

    I'd put the code in the Before Update event of the form. This will be executed as soon as the user starts entering data in a new record.

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim JaarNu As Integer
    Set db = CurrentDb
    Set rst = db.OpenRecordset("qrymaxnrfactuur", dbOpenDynaset)
    If rst.EOF Then
    Me!<!t>[intFactnr]<!/t> = 1
    Else
    JaarNu = Year(Date)
    If JaarNu > Nz(rst!jaarfact, 0) Then
    Me!<!t>[intFactnr]<!/t> = 1
    Else
    Me!<!t>[intFactnr]<!/t> = rst!factnr + 1
    End If
    End If
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    End Sub

    See the attached version.
    Note: I changed the year and invoice number (factuurnummer) to numeric.
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic counting number by script (2003)

    Hello Hans,
    Many many thanks.
    Everything works just fine like it should.

    I have to make this one also in version 2007, I 'll hope that it will not give any troubles.

    Thanks again

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatic counting number by script (2003)

    Good luck - I haven't tried Access 2007 yet, but I don't think it should cause problems.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •