After duplicating a record - form will not go to the last record

[Gina Maylone]

I've tried all different combinations to get to the last record that was just added and have had no luck! What am I missing? TIA.

Dim db As Database
Set db = CurrentDb
Dim RS As Recordset
Dim LNGID As Long
LNGID = Next_Custom_Counter()
Debug.Print LNGID

Set RS = db.OpenRecordset("tblProcessEntry")
RS.AddNew
RS![ID] = [LNGID]
RS![COURT] = [COURT]
RS![CASE] = [CASE]
RS!PRIORITY = PRIORITY
RS!EXPDATE = EXPDATE
RS!EXPTIME = EXPTIME
RS!RE_ONE = RE_ONE
RS!PLAINTIFF = PLAINTIFF
RS!RE_TWO = RE_TWO
RS!DEFENDANT = DEFENDANT
RS![FOR] = [FOR]
RS!BILLTO = BILLTO
RS!INVOICEEMAIL = INVOICEEMAIL
RS!DATE_RECEIVED = DATE_RECEIVED
RS!TIME_RECEIVED = TIME_RECEIVED
RS!DOCUMENTS = DOCUMENTS
RS.Update
RS.MoveLast
RS.Close
Me.Refresh
DoCmd.GoToRecord , mainprocessentry, acLast

Sorry the indents didn't hold.

[RuralGuy]

Here's some code that may give you some ideas.

From MVP Ken Snell
rst.AddNew
    rst.Fields("FieldName1").Value = "YourNewValue1"
    rst.Fields("FieldName2").Value = "YourNewValue2"
'  (etc. -- do not include the autonumber field as one that
'   is given a value)
rst.Update
rst.Bookmark = rst.LastModified
lngAuto = rst.Fields("AutonumberFieldName").Value

[orange]

Gina,


Please provide more info re:
Next_Custom_Counter()

[Gina Maylone]

Function Next_Custom_Counter()
   
    On Error GoTo Next_Custom_Counter_Err
    Dim RS As ADODB.Recordset
    Dim NextCounter As Long
    Set RS = New ADODB.Recordset
    'Open the ADO recordset.
    RS.Open "tblCounterTable", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    'Get the next counter.
    NextCounter = RS!NextAvailableCounter
   
    RS!NextAvailableCounter = NextCounter + 1
    NextCounter = RS!NextAvailableCounter
    RS.Update
    MsgBox "The next PSSPro ID generated will be: " & Str(NextCounter)
    
    RS.Close
    Set RS = Nothing
    Next_Custom_Counter = NextCounter
Exit Function
 
Next_Custom_Counter_Err:
    MsgBox "Error " & Err & ": " & Error$
    If Err <> 0 Then Resume
    End
End Function

[Gina Maylone]

Thanks for trying Rural Guy - but that did work either. BTW, there wasn't an autonumber field in the table, so I added one. (I inherited this db). Thank you again for your time.

[RuralGuy]

I'm not real familiar with ADO. I'll bet Orange will come up with a solution.

[orange]

Gina,

I use DAO most often. You said to RG that

but that did work either.

What exactly did you get?
Can you post a copy of the database? Just enough records to show the issue.

We don't know the table structures and that may be part of it.

A few more questions:
Why are you duplicating a record?
Is there something special requiring a Custom number vs an Access controlled autonumber?
Have you tried setting a breakpoint and stepping through the code?
What was in the msgbox from within the Next_Custom_Counter() function?

The function doesn't identify a return value/datatype?
eg : (not sure if this is relevant)
Function Next_Custom_Counter() as Long

[orange]

Gina,

I use DAO most often. You said to RG that

but that did work either.

What exactly did you get?
Can you post a copy of the database? Just enough records to show the issue.

We don't know the table structures and that may be part of it.

A few more questions:
Why are you duplicating a record?
Is there something special requiring a Custom number vs an Access controlled autonumber?
Have you tried setting a breakpoint and stepping through the code?

Update:

I just mocked up your tblCounterTable, with 1 field == NextAvailableCounter
and set an initial value of 163.
Used this small sub to exercise the Function 2 times.

Sub testCustomNumber()
   Debug.Print Next_Custom_Counter
End Sub

The output was:

164 
165

So, the Next_Custom_Counter works as expected.

[Gina Maylone]

Hi Orange,

