Increment data object when data entry form is opened 1

[KerryL]

I'm trying to write a simple subroutine that will automatically create a number in a data object on a form. The new number will be generated by taking the last value that was saved to the table from that object and incrementing it by +1.

db = WARN_Vers11.mdb
tbl = WARNData
tblfield = NoticeNo
frm = frmDataEntry
object = txtNoticeNo

When frmDataEntry is opened I want the program to look at the value of NoticeNo in the last record of the table, then populate txtNoticeNo with that value +1.

For example, if the NoticeNo for the last record in the table is 3595, when I open frmDataEntry to enter a new record the txtNoticeNo box should automatically be populated with 3596.

I'm not so good with subroutines that require me to define recordsets, etc. Any help you can provide will be greatly appreciated.

 

[belovedcej]

A possibility:

On the open event:

Dim rst As New ADODB.Recordset
Dim newNumber As Long

Set rst = CurrentProject.Connection.Execute("Select MAX(NoticeNo) As MaxNum FROM WARNData")


newNumber = rst.Fields("MaxNum") + 1
Set rst = nothing

frmDataEntry.txtNoticeNo = newNumber

 

[KerryL]

I tried your code suggestion but I'm getting a "Runtime Error 424 - Object Required" error on this line:

frmEnterData.txtNoticeNo = newNumber

(Yes, the form and object name are correct)

Do I need to add the db name to the front of that string?

 

[PHV]

In the Load event procedure of frmDataEntry:
Me!txtNoticeNo = 1 + Nz(DMax("NoticeNo", "WARNData"), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[KerryL]

PHV,
You code worked perfectly. Thank you very much!!

The only thing I have to do now is add some code behind the "Save" and "Return to Menu" cmd buttons so that records aren't saved with just the notice number object populated.

Again, thank you!

 

[PHV]

Be aware that this code is not designed for multiuser ...