Forms- Auto Retrieve number to be displayed in textbox

[Mike]

Hi All,

I created a form that constains textbox. One of the
textboxes labled as Id. This ID textbox should
automatically retrieve an id from one of the database
tables and automatically increment the id to the next
number. How can I program the form to retreive the id and
increment the id to the next number?

Thanks for the help....

Mike

 

[Mark Phillipson]

One way to automatically generate numbers is to set the data type to
AutoNumber.

There can be only one AutoNumber field per table.

The Autonumber can have gaps in the sequence, when records are undone or
deleted.

Another way is to put code in the form's Before Insert Event that gets the
last number used and add 1 to it.

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/

 

[Mike]

How can I program the form to retrieve the number and
increment to the next number and displayed it right in the
textbox?

Thanks for the help....

Mike

 

[Mark Phillipson]

Try something like:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim strSQLText As String
strSQLText = "SELECT Max(tblSomeTable.ID) AS ID" & vbCrLf
strSQLText = strSQLText & " FROM tblSomeTable;"
Set rs = db.OpenRecordset(strSQLText)
If Not rs.EOF Then
rs.MoveFirst
Me.txtID = Nz(rs![ID], 0) + 1
End If
rs.Close
End Sub

You would have to change tblSomeTable with the name of the table.

Cheers
Mark