Yes, the counter function works fine and creates a duplicate record (honestly I don't know why the want to duplicate records) the thing that is not working is displaying the new record after it's created. Also, sometimes when duplicating a record, it uses the same id number (instead of adding 1). I'm stripping the db down so I can post it. Thanks for the time!

[Gina Maylone]

Here it is. And right now - it is assigning a number that already exists! The form where the duplicate record button is is mainprocessentry, processentry.

forum.zip

Thank you again!

[orange]

I mocked up your main routine as best I could.

'---------------------------------------------------------------------------------------
' Procedure : GinaM
' Author    : mellon
' Date      : 21/02/2016
' Purpose   : This is a mockup of Gina's main routine  for debugging pirposes.
'https://www.accessforums.net/showthread.php?t=58262&p=311736#post311736
'---------------------------------------------------------------------------------------
'
Sub GinaM()


      Dim db As Database
10       On Error GoTo GinaM_Error

'''Your code commented
      'Set db = CurrentDb
      'Dim RS As Recordset
      'Dim LNGID As Long
      'LNGID = Next_Custom_Counter()
      'Debug.Print LNGID
      '
      'Set RS = db.OpenRecordset("tblProcessEntry")
      'RS.AddNew
      'RS![id] = [LNGID]
      'RS![COURT] = [COURT]
      'RS![Case] = [Case]
      'RS!Priority = Priority
      'RS!EXPDATE = EXPDATE
      'RS!EXPTIME = EXPTIME
      'RS!RE_ONE = RE_ONE
      'RS!PLAINTIFF = PLAINTIFF
      'RS!RE_TWO = RE_TWO
      'RS!DEFENDANT = DEFENDANT
      'RS![FOR] = [FOR]
      'RS!BILLTO = BILLTO
      'RS!INVOICEEMAIL = INVOICEEMAIL
      'RS!DATE_RECEIVED = DATE_RECEIVED
      'RS!TIME_RECEIVED = TIME_RECEIVED
      'RS!Documents = Documents
      'RS.Update
      'RS.MoveLast
      'RS.Close
      'Me.Refresh
      'DoCmd.GoToRecord , mainprocessentry, acLast

'''my mockup
      Dim rs As Recordset
20    Set db = CurrentDb
      Dim LNGID As Long
30    LNGID = Next_Custom_Counter()
40    Debug.Print LNGID
      '
      'Using my table ancestor to add some records
      
           ' name            Text
           ' id              Double
           ' deathdate       Date
           ' birthdate       Date
           
50    Set rs = db.OpenRecordset("ancestor")
60    rs.AddNew
70    rs![id] = [LNGID]
80    rs![birthdate] = #7/21/1937#
90    rs![deathdate] = #3/19/2009#
100   rs![name] = "TestPerson" & CStr(LNGID)
110    rs.Update
120    rs.MoveLast
       'rs.Close
       'Me.Refresh
       '  DoCmd.GoToRecord , "ancestor", acLast
122      Debug.Print "The latest id added was " & rs!id  'Got here using rs.Movelast .................
130      Debug.Print "The latest id added -using Dmax - was " & DMax("id", "ancestor")
140      On Error GoTo 0
150   rs.Close
160      Exit Sub

GinaM_Error:

170       MsgBox "Error " & Err.Number & "  in line  " & Erl & " (" & Err.Description & ") in procedure GinaM of Module AWF_Related"
End Sub

The output for the Debug.print for the last 2 records added was

175
The latest id added was 175
The latest id added -using Dmax - was 175
176
The latest id added was 176
The latest id added -using Dmax - was 176

[orange]

I need some instructions of how to proceed with your form. The more detail the better, since your process is new to me.
I notice that this is an open source (or was based on an open source) program.

I notice that there are many tables, queries, modules...
The relationships window is pretty sparse and shows 1:1 relationships.

I'm attaching a picture of the error I get on opening the form.

Did you look at the mock up I sent?

[Gina Maylone]

forum (2).zip

Hi again - yes this is an old program, messy, but still in use - but the author is long gone - so I was hired to fix and add some things. In the process - this glitch started happening with add record and duplicate record buttons. And yes, I checked out the mock-up. Thank you. Sorry about the error, there was a table missing. The DB attached includes it (CompanyEmails).

So when the MainProcessEntry pops up, go to ProcessEntry. Then click Duplicate Record. When I just did it, it was giving me the same ID number as the current record. NOTE - from what I've been able to learn, the next_counter function is supposed to be useful for multi-user systems. Personally I'd rather just do a DMAX+1 on the Autonumber field...but I worry that something will go haywire. Did I mention that this program is possessed?

Thank you for looking at this.

[orange]

The about file supplied(F2) with the program says it is for use on 1 personal computer. This seems to indicate it is not designed for multi-user. Could that be part of the problem??

Someone must know why they duplicate records. I don't.

I have put some debug.print statements in similar code previously to trace the logic of what was processed during specific events. You could do that to understand what it is doing, but if you don't know what it should be doing or why, I'm not sure what to suggest.

You can put Debug.Print "event name goes here -was executed " & Now
and do this for several events. It will give you the logic flow in the immediate window.
Reviewing the outputs should help identify whether something was done or not.

Pressing Duplicate Record gives me the next number.
under PSSProID I had 48442
after hitting Duplicate record I get msgbox saying Next PSSPro ID generated will be 48443

If I click Add New Record, I do not get a message.
IN all cases the value under PSSProID sstays at 48442.

The value in the table has increased to 48443.

Hope it's helpful.

[Gina Maylone]

After it added the record, did the form open to the last record?

I Googled the counter function and came up with the same one as in this program. The title read something like "how to create a custom counter for multiple users". This db is split - and only one person actually sets up the new cases. So assigned numbers shouldn't be clashing.

I had tried debug.print at various points, and will continue trying some more. I also created a clean, new database and importing all of the objects, in case it was corrupted. As I said, this thing is possessed - my client will highlight some text in the "Comments" field - and then cannot un-highlight it. I jump on remotely and it works just fine. UGH.