Forms- Auto Retrieve number to be displayed in textbox

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
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/
 
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
 
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
 
Back
Top