New with a problem (1 Viewer)

[Yollem Crumble]

Hi - I am new to this site but have been using MS Access with a ton of VBA behind for many years. Usually I can solve most problems but recently came across one I need help with. I have a form drawing data from a table. It contains a field for town/suburb and a field for Post code (Zip Code) I use another table to check the post code against the town (and State). On the Town field I have an after update to do the check. The code is as follows:

Private Sub Town_Suburb_AfterUpdate()
Dim psta As String
Dim ploc As String
Dim ppc As String
Dim pss As String
Dim pcare As String
Dim ans As String
Dim ab As Boolean

DoCmd.RunCommand acCmdSaveRecord
Me.[Suburb] = UCase(Me.[Suburb])
If Me.[Country] <> "Australia" Then GoTo endprocess
If IsNull([State]) Or IsNull([Suburb]) Then
MsgBox ("a suburb or town and state must be present to check post code")
Else
psta = [State]
ploc = [Suburb]
pss = Trim(ploc) & Trim(psta)
If IsNull(DLookup("pcode", "pcbrief", "loc_sta = '" & [pss] & "'")) Then
MsgBox ("Suburb not recognised")
Else
ppc = DLookup("pcode", "pcbrief", "loc_sta = '" & [pss] & "'")
[Postcode] = ppc
If ppc = [Postcode] Then
pcare = DLookup("PCA", "PCArea", "postcode = '" & [ppc] & "'")
Me.[PCA] = pcare
End If
End If
End If
ans = Log_Change("suburb", [Suburb], [Member_Number]) ' save the change to a log table
endprocess:
DoCmd.RunCommand acCmdSaveRecord
End Sub

The issue is in the two docmd.runcommand accmdsaveRecord lines
Both trigger a Write Conflict - this record has been changed by another user .......
I have tried using the line: "if me.dirty then me.dirty = False" but with the same result.

To make things stranger, if I remove the saves it only fails on every second occasion. Prior to a few months ago, it worked for some years.

Does anyone have a suggestion?
Thanks in advance

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Gasman]

Please put your code within code tags, so it keeps the indentation and makes it easier to read. Use the </> icon.

 

[GaP42]

Your issue: Both DoCmd.RunCommand acCmdSaveRecord trigger a write conflict.

1. The first forces a save of the current record on After updating the Town_suburb control. This is not an appropriate place for this action. Validation checks of data on your form from occurs in the Before_update event of the form. NO need to use the save command - it will happen automatically if it passes your validation tests in the Before_update event.
2. The postcode is allocated based on a lookup of suburb and state. It is essentially a calculated field - do you allow any entry of postcode? Is your locality table always uptodate? As a calculated field display the PostCode when Nz(pcare) is <> 0. The logic to validate the postcode can be placed in the before update of the form.
3. Saving the change to the log file requires a SQL insert statement in before update too - on success of the change to the record.

AND Welcome to the forum