Thanks for that but I think that this is a little more
complex than what I require....
What I am after is that one the first form I have a
refrence number which is inputted by the user and then on
each other form it looks up that refrence number from the
first form.
All I require it to do is that when the refrence number is
pulled from teh first form it gets added into the table by
default.
Many Thanks
James
-----Original Message-----
Hi James,
If you need the reference number to increment by 1 each time a new record is
added, then enter code like the following in say the Before Insert Event:
Private Sub Form_BeforeInsert(Cancel As Integer)
'Get Next Reference Number
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLText As String
Set db = CurrentDb
strSQLText = "SELECT Max(tblSomeTable.Reference_No) AS Reference_No" &
vbCrLf
strSQLText = strSQLText & " FROM tblSomeTable;"
Set rs = db.OpenRecordset(strSQLText)
If Not rs.EOF Then
rs.MoveFirst
Me.txtReference_No = Format(Nz(rs!
[Reference_No], "0") + 1, "00000")
End If
rs.Close
End Sub
You would need to change tblSomeTable to the name of your table.
Cheers
Mark
Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/
.