generating an auto number

Question

nawaray 0 Newbie Poster

21 Years Ago

: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

visual-basic

3 Contributors
2 Replies
465 Views
1 Week Discussion Span
Latest Post 21 Years Ago Latest Post by mdv3441

Dani AI

Generated 9 Months Ago

A few quick notes that pinpoint what's breaking and how to fix it without changing your table design.

The immediate runtime error is almost always caused by the helper function not having a live Connection and by not returning its value. In the posted getUniqID you open a Recordset with only SQL (no connection object), then later rely on a module variable; that will fail if the recordset is closed/empty or the connection isn’t supplied. Also be careful with field names like Date (wrap them in square brackets) and use Option Explicit so missing declarations (for intSerialNo or serial) are caught early.

If you must compute MAX+1 in code, call the database via the Connection and return the value explicitly. Example (use your existing open ADODB.Connection object):

Function GetNextSerial(cnn As ADODB.Connection, tbl As String, fld As String) As Long
  Dim rs As ADODB.Recordset
  Dim sql As String
  sql = "SELECT MAX([" & fld & "]) AS MaxID FROM [" & tbl & "]"
  Set rs = cnn.Execute(sql)
  If rs.EOF Or IsNull(rs!MaxID) Then
    GetNextSerial = 1
  Else
    GetNextSerial = CLng(rs!MaxID) + 1
  End If
  rs.Close
  Set rs = Nothing
End Function

A safer pattern is to let the database assign the value and read it back after the insert. After doing the insert on the same Connection you can read the new autonumber with: Set rs = cnn.Execute("SELECT @@IDENTITY AS NewID") and use rs!NewID. (For SQL Server use SCOPE_IDENTITY() or the OUTPUT clause.)

Checklist: verify the Connection is open, avoid reading fields when the recordset is at EOF, ensure the function assigns its return value, and handle concurrency — SELECT MAX(...) + 1 is vulnerable to race conditions in multi-user apps. As and hinted, the most robust approach is to let the DB generate the key and then retrieve it from the same connection.

All 2 Replies

Comatose 290 Taboo Programmer

21 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mdv3441 17 Newbie Poster · Answer 1 · 2005-04-29T07:47:28+00:00

: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

generating an auto number

Dani AI

Recommended Answers Collapse Answers

All 2 Replies

Recommended Answers