write conflict

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

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?
João serras-pereiraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Dale FyeOwner, Developing Solutions LLCCommented:
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
João serras-pereiraAuthor Commented:
Hi Dale
me.parent.txt_SomeField = me.somevalue
need to be bound?

btw - the doctor LOVED the output of your grid Idea...
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
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
        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
                !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
                !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
                !imgIdShiller = Nz(Me.frm_idImagem, 0)
                Me.Parent.Dirty = False

            Case Else ' apenas se podem associar ao exames os 3 tipos de imagem
                MsgBox "Imagem não associável"
                Set q02_exameSet = Nothing
                Call logMe("btnAssociarImagem_Click", "end")
                Exit Sub
        End Select
    End With
    Set q02_exameSet = Nothing
    Call logMe("btnAssociarImagem_Click", "end")
    Exit Sub
    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
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.
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?
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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
    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
End with

Open in new window

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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
João serras-pereiraAuthor Commented:
Thanks all for the support. I have changes and cleaned the code and it looks that it is working!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.