: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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

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

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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

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

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

where serial is an integer

i tried doing this: ( by adding a function)

(Toggle Plain Text)

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

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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

Private Sub Form_Load()
 
Call getUniqID("rent", "serial")
 
End Sub

Private Sub Form_Load() Call getUniqID("rent", "serial") End Sub

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

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

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