Consecutive numbers with a letter prefix? (1 Viewer)

mounty76

Registered User.
Local time
Today, 08:18
Joined
Sep 14, 2017
Messages
341
Hello,

I'm about to try and add a new function to a db I made but wondered if anyone had any top tips before I try and struggle myself!

db consists of many fields but one is a drop down menu from which the user can select various training courses.

Some of the courses need to have an associated certificate consecutive number, we have to have these as AA001/2017, AA002/2017, etc. then reset back to 001 at the start of each year.

My problem that I'm thinking is that out of 25 courses only 4 need to have this and out of them 4 courses each one needs to have different letters/consecutive number, so it might look something like the below:

Course ConNumber

A AA001/2017
A AA002/2017
B BB001/2017
C CC001/2017
A AA003/2017
C CC002/2017

I'd like the consecutive numbers to be automatically generated depending on the course that is chosen.

I'm thinking along the lines of 4 update queries but then not too sure....:eek:

Any ideas?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:18
Joined
May 7, 2009
Messages
19,175
you need a function to do that.
copy and paste the function in
a standard module:

Code:
Public Function NextCertificateNumber(course As Variant) As String
    Dim nextCon As Variant
    course = Trim(course & "")
    If course = "" Then Exit Function
    
    nextCon = DMax("ConNumber", "yourTable", _
                "Left(ConNumber,1)=" & Chr(34) & course & Chr(34) & " " & _
                "And Right(ConNumber,4)=" & Chr(34) & Year(Date) & Chr(34))
    If IsNull(nextCon) Then
        nextCon = String$(2, course) & "001/" & Year(Date)
    Else
        nextCon = Left(nextCon, 2) & Format(Val(Mid(nextCon, 3, 3)) + 1, "000") & Right(nextCon, 5)
    End If
    
    NextCertificateNumber = nextCon
    
End Function

your form should be Bound to the table.
on your form, BeforeInsert Event (before record is added),

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ConNumber = NextCertificateNumber([yourCombo])
End Sub

your combo should include as first column the letter of
courses( "A", "B", "C", etc.) since this is where the
function is based.


replace "yourtable" in the function with your real
table name, and all fields with real field names (of course).
 

mounty76

Registered User.
Local time
Today, 08:18
Joined
Sep 14, 2017
Messages
341
Thanks for much for this, should hopefully get round to trying it this afternoon, I'll let you know how I get on!

One thing I should have explained a little better an example of a course would be 'Crisis Management and Human Behaviour' this cert number needs to be CMHB001/2017 another course would be Crowd Management so would need to be CM001/2017

Sorry I was trying to simply what I was trying to achieve! You've given me a great place to start though, thanks again
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:18
Joined
May 7, 2009
Messages
19,175
is that so...
just add those first letters on the combo ("A", "B", "CMHB", "CM", etc) as
the first Column.
modify the code to this:
Code:
Public Function NextCertificateNumber(course As Variant) As String
    Dim nextCon As Variant
    Dim lenCourse As Integer 
    course = Trim(course & "")
    If course = "" Then Exit Function
    lenCourse = Len(course)
    nextCon = DMax("ConNumber", "yourTable", _
                "Left(ConNumber," & lenCourse & ")=" & Chr(34) & course & Chr(34) & " " & _
                "And Right(ConNumber,4)=" & Chr(34) & Year(Date) & Chr(34))
    If IsNull(nextCon) Then
        nextCon = course & "001/" & Year(Date)
    Else
        nextCon = course & Format(Val(Mid(nextCon, lenCourse + 1, 3)) + 1, "000") & Right(nextCon, 5)
    End If
    
    NextCertificateNumber = nextCon
    
End Function
 

mounty76

Registered User.
Local time
Today, 08:18
Joined
Sep 14, 2017
Messages
341
Thanks very much for this, I've opted for a slightly different method as I think I was over complicating things, one last thing you could maybe help me with?? :)

I have a form that opens and displays the last record, when someone clicks on the next line to enter a new record I want the db to automatically fill in three fields in that record, the fields are not on the form though because I don't want the user to see them, the three fields I need auto filling in are:

Course Name (to complete automatically as Crowd Management)
Approval1 (to complete automatically as TRUE)
Approval2 (to complete automatically as FALSE)

Assuming some code on AfterInsert or BeforeInsert would do the trick?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:18
Joined
May 7, 2009
Messages
19,175
You should add those fields on your form. If the form is single form or continuous form, set the visible property of the 3 to No. If it is a datasheet, on Load Event, set their ColumnHidden property to True.

This will make the three textbox controls hidden.

Add code to BeforeInsert event of the form :

Private Sub Form_BeforeInsert(Cancel As Integer)
[Course Name] ="Crowd Management"
[Approval1]=True
[Approval2]=False
End Sub

***
Sorry if the code did not worked.
 

mounty76

Registered User.
Local time
Today, 08:18
Joined
Sep 14, 2017
Messages
341
Hi,

No problem about the code, I was over thinking it to be honest!

Thanks very much for the autofill on the fields, works a treat!

I don't suppose you know a way of putting the new record blank fields at the top of a form instead of the bottom? I've read a few threads but can't seem to find a way.

It's not really too much of a problem as I have the form open at the last record so you're not having to scroll down all the way.

Thanks again for code for the autofill!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:18
Joined
May 7, 2009
Messages
19,175
Sorry, i dont. You can always press the New Record on the Navigation Button (the one with record number and search) so you wont need to scroll.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:18
Joined
Feb 19, 2002
Messages
42,986
You don't ever need to scroll. Just press the yellow asterisk to go to a new record. Or add a NewRecord button or open the form in DataEntry mode so you don't even see old records.
 

Users who are viewing this thread

Top Bottom