• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 24
  • Last Modified:

write conflict

Hi
On my app I am getting now the warning "write conflict" as per the below message.

Screen-Shot-2018-02-20-at-09.04.50.png
This happens when I try to close a form (and I am the single user...) after, on a subform I updated the record on the master form. If if click SAVE RECORD then the data is destroyed. If I click either COPY or DROP then it's OK. I do not want the user to get this annoying message.
How (i) can I do something to avoid the "stupid" message by disabling some automatic change that is nowhere or (ii) just pre-select the DROP and avoid showing the message

Can anyone help?
0
João serras-pereira
Asked:
João serras-pereira
  • 5
  • 3
  • 2
  • +1
5 Solutions
 
Gustav BrockCIOCommented:
You are updating the main form from the subform in a second process using SQL.
Instead, update the RecordsetClone of the main form from the subform.

Or, implement the simple method described here:

Handle concurrent update conflicts in Access silently
0
 
Dale FyeCommented:
in the subform, when you update the record in the main form, do something like:

me.parent.txt_SomeField = me.somevalue
me.parent.dirty = false
me.parent.refresh
0
 
João serras-pereiraAuthor Commented:
Hi Dale
does
me.parent.txt_SomeField = me.somevalue
need to be bound?

btw - the doctor LOVED the output of your grid Idea...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
As the others suggested, you are conflicting with yourself.  You have code that is dirtying a form and then perhaps using DAO or a query to update it.  We can't really offer a solution without knowing how the problem is caused.
0
 
João serras-pereiraAuthor Commented:
Hi Pat, Dale -
 Here is what I have (my problem is that the error message just pops up without letting me where it really comes from, except when I press a close button or after close when I change current record in the master table

I have a main form and a subform

 Mainform is bound to a table [tblExame] - [tblExame], contains 3 fields that, actually, are the keys for 3 images in [tblImagem]

SubForm Is bound  [tblImagem] - actual sets of images to select for one Exam in tblExame]

[tblImagem] is a "sub" table of  [tblExame]


This is Ok and forms behave - linked fields are OK

So what I what to do is quite simple:

