numbering checking

I have a form that displays information of courses. So when I click one of the courses I want to print a certificate for it. My question is how can I make this number to increase. I want to start the first certificate to print like this 0001 and the next one will increase like 0002, etc.
So my question is: how can I go and check the last certificate printed to my access database and then print the next one?

thank you for your help...

mapyAsked:
Who is Participating?
 
frodomanCommented:
Actually I think it should be strGetNumber = .Fields(0)  <-- Meaning the first field retrieved instead of referencing by name.

If you want to use a name, change your sql to

 strCheck = "SELECT MAX(Certificate) AS Certificate FROM Course_Registration"

and then leave the other line as: strGetNumber = .Fields("Certificate")
0
 
frodomanCommented:
I assume you have a table where you store the certificates after printing them?  

It should be a matter of doing SELECT MAX(CertNumber) FROM CertTable, then adding 1 to this number and inserting a new record into the table.

0
 
Dang123Commented:
mapy,
    frodoman is correct, also if you are keeping information for more than one type of certificate in your table, you can add the WHERE clause making it :

SELECT MAX(CertNumber) FROM CertTable WHERE CertType = "Computer Science"

Dang123

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
inthedarkCommented:
If you are using a table which contains your certificate details add an AutoNumber field to the record.  You can then use this field as it will always be unique. I expect you already have an autonumber field.  I would also add another field say called Printed.



' open your certificate records.

SQL = "Select * From [MyTable] Where ([Printed]=False);"
Set RS = DB.OpenRecordSet(SQL, dbOpenDynaset, DbSeeChanges)


' loop until all have been printed
Do While Not Rs.Eof

    'Print the cretificates
    PrintCert RS("MyAutoNumberField") ' call a sub to print the certificate
   ' PrintCert RS ' or you could pass the recordset to the subroutine.
   
   ' No mark the record as printed.
   WorkSpaces(0).BeginTrans
   RS.Edit
   RS("Printed") = True
   RS.Update
   Workspaces(0).Committrans

   RS.MoveNext
Loop
   
Hope this helps~)
0
 
FicusCommented:
Those suggestions are correct.  If you are using a access data base you could use the primary key as an auto number and ensure that you keep a proper consecutice series of numbers with no jumps or duplicates.
0
 
mapyAuthor Commented:
Hi guys, help me out a little bit more.
I have done this:
Dim strCheck As String
Dim strGetNumber As String
Dim strCertificateNum As String
Dim rsMax As ADODB.Recordset
Set rsMax = New ADODB.Recordset
       
        strCheck = "SELECT MAX(Certificate) FROM Course_Registration"
        With rsMax
            .Open strCheck, cnConn, adOpenKeyset, adLockOptimistic
            If Not .EOF Then
                strGetNumber = .Fields("Certificate")
            Else
                strGetNumber = "SN001"
            End If
            .Close
        End With
        Set rsMax = Nothing
        strGetNumber = Replace(strGetNumber, "SN", 0)
        strCertificateNum = "SN00" & (strGetNumber + 1)
        Text1.Text = strCertificateNum


and i'm getting an error message: 3265 "Item cannot be found in the collection corresponding to the requested name or ordinal." and it points to this line "strGetNumber = .Fields("Certificate")"
any suggestions.
thank you again.

0
 
Dang123Commented:
The query is returning a single value, the highest number in the "Certificate" column. I beleave it would be named "MAX(Certificate)" or something similar. You can use AS to rename the column if you would like.
0
 
mapyAuthor Commented:
doesn't work dang123. i get the same error.
0
 
frodomanCommented:
Try

strGetNumber = .Fields(1)
0
 
frodomanCommented:
You may also want to consider replacing this:

strCertificateNum = "SN00" & (strGetNumber + 1)

With this:

strCertificateNum = "SN" & right("000" & (strGetNumber + 1),4)


This way you won't have to change your code when you reach 100 and again when you reach 1000.
0
 
mapyAuthor Commented:
Frudo,
why you replace the field customer to 1? i tried and i still get the same error.
after you guys doing the qry: "select max(certificate) from table" how do you assign the biggest number to a variable? You have to open your table and assign it. how do you do it? Thanks a million for your help.
0
 
mapyAuthor Commented:
thank you Frudo for the extra codding correction of trimming. I'll use it. thank you!
0
 
mapyAuthor Commented:
it worked frodoman!!   I had to write: AS certificate.

Thank you all you guys for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.