Write conflict

[Yollem Crumble]

I have an access table holding, amongst other things addresses of members. When an address is added or modified I trigger an "after update"
event to correct abbreviations and also to populate another field in the record with a "full address" which is used for many other purposes. The problem I have is that I cannot figure out how to "commit" changes to a field, resulting in write conflict errors.. There are no subforms involved. It is also not a case of two users operating on the field at the same time. Any help with resolving this would be appreciated.
This is the code:

Private Sub Street1_AfterUpdate()
Dim ans As String

    If Me.Dirty Then
      Me.Dirty = False
    End If
     '''''''''''''''''''''''''''''''''Write conflict generated
    [Street1] = ckadd([Street1]) ' check street abbreviation
    [emailOK] = True  'set a flag field to nenew email data
    ans = Log_Change("Street1", [Street1], [Member_Number] )' add a record to log table
    If Me.Dirty Then
      Me.Dirty = False
    End If
    [FullAddress] = Fill_FAddress([Member_Number]) 'call function to update a field with the full address
    If Me.Dirty Then
      Me.Dirty = False
    End If
     '''''''''''''''''''''''''''''''''Write conflict generated
End Sub
'---------------------------------------------------------------------------
Private Function ckadd(add) As String
Dim last As String
Dim tem As String
Dim aok As Boolean
'check if nul
If Len(add & vbNullString) = 0 Then
  add = ""
End If
aok = False
'remove punctuation
tem = Replace(add, ",", "")
add = Trim(tem)
tem = Replace(add, ".", "")
add = Trim(tem)
If Len(add) > 4 Then
    last = Right(add, 3)

    If last = " St" Then
       last = " Street"
       aok = True
    End If
' And a whole heap of other abbreviations checked for
    If last = "ent" Or last = "ace" Or last = "nue" Or last = "ade" Or last = "uit" Or last = "urt" Or last = "ane" _
    Or last = "ive" Or last = "eet" Or last = "oad" Or last = "ove" Or last = "ace" Or last = "ose" Or last = "Way" _
    Or last = "ews" Or Left(add, 2) = "PO" Or Left(add, 2) = "GP" Then
    aok = True
    End If
    tem = StrConv(Left(add, Len(add) - 3), 3) & last
End If
If Not aok Then
   add = InputBox("Please Check Street Name ", , add)
End If

ckadd = Trim(tem)
End Function
'--------------------------------------------------------------------------------------
Function Fill_FAddress(mem) As String
    Dim faddress As String
    Dim dbgpsmembership As DAO.Database
    Dim rsm As Recordset
    Set dbgpsmembership = CurrentDb
    Set rsm = dbgpsmembership.OpenRecordset("GPSMembers")

    rsm.MoveFirst
    rsm.FindFirst "[member_Number] = " & mem
    If rsm.NoMatch Then GoTo errormem

    faddress = rsm("Street1").Value & "; "
    If Len(rsm("Street2").Value & vbNulString) <> 0 Then faddress = faddress & rsm("Street2").Value & "; "
    faddress = faddress & rsm("Suburb").Value & "; " & rsm("State").Value & " " & rsm("Postcode").Value
    faddress = faddress & " " & rsm("Country").Value

    Fill_FAddress = faddress
    GoTo Endprocess
errormem:
MsgBox ("Error in fill full Address")
 Fill_FAddress = ""
Endprocess:
End Function

 

[The_Doc_Man]

You have the form open, presumably NOT read-only, and then use VBA to update (write) the record at the same time. The AfterUpdate event occurs just after you updated the record and now you are updating it again. In fact, you use the Me.Dirty=False trick twice in the AfterUpdate event code, not just once.

Use the BEFOREUpdate event to update the fields and then just allow the update to occur... once, which will perform the updates automagically.

 

[Yollem Crumble]

The dirty lines are my attempt to save previous edits. I removed them and shifted the code to before update. The code crashes on the checkadd function which will not allow me to update the field.
Actually the ckadd fuction is the only validation line of code. the rest is updating other fields dependant on the Street1 field.

 

[isladogs]

Is your data in SQL Server? If so the issue may be with a boolean field

 

[Yollem Crumble]

No it is an access table.

 

[The_Doc_Man]

The code crashes on the checkadd function which will not allow me to update the field.

Your description makes no sense because nothing in your CheckAdd function is directly I/O related except for the InputBox - which just returns a value - and the returned value doesn't get tested in your main code. Can you explain that "crash" in a little more detail?

If something crashes, obviously you can't do the update. But I don't see why that particular line would cause such a failure.

 

[Ken Sheridan]

I have an access table holding, amongst other things addresses of members.

Storing each member's full address table in a single table implies that the table is not normalized to Third Normal Form (3NF), as this would involve a number of transitive dependencies on the key. The table is consequently open to to the risk of update anomalies. The address data is hierarchical, from Counties downwards, so should be stored in a set of related tables which model the hierarchy.

The attached file is a simple illustration of this, the hierarchy being Countries, Regions, and Cities in a set of enforced relationships. This could easily be extended to include Counties between Regions and Cities, Suburbs, Streets, and even PropertyNamesOrNumbers, to be more comprehensive. In the attached demo, address data is by the selection of a country, region, then city in three correlated combo boxes. The NotInList event procedure allows new values to be entered at any level. The sort of validation which you are doing could then be done when the dialogue form to insert a new row into the relevant referenced table is opened by the code in the NotInList event procedure. In the case of the topmost level of the hierarchy, Countries, the insertion is done transparently, without opening a dialogue form, so any validation would be done in the NotInList event procedure itself.

Note how, by virtue of the correlation of the combo boxes, the insertion of multiple values is possible, e.g. duplicated city name such as Paris Île-de-France and Paris Texas. The primary keys of the referenced tables are numeric surrogate keys of course, to allow such duplication. In each case the RegionID foreign key in, for example, the Cities table will differ.

When you need to return a member's complete address this would be done within a simple query which joins the referenced tables. Where the hierarchy is discontinuous, resulting from different structures internationally, N/A values are used to plug the gaps.