Auto Updating Combo Box

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hello I have a text box that is a refrence number which
will follow on from from to form how do I get this to
automatically be inputted within the text box that follows?

Many Thanks

James
 
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/
 
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!
Click to expand...
[Reference_No], "0") + 1, "00000")
 
OK try code like:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim f as Access.Form
Set f = Forms![YourFirstFormName] 'This will raise an error if the form
is not loaded
Me.[txtReference_No] = f.[txtReference_No]
End Sub

--

Cheers
Mark

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



James said:
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!
Click to expand...
[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/








.
Click to expand...
Click to expand...
 
Would you like to have a look at the database?

Would this help matters any?

Cheers

James
-----Original Message-----
OK try code like:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim f as Access.Form
Set f = Forms![YourFirstFormName] 'This will raise an error if the form
is not loaded
Me.[txtReference_No] = f.[txtReference_No]
End Sub

--

Cheers
Mark

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



James said:
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!
Click to expand...
[Reference_No], "0") + 1, "00000")
End If
rs.Close
End Sub

You would need to change tblSomeTable to the name of
Click to expand...
your
table.
Cheers
Mark

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





Hello I have a text box that is a refrence number which
will follow on from from to form how do I get this to
automatically be inputted within the text box that follows?

Many Thanks

James



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top