Setting an autonumber to specific format? help pls need suggestion :)

[student02]

Hi guys.. Im still working on my project, it is a payment and enrollment system. Ive been thinking what to do with the Student ID since it requires a format like xx-xxxx, its possible that ill make two fields right? i mean the first xx on xx-xxxx to make it as a the last two digits on year? i mean like on 2010 it will be 10-xxxx then ill make the last for a autonumber? can you guys tell me how to set the autonumber to have four digits? im newbie at VB.. as in.. :) sorry. hehe

but its also ok if you give me some key words so that i could ask our best friend google.. hehehe

[SCBWV]

Try Format$(Number, "##-####"). If you want leading zeros, use Format$(Number, "00-####") or Format$(Number, "00-0000")

To be consistent, you can make the "00-0000" a public constant.

[student02]

in access?
like this one?
on data type
Number(##,####)? thanks ill try it now :)

[student02]

i think, i didnt get it.. where to put that format format$(00-####)?

[student02]

is it possible to set it on VBA?
im getting more confused now :(

[SCBWV]

Are you using VBA in Access or Visual Basic 6?

[student02]

Are you using VBA in Access or Visual Basic 6?

VB6 sir,,

[vb5prgrmr]

To begin with, you cannot format an autonumber within the field itself, which does not mean you cannot format it for display, but it does mean you will need another field to store this formatted number and it will need to be a text field. It also means that you will also need another table to store that last student number used for current year...

tblLast
iYear 'current year
iNo 'this is the auto number

Now, this above table will only ever have one record...

So, your process will be...

With the student name information, check to see if student exists.
If student does not exist then
Select * from tblLast
'check to see if we are in same year or have we moved into the next
if Format(Rs.Fields("iYear").Value, "yy") <> Format(Year, "yy") then
UPDATE tblLast SET iYear = " & Year & ", iNo = 1"
StudentIDStringValue = Format(Year, "yy") & "-0001"
else
StudentIDStringValue = Format(Rs.Fields("iYear").Value, "yy") & "-" & Format(Rs.Fields("iNo").Value + 1, "0000")
UPDATE tblLast SET iNo = [tblLast].[iNo]+1"
End IF

And then from there you could/can insert your record into your student table...

Good Luck

[student02]

hi vb5prgrmr,

I cant relate that much on those codes your using cuz I use the adodc component. and its the only way i know.. can you pls translate it to adodc? sorry for this newb attitude.. :(

[vb5prgrmr]

It is not that much different if I remember correctly, just replace rs with adodc1 and you should be able to use the same queries in its recordset arguement...

Good Luck

[student02]

oo sweet.. thanks, trying it now.

[student02]

i tried to apply it using adodc but it messes up and errors.

should i put this on same cmd button that saves the data entered.. or the cmdAdd button?

im trying to make it work.. hhmmm.. i still didnt inject the code.. debugging manually at notepad :)

[student02]

just finish this code, but got an error "Argument not optional"

Private Sub Command1_Click()

If (Text1.Text <> "" And Text2.Text <> "" And Text3.Text <> "" And Text4.Text <> "" And Text5.Text <> "" And Text6.Text <> "" And Text7.Text <> "" And Text8.Text <> "") Then


    Adodc1.Refresh
    Adodc1.Recordset.AddNew
    Adodc1.Recordset.Fields("First_Name") = Text1.Text
    Adodc1.Recordset.Fields("Gender") = Combo1.Text
    Adodc1.Recordset.Fields("Age") = Text2.Text
    Adodc1.Recordset.Fields("Address") = Text4.Text
    Adodc1.Recordset.Fields("Level") = Combo2.Text
    Adodc1.Recordset.Fields("Last_Name") = Text3.Text
    Adodc1.Recordset.Fields("Middle_Name") = Text5.Text
    Adodc1.Recordset.Fields("Contact_Number") = Text6.Text
    Adodc1.Recordset.Fields("Mother_Name") = Text7.Text
    Adodc1.Recordset.Fields("Father_Name") = Text8.Text
    Adodc1.Recordset.Update
    MsgBox "Data Added", , "Confirmation Message"
    
  
    
Else
    MsgBox "Please Enter Required Fields", , "Error"
End If

Text2.Text = ""
Text4.Text = ""
Combo1.Text = "- Select Gender - "
Combo2.Text = "- Select Level -"
Text5.Text = ""
Text6.Text = ""
Text7.Text = ""
Text8.Text = ""

adoTransaction.RecordSource = "SELECT * FROM Transaction WHERE First_Name = " & Text1.Text And "SELECT * FROM Transaction WHERE Last_Name = " & Text2.Text


    
adoTransaction.Refresh
adoTransaction.Recordset.Update
adoTransaction.Recordset.Field("Year") = Format(Year, "yy")
adoTransaction.Recordset.Field("Student_ID_PK") = Format(Year, "yy") + "-" + 0 + adoTransaction.Recordset.Field("iNo")
adoTransaction.Recordset.Update

End Sub

when it errors, and i press "DEBUG" it highlights the Year (Format(Year,"yy"))

[student02]

figured it out now i got an error again..

Runtime error 13 with same codes..

Type Mismatch.. :(help

[student02]

whoa, Finally got it.. But how about searching it, Since it is a text not a number, and it pops an error when i try to search it.. hehe. thank vbprg

[student02]

i dont know what is wrong with my code, but i cant find a data on my db...

Private Sub Command1_Click()
Adodc1.RecordSource = "SELECT * FROM Student_Info WHERE Student_ID_PK like " & Trim(Text1.Text)
Adodc1.Refresh


If (Adodc1.Recordset.RecordCount <> 0) Then
    Form2.Show
    Form1.Hide
Else
    MsgBox "Student not found!"
    Text1.Text = ""
    
End If


End Sub

It always show the msgbox even i have an id on my db..
btw the format of the Searched data is ##-####

[student02]

i get it, ill post it later after my presentation, Wish me luck guys.. :)

[vb5prgrmr]

Well Student02, I kind of got a kick out of reading your posts as my mind or experience has sometimes gone along the same path as you have shown your mind's travels... :)

Good Luck

[student02]

hi im back, just get back.. i and my group waited few hours for our turn, but the subject run out of time and we havent present, the final presentation will be tomorrow :)

btw, even i havent present yet, ill show what i did on my program :)

look, "Type the last four digit of Student's ID Number"
since the format is ##-####(The first two numbers indicates the year - 2000, since i dnt know how to show date on 2 digits.. hehe)

and the last four is saved as a autonumber..

after i searched a ID number

this appears for example,
the textbox nxt to the label that has a caption "ID Number"
is formatted like this at form_load

Text10 = Val(Year(Now)) - 2000 & "-00" & Format(Form1.Adodc1.Recordset.Fields("Student_ID_PK"), "####")

at adding students,

finally, this is the code i decided to use:

Private Sub cmdAdd_Click()

If (Text1.Text <> "" And Text2.Text <> "" And Text3.Text <> "" And Text4.Text <> "" And Text5.Text <> "" And Text6.Text <> "" And Text7.Text <> "" And Text8.Text <> "") Then





    Adodc1.Refresh
    Adodc1.Recordset.AddNew
    Adodc1.Recordset.Fields("First_Name") = Text1.Text
    Adodc1.Recordset.Fields("Gender") = Combo1.Text
    Adodc1.Recordset.Fields("Age") = Text2.Text
    Adodc1.Recordset.Fields("Address") = Text4.Text
    Adodc1.Recordset.Fields("Level") = Combo2.Text
    Adodc1.Recordset.Fields("Last_Name") = Text3.Text
    Adodc1.Recordset.Fields("Middle_Name") = Text5.Text
    Adodc1.Recordset.Fields("Contact_Number") = Text6.Text
    Adodc1.Recordset.Fields("Mother_Name") = Text7.Text
    Adodc1.Recordset.Fields("Father_Name") = Text8.Text
    Adodc1.Recordset.Update
    MsgBox "Student Enrolled!", , "Confirmation Message"
    MsgBox Val(Year(Now)) - 2000 & "-00" & Adodc1.Recordset.Fields("Student_ID_PK"), , "ID Number"
    
Else
    MsgBox "Please Enter Required Fields", , "Error"
End If

Text1.Text = ""
Text5.Text = ""
Text3.Text = ""
Text2.Text = ""
Text4.Text = ""
Combo1.Text = "- Select Gender - "
Combo2.Text = "- Select Level -"
Text5.Text = ""
Text6.Text = ""
Text7.Text = ""
Text8.Text = ""
End Sub

Its so sad that i cant make a good program since this was the last part of our subject last year, i it wasnt discussed by our professor because we semestral break comes before this topic, so she gave us a soft copy of that lesson but sadly, she mailed the copy on the wrong e-mail, and i have to experiment to came with this codes..
but im happy i made a working program,

and... Daniweb.com is a big help for a newbie like me..

@vb5prgrmr
heheeh.. thanks :)

