Cannot increment log number 2

[mraetrudeaujr]

I have a database where the users are manually inputting the next "PRSLOG" number. They end up going to the last record, adding one number to it, and then putting it in the field. They would like me to 'automate' this so that every time they click the "add new record" button it automatically gives them the next sequential "PRSLOG" number. I searched this area and found some code that looks like it would work, but it didn't. It was posted by 'dyarwood'. It worked for the user that posted his question, but I get errors when I run it (after modifying it to fit my database). Here is the posted code:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click
    DoCmd.GoToRecord , , acNewRec
'increment service id number
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rcd As ADODB.Recordset
Set rcd = New ADODB.Recordset
rcd.Open "tblService", cnn, adOpenForwardOnly,_ adLockOptimistic
i = DMax("[ServiceID]", "tblService")
i = i + 1
rcd.MoveLast
rcd.MoveLast
rcd.Fields(a) = i
Exit_cmdAddRecord_Click:
    Exit Sub
Err_cmdAddRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click
End Sub

...and here is the code modified for my database:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click
    DoCmd.GoToRecord , , acNewRec
'increment service id number
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rcd As ADODB.Recordset
Set rcd = New ADODB.Recordset
rcd.Open "2005 Log", cnn, adOpenForwardOnly,_ adLockOptimistic
i = DMax("[PRSLOG]", "2005")
i = i + 1
rcd.MoveLast
rcd.MoveLast
rcd.Fields(0) = i
Exit_cmdAddRecord_Click:
    Exit Sub
Err_cmdAddRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click
End Sub

So what do I need to make this work?
Table name=2005 Log
Field name=PRSLOG

 

[rubbernilly]

Why can't you just set the control value to be the value you return?

Me.Control.Text = DMax("YourField","YourTableName")+1

 

[mraetrudeaujr]

Hmm. I must be missing something here. When I use this on the command button's 'On Click' event:

Private Sub cmdNewAddRecordButton_Click()
Me.Control.Text = DMax("PRSLOG", "2005 LOG") + 1
End Sub

I get the "Microsoft Visual Basic" pop-up stating "Compile error: Method or data member not found" and the buttons available being "OK" and "Help" and the 'Control' portion of the "Me.Control.Text" being highlighted in blue. I even tried substituting the 'Control' for the actual control name of the field in question - "txtPRSLOGPage2". This still yielded an error. So now what?

 

[PHV]

Something like this ?
Me!txtPRSLOGPage2 = 1 + Nz(DMax("PRSLOG", "[2005 LOG]"), 0)

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

 

[mraetrudeaujr]

Thanks PHV!

Originally I put this on a new button's 'On Click' event, but it just changed the current records PRSLOG number. Before panicking, I undid this operation, deleted the button, and then placed the code at the bottom of the "Add New Record" button/code. This worked perfectly, thank you.

Al

 

[mraetrudeaujr]

Here's a twist for you, PHV;

Every month this log number has to have the 'prefix' changed to reflect the month. For example; "10 0545" is the two digit month followed by a four digit sequential number. So, beginning November, it will start as "11 0001".

I realize that this may seem nitpicky, but to be able to do this would help to keep users out of the backend tables, and build their confidence in using the Form GUI instead of direct input in the tables.

 

[PHV]

Me!txtPRSLOGPage2 = 1 + Nz(DMax("PRSLOG", "[2005 LOG]", "PRSLOG-(10000*Month(Now())) Between 1 And 9999"), 10000 * Month(Now))

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

 

[MichaelRed]

way past being of any interest, but you might review faq700-184






MichaelRed