generating an auto number

[nawaray]

:ehh: helloo...

i have this renting application in which the user enters his name, phone , amount, date , payment to rent a flat ,,

i have a table called rent which is related to this its fields are :

name, telephone,amount, date, paid and there is one more field called serial which is the primary key, i want every user to have a different serial number ( which will be generated automatically every time i add a user)

for example:

when user one will be added he will have serial number 1

when user two is added he will have serial number 2

user 10 serial 10 and so on ,, i tried doing many things but it doesnt work ,

this is my code for the add button:

Private Sub cmdSave_Click()  
adoBooks.Recordset.Fields("Name") = txtName.Text 
adoBooks.Recordset.Fields("telephone") = txtPhone.Text
adoBooks.Recordset.Fields("amount") = txtAmount.Text  
adoBooks.Recordset.Fields("Date") = txtDate.Text
adoBooks.Recordset.Fields("paid") = Check1.Value 
adoBooks.Recordset.Fields("serial") = txtSerial.Text 

adoBooks.Recordset.Fields("serial") = serial
adoBooks.Recordset.Update

and this is when the form is loaded:

serial = adoBooks.Recordset.Fields("serial")  
serial = serial + 1

where serial is an integer

i tried doing this: ( by adding a function)

Public Function getUniqID(tblname As String, fldname As String) As Long
    Dim tmpRs As ADODB.Recordset
    Dim MaxID As Long
Set tmpRs = New ADODB.Recordset
 tmpRs.CursorLocation = adUseClient
   tmpRs.Open "select max(" & fldname & ") as maxID from " & tblname ', Cnn, adOpenDynamic, adLockOptimistic
   If IsNull(tmpRs("maxID")) = True Then 
MaxID = 0 
Else MaxID = tmpRs("maxID")
  If MaxID = 0 Then
   intSerialNo = 1  
 Else
    intSerialNo = MaxID + 1
    End If
    tmpRs.Close
End Function

Private Sub Form_Load()

Call getUniqID("rent", "serial")

End Sub

Private Sub cmdSave_Click()

 Call getUniqID("rent", "serial")

 adoBooks.Recordset.Fields("Name") = txtName.Text
 adoBooks.Recordset.Fields("telephone") = txtPhone.Text
 adoBooks.Recordset.Fields("amount") = txtAmount.Text
 adoBooks.Recordset.Fields("Date") = txtDate.Text
 adoBooks.Recordset.Fields("paid") = Check1.Value

 adoBooks.Recordset.Fields("serial") = intSerialNo
 adoBooks.Recordset.Update

End Sub

but i keep on getting an error in the line colored red
any help please?

nawaray

[Comatose]

Something to consider, is setting the database reference to nothing. This should close and unload the adobooks object from memory, and allow you to re-create, and re-open it. I might be mistaken, but it looks like it's mad that you are trying to open a database that is already open.....

[Mike Vance]

:ehh: helloo...

i have this renting application in which the user enters his name, phone , amount, date , payment to rent a flat ,,

i have a table called rent which is related to this its fields are :

name, telephone,amount, date, paid and there is one more field called serial which is the primary key, i want every user to have a different serial number ( which will be generated automatically every time i add a user)

for example:

when user one will be added he will have serial number 1

when user two is added he will have serial number 2

user 10 serial 10 and so on ,, i tried doing many things but it doesnt work ,

this is my code for the add button:

Private Sub cmdSave_Click()  
adoBooks.Recordset.Fields("Name") = txtName.Text 
adoBooks.Recordset.Fields("telephone") = txtPhone.Text
adoBooks.Recordset.Fields("amount") = txtAmount.Text  
adoBooks.Recordset.Fields("Date") = txtDate.Text
adoBooks.Recordset.Fields("paid") = Check1.Value 
adoBooks.Recordset.Fields("serial") = txtSerial.Text 

adoBooks.Recordset.Fields("serial") = serial
adoBooks.Recordset.Update

and this is when the form is loaded:

serial = adoBooks.Recordset.Fields("serial")  
serial = serial + 1

where serial is an integer

i tried doing this: ( by adding a function)

Public Function getUniqID(tblname As String, fldname As String) As Long
    Dim tmpRs As ADODB.Recordset
    Dim MaxID As Long
Set tmpRs = New ADODB.Recordset
 tmpRs.CursorLocation = adUseClient
   tmpRs.Open "select max(" & fldname & ") as maxID from " & tblname ', Cnn, adOpenDynamic, adLockOptimistic
   If IsNull(tmpRs("maxID")) = True Then 
MaxID = 0 
Else MaxID = tmpRs("maxID")
  If MaxID = 0 Then
   intSerialNo = 1  
 Else
    intSerialNo = MaxID + 1
    End If
    tmpRs.Close
End Function

Private Sub Form_Load()

Call getUniqID("rent", "serial")

End Sub

Private Sub cmdSave_Click()

 Call getUniqID("rent", "serial")

 adoBooks.Recordset.Fields("Name") = txtName.Text
 adoBooks.Recordset.Fields("telephone") = txtPhone.Text
 adoBooks.Recordset.Fields("amount") = txtAmount.Text
 adoBooks.Recordset.Fields("Date") = txtDate.Text
 adoBooks.Recordset.Fields("paid") = Check1.Value

 adoBooks.Recordset.Fields("serial") = intSerialNo
 adoBooks.Recordset.Update

End Sub

but i keep on getting an error in the line colored red any help please?

nawaray

Why can't you change the MS Access data type to Autonumber for your serial field. When you add a record the serial number will be created for you automatically and always Uniquely. After the execution of adoBooks.Recordset.Update you can assign a text field like txt_Serial.text = adoBooks.Recordset.fields("serial") or txt_Serial.text =adoBooks.Recordset!serial thus returning the serial number from MSAccess. If you ever switch to SQL Server you must set .CursorLocation to get the new serial number.

Mike