thread solved :)

[cid777]

'* Function Update will update corresponding record in table Payments
'*******************************************************************************
Public Function Update(ByVal nPaymentID As Variant)
Dim sSQL As String
On Error GoTo Update_Error
getDbConnection
sSQL="update Payments set " + _
" PaymentID = " + SqlString(nPaymentID,adDecimal ) + _
" ,ContactID = " + SqlString(nContactID,adDecimal ) + _
" ,PaymentDate = " + SqlString(dPaymentDate,adDate ) + _
" ,PaymentlTime = " + SqlString(dPaymentlTime,adDate ) + _
" ,PaymentForMonth = " + SqlString(dPaymentForMonth,adDate ) + _
" ,Amount = " + SqlString(sAmount,adVarChar ) + _
" ,AmountCurrency = " + SqlString(sAmountCurrency,adVarChar ) + _
" ,Notes = " + SqlString(sNotes,adVarChar ) + _
" where PaymentID = " + SqlString(nPaymentID,adDecimal )
objConn.Execute sSql, , adExecuteNoRecords

If Not objCtx Is Nothing Then objCtx.SetComplete
Exit Function
Update_Error:
If Not objCtx Is Nothing Then objCtx.SetAbort
Err.Raise Err.Number, Err.Source & " (PAYMENTSClass.Update)", Err.Description
End Function