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.

 

[Yollem Crumble]

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.

The attached image shows the error on the line [Street1] = ckadd([Street1])

 

[Yollem Crumble]

Ken - thanks for your reply. The database in mostly fully normalised but I found I was taking too long to create files to upload to our e-mail provider, so decided to add two fields with dependancies. I fail to see how that creates the write conflict.

 

[The_Doc_Man]

After looking at that again, the "preventing update" error is probably related to the repeated use of setting .Dirty to FALSE. In a BeforeUpdate routine, you don't need the Me.Dirty trick because as soon as you exit the BeforeUpdate routine, the update occurs. I.e. whether you used the Me.Dirty trick or not, if you don't cancel the BeforeUpdate, all the edits you made to fields for that form WILL occur. A write conflict would occur if you were about to update a record and something else did an update.

The #1 update source is the internal process by which your form reaches the BeforeUpdate event in the natural flow of saving form data. The #2 update is the VBA Me.Dirty=FALSE trick that updates the SAME RECORDSET before the event completes the #1 update. I can only say this a couple of ways. The form's natural data flow is an update to the same recordsource as the Me.Dirty trick - TWO channels both trying to save the record at the same time. That's your most likely conflict. Your code is stepping on the form's natural flow.

 

[arnelgp]

you don't need to Save the Full Address in your table, since it can be derived from the Other table.
just link the two tables together using a query and use the query as the recordsource of your form.

 

[Yollem Crumble]

After looking at that again, the "preventing update" error is probably related to the repeated use of setting .Dirty to FALSE. In a BeforeUpdate routine, you don't need the Me.Dirty trick because as soon as you exit the BeforeUpdate routine, the update occurs. I.e. whether you used the Me.Dirty trick or not, if you don't cancel the BeforeUpdate, all the edits you made to fields for that form WILL occur. A write conflict would occur if you were about to update a record and something else did an update.

The #1 update source is the internal process by which your form reaches the BeforeUpdate event in the natural flow of saving form data. The #2 update is the VBA Me.Dirty=FALSE trick that updates the SAME RECORDSET before the event completes the #1 update. I can only say this a couple of ways. The form's natural data flow is an update to the same recordsource as the Me.Dirty trick - TWO channels both trying to save the record at the same time. That's your most likely conflict. Your code is stepping on the form's natural flow.

Thanks - I think I understand that - I will experiment a bit more with the before and after update and see what occurs.

 

[Yollem Crumble]

Thanks - I think I understand that - I will experiment a bit more with the before and after update and see what occurs.

On further reading, updating the field through a beforeUpdate event causes an error. It needs to be done after the update so as not to cause a conflict in the field being updated. That still leaves me with a write conflict which I now suspact is caused by altering the field (or other fields) in a dynaset. I will keep looking

 

[Yollem Crumble]

you don't need to Save the Full Address in your table, since it can be derived from the Other table.
just link the two tables together using a query and use the query as the recordsource of your form.

The query got overly complicated as it depends on so many variables. That is why I decided to ditch the query and just store it.

 

[arnelgp]

The query got overly complicated as it depends on so many variables. That is why I decided to ditch the query and just store it.

then you are just Duplicating the infor already on the other table, thus, wasting space on your db.

 

[Ken Sheridan]

The query got overly complicated as it depends on so many variables. That is why I decided to ditch the query and just store it.

You can return the full address as a concatenated string expression in one query, and then base another query on that query rather than the base table to reduce the complexity.

When concatenating values you might find the following function useful. It's an amended version of the CanShrinkLines function published by Microsoft many years ago, and like that, it suppresses any Nulls to avoid redundant spaces or punctuation characters in the function's return value:

Public Function ConcatValues(strSeparator As String, ParamArray arrVals())
 
    ' Pass this function a character or characters
    ' to be used as the separator followed by the values to be combined
    ' For example: strFullName =
    ' ConcatValues(" ",FirstName,MiddleName,LastName)
    
    Dim X As Integer, strLine As String
    
    For X = 0 To UBound(arrVals)
        If Not IsNull(arrVals(X)) And Trim(arrVals(X)) <> "" Then
          strLine = strLine & strSeparator & arrVals(X)
        End If
    Next
      
    ' remove leading separator character(s)
    ConcatValues = Mid(strLine, Len(strSeparator) + 1)
 
End Function

Note that when including punctuation characters in a member of the parameter array the + operator rather than the & concatenation operator should be used. Null propagates in arithmetical expressions, so the punctuation character will be suppressed where necessary, e.g.

ConcatValues(" ",LastName+",",FirstName,MiddleName)