On the SUBFORM (related to [tblImage] I pick out of the set of images (20 - 30)  the 3 images and update the 3 fields in the main table [tblExam]. They are correctly updated (I am using DAO for this)
then I close the form and come to another form that lists all records in [tblExame].

Now, if I try to close this form, or change the "current record" of the list going to another record of [tblExame] I get the message.

The code in the subform is:


Private Sub btnAssociarImagem_Click()

    Dim LifeLingerDB As DAO.Database
    Dim q02_exameSet As DAO.Recordset
    Dim q02_exameDef As QueryDef
    Dim strSetSQL As String
    Dim bUpdate As Boolean
    Dim intIdPaciente As Integer

    Dim strFilePath, _
        strImgPath As String
    
    Dim nIdImagem, _
        nIdExame As Integer
    
    
    
'On Error GoTo errorTrap

    Call logMe("btnAssociarImagem_Click", "start")
    Forms![_intData].frm_idImagem = Nz(Me.frm_idImagem, 0)
    nIdImagem = Nz(Me.frm_idImagem, "0")
    strImgPath = Nz(DLookup("imgPath", "tblImagem", "idImagem =" & nIdImagem), "")
    If strImgPath = "" Then
        MsgBox "Erro Interno, Imagem [" & nIdImagem & "] não localizada"
        Call logMe("btnAssociarImagem_Click", "Erro Interno, Imagem [" & nIdImagem & "] não localizada")
        Exit Sub
    End If
   
    nIdExame = Nz(Forms![_intData].frm_idExame, 0)
    Set LifeLingerDB = CurrentDb
    Set q02_exameDef = LifeLingerDB.QueryDefs("q02_exame")
    strSetSQL = "SELECT "
    strSetSQL = strSetSQL & "tblExame.IDpaciente, "
    strSetSQL = strSetSQL & "tblExame.IDexame, "
    strSetSQL = strSetSQL & "tblExame.DataExame, "
    strSetSQL = strSetSQL & "tblExame.MotivoExame, "
    strSetSQL = strSetSQL & "tblExame.Observacao, "
    strSetSQL = strSetSQL & "tblExame.Diagnostico, "
    strSetSQL = strSetSQL & "tblExame.Orientacao, "
    strSetSQL = strSetSQL & "tblExame.imgIdNormal, "
    strSetSQL = strSetSQL & "tblExame.imgIdAcidoAcetico, "
    strSetSQL = strSetSQL & "tblExame.imgIdShiller "
    strSetSQL = strSetSQL & "FROM tblExame "
    strSetSQL = strSetSQL & "WHERE (((tblExame.idexame)= "
    strSetSQL = strSetSQL & nIdExame
    strSetSQL = strSetSQL & "));"
    q02_exameDef.SQL = strSetSQL
    Set q02_exameSet = LifeLingerDB.OpenRecordset("tblExame", dbOpenDynaset)

    ' testar se a imagem já está associada a um tipo no exam
    With q02_exameSet
        If Nz(!imgIdNormal, 0) = Me.frm_idImagem Or _
            Nz(!imgIdAcidoAcetico, 0) = Me.frm_idImagem Or _
            Nz(!imgIdShiller, 0) = Me.frm_idImagem Then
            ' já se encontra utilizada
            MsgBox "Já está associada" & vbCrLf _
             & "Deverá remover a ligação para atribuir o novo Tipo"
            Call logMe("btnAssociarImagem_Click", "Já existe imagem [Normal]" & vbCrLf _
                        & "Deverá remover a ligação para atribuir o novo Tipo")
            Call logMe("btnAssociarImagem_Click", "end")
            Exit Sub
        End If
    End With
    
     With q02_exameSet
     .FindFirst "idExame = " & Me.frm_idExame
        .Edit
        Select Case Nz(Me.frm_imgTipo, 0)
        
            Case 1 ' imagem normal

                Forms!frmImagemClassificar.frm_imgNormal.Picture = strImgPath
                Me.Parent.frm_imgNormalPath = Nz(strImgPath, "")
                Me.Parent.Dirty = False
                Me.Parent.Refresh
                !imgIdNormal = Nz(Me.frm_idImagem, 0)
                
            Case 2 ' imagem de ácido acético

                Forms!frmImagemClassificar.frm_imgAcidoAcetico.Picture = strImgPath
                Me.Parent.frm_imgAcidoAceticoPath = Nz(strImgPath, "")
                Me.Parent.Dirty = False
                Me.Parent.Refresh
                !imgIdAcidoAcetico = Nz(Me.frm_idImagem, 0)
                
            Case 3 ' imagem Shiller
            
                Forms!frmImagemClassificar.frm_imgShiller.Picture = strImgPath
                Me.Parent.frm_imgShillerPath = Nz(strImgPath, "")
                Me.Parent.Dirty = False
                Me.Parent.Refresh
                !imgIdShiller = Nz(Me.frm_idImagem, 0)
                Me.Parent.Dirty = False
                Me.Parent.Refresh

            Case Else ' apenas se podem associar ao exames os 3 tipos de imagem
                MsgBox "Imagem não associável"
                .Close
                Set q02_exameSet = Nothing
                Call logMe("btnAssociarImagem_Click", "end")
                Exit Sub
        
        End Select
        .Update
        .Close
        
    End With
    Set q02_exameSet = Nothing
    Call logMe("btnAssociarImagem_Click", "end")
    Exit Sub
 
errorTrap:
    MsgBox "[btnAssociarImagem_Click]." & "[" & Err.Number & "].[" & Err.Description & "]"
    Call logMe("btnAssociarImagem_Click", "errorTrap:" & "[btnAssociarImagem_Click]." & "[" & Err.Number & "].[" & Err.Description & "]")
    Resume Next

End Sub

Open in new window


And the Select statement is the update, now with dale's suggestion, but without results.
Of course I can send the database, just let me know

Hope this is enough
0
 
Gustav BrockCIOCommented:
You are writing to q02_exameSet as well as writing to the form. If they use some common tables, it will cause the trouble you see.
0
 
João serras-pereiraAuthor Commented:
What should I do? Yes the table is the same (tblExame)

Is writing to the form on the "dirty"? If so, the problem was there before. In the subform I need to update the record that is bound to the main form. What would be your suggestion?
0
 
Gustav BrockCIOCommented:
I would rethink the concept completely, but if it cannot be modified, you'll have to study the article I referenced on how to solve it.
0
 
Dale FyeCommented:
Can you tell us what the RecordSource is for the main form?  If it is a table and not a query, show the SQL which would select all of the fields from the table.

You might be able to avoid this issue if you exclude fields from the Recordsource of the main form, which are edited in the code you use in the "btnAssociarImagem_Click" event.

Another alternative might be to try using the RecordSetClone method mentioned by Gustav.  Instead of using a select statement to create the recordset in the event code above, use the RecordsetClone method to clone the parent forms recordset.  something like:
set rs = me.parent.recordsetclone
with rs
    .bookmark = me.parent.bookmark  'this makes sure the clone recordset has the same focus as the parent form.

    'insert other code here
    .edit
    Select Case ...
'Insert your select case code here, but leave out code that looks like:
'                Me.Parent.frm_imgShillerPath = Nz(strImgPath, "")
'                Me.Parent.Dirty = False
'                Me.Parent.Refresh
'Instead, simply use something like:
'               .frm_imgShillerPath = NZ(strImgPath, "")
    End Select
    .update
End with
rs.close
me.parent.refresh 

Open in new window

0
 
João serras-pereiraAuthor Commented:
Ok. I'll give a good look to Gustav's suggestion, but first will try a rethink of the approach. Both the main and subforms may be linked to tables so I may be able to drop DAO altogether and just modify, in code, the form fields.
will let you know
0
 
PatHartmanCommented:
On the SUBFORM (related to [tblImage] I pick out of the set of images (20 - 30)  the 3 images and update the 3 fields in the main table [tblExam].
If you have ONE parent record and MANY related child records, it doesn't make sense to store data related to ONE child record in the parent record.

On the RARE occasions where you want to update a main form record from a subform record, you would never open a recordset or run a query, you would simply put the values directly into the controls on the main form.

Me.Parent!Field1 = Me.Field1
Me.Parent!Field2 = Me.Field2
Me.Parent!Field3 = Me.Field3

The values will be saved when the form is closed or you move to a new main form record.  If you move to a new subform record, the parent record remains dirty so you might need to force the parent record to be saved.  However, it simply doesn't make sense to keep overwriting the same three fields with different values.
1
 
João serras-pereiraAuthor Commented:
Thanks all for the support. I have changes and cleaned the code and it looks that it is working!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now