Form - Increments a counter by Year (1 Viewer)

gcdouglass · Dec 6, 2019

Have a form. When a new record is created goes to a table to get its numbering and increments a counter. Then prefixes the year the number and populates a text field. So for example 19-240, 19-241 ...

This works in MS Access 2016 and was working in 2019. In version 1808 build 10352.20042 Click to Run the following code no longer runs:

Code:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim rec As Recordset
Set rec = Me.Recordset()

On Error GoTo Form_BeforeInsert_Err

    DoCmd.RunSQL "UPDATE [tblNextYearSeqNo]  SET SeqNo = SeqNo + 1  WHERE RecordYear = Right(Year(Date()),2);    ", -1
    Forms!frmIncidentResponse!txtDESIncidentNumber = Right(Year(Date), 2) & "-" & Format(DLookup("[SeqNo]", "tblNextYearSeqNo", "[RecordYear]=Right(Year(Date()),2)"), "0000")


Form_BeforeInsert_Exit:
    Exit Sub

Form_BeforeInsert_Err:
    
    If rec.RecordCount = 0 Then
    DoCmd.Close acForm, "frmIncidentResponse"
    Else
    DoCmd.GoToRecord , , acLast
    End If
    
End Sub

Any help would be appreciated.

theDBguy · Dec 6, 2019

Hi. Welcome to AWF!

"No longer works" means what exactly? Were you getting a 3340 error? If so, try the workaround utility I posted earlier.

gcdouglass · Dec 6, 2019

When the form works:

Changing a value on the form. Gets the last counter from the database. Increments its value and populates the form field

Now whenever a value is changed on the form it closes immediately.
The counter in the table tblNextYearSeqNo is not being incremented.

theDBguy · Dec 6, 2019

gcdouglass said:
When the form works:

Changing a value on the form. Gets the last counter from the database. Increments its value and populates the form field

Now whenever a value is changed on the form it closes immediately.
The counter in the table tblNextYearSeqNo is not being incremented.

Hi. If you tried to attach an image, try zipping it up first. Also, did you try the utility I mentioned?

gcdouglass · Dec 6, 2019

Not enough post to post a link or an image yet.

Tried the utility but it is a split database. So now the accdb file with the forms and reports cannot find the data

gcdouglass · Dec 6, 2019

Seeing if I can use attachments for screenshots.

gcdouglass · Dec 6, 2019

I did not do this original code. But have been resolving as it develops issues.

There is a form frmIncidentResponse. When it is edited, the autonumber based on year is updated and its text field txtDESIncidentNumber is suppose be populated with the date. Works in all but the newest version of Access 2019. Updates go through SCCM so I can not opt the users out of the MS Office updates.

theDBguy · Dec 6, 2019

gcdouglass said:
I did not do this original code. But have been resolving as it develops issues.

There is a form frmIncidentResponse. When it is edited, the autonumber based on year is updated and its text field txtDESIncidentNumber is suppose be populated with the date. Works in all but the newest version of Access 2019. Updates go through SCCM so I can not opt the users out of the MS Office updates.

Hi. Just curious if you tried the utility I linked to earlier. If not, just curious what your reasons might be.

gcdouglass · Dec 6, 2019

Tried the utility but it is a split database. After the database ran on the tables accdb could not get the linked table manager to find the updated tables.

theDBguy · Dec 6, 2019

gcdouglass said:
Tried the utility but it is a split database. After the database ran on the tables accdb could not get the linked table manager to find the updated tables.

Ah, sorry for the confusion. You were supposed to apply the fix on the FE and not the BE. You can rerun the utility against the BE to unfix it and then run it against the FE.

gcdouglass · Dec 6, 2019

Thank you! That fixed it.

The original file with data is a mdb. Is there a way to fix that even if I have to do it manually. Since the mdb has 20 years of data some if it now not pertinent and is used by two users at the same time rarely, when I received this problem I made the file an accdb. Then I split the data. Worst case I could just make the mdb an accdb and then apply your fix. But since the mdb will only be in use for a couple more weeks I would like to avoid having to send out new instructions to everyone.

Thanks again!

theDBguy · Dec 6, 2019

gcdouglass said:
Thank you! That fixed it.

The original file with data is a mdb. Is there a way to fix that even if I have to do it manually. Since the mdb has 20 years of data some if it now not pertinent and is used by two users at the same time rarely, when I received this problem I made the file an accdb. Then I split the data. Worst case I could just make the mdb an accdb and then apply your fix. But since the mdb will only be in use for a couple more weeks I would like to avoid having to send out new instructions to everyone.

Thanks again!

Hi. I guess when I created the utility, I didn't think it would be used to fix MDB files. Just as a test, try typing the path to the MDB file in the textbox instead of browsing for it. Make sure you try it out with a backup copy first.

gcdouglass · Dec 6, 2019

I was able to type in the full location and file name and it seems to fix the mdb file as well

theDBguy · Dec 6, 2019

gcdouglass said:
I was able to type in the full location and file name and it seems to fix the mdb file as well

Hi. Glad to hear that. Please remember, it was to be a temporary fix; although, I don't see any major side effects if you left it there permanently. But as soon as the actual fix from MS comes out, all you have to do is re-run my utility to "unfix" it. Good luck!

Form - Increments a counter by Year (1 Viewer)

gcdouglass

New member

theDBguy

I’m here to help

gcdouglass

New member

theDBguy

I’m here to help

gcdouglass

New member

gcdouglass

New member

Attachments

gcdouglass

New member

theDBguy

I’m here to help

gcdouglass

New member

theDBguy

I’m here to help

gcdouglass

New member

theDBguy

I’m here to help

gcdouglass

New member

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread