Recordset and Workgroup question.

[Bergja]

Everything in my database seems to be working fine for the moment except the following code. First an explanation of the form this Sub belongs too. Page one of the form has a listbox populated with data from the Helpdesk table. When you click on the an item in the listbox the a record is selected by means of the recordset.findfirst method. and the controls on page 2 of the form are populated accordingly from that record. The idea is that someone can make all the changes they want to the data they are looking at on the form and then click the Update button to execute the following code.

Private Sub cmdUpdate_Click()
 
If Not (cmbStatus.Value = "Resolved") Then
wrkDefault.BeginTrans
    With rst
 
        'Updates current record with changes
        .Edit
            ![Computer Name] = Me!txtComputerName
            ![Building Number] = Me!txtBldgNumber
            ![Room Number] = Me!txtRmNumber
            ![Date Problem Started] = Me!txtDate
            ![Type of Issue] = Me!cmbType
            ![Remarks] = Me!txtDetailRemarks
            ![Rank] = Me!txtRank
            ![Phone Number DSN] = Me!txtPhone
            ![WorkLog] = Me!txtWorklog.Value
            ![Status] = Me!cmbStatus.Value
        .Update
        FrmChg = 13
    End With
wrkDefault.CommitTrans
 
Else
    With rstClosed
        wrkDefault.BeginTrans
        'Updates current record with changes
        .AddNew
            ![Name] = Me!txtName
            ![User Name] = Me!txtUsername
            ![User ID Number] = Me!txtUserID
            ![Computer Name] = Me!txtComputerName
            ![Building Number] = Me!txtBldgNumber
            ![Room Number] = Me!txtRmNumber
            ![Date Problem Started] = Me!txtDate
            ![Type of Issue] = Me!cmbType
            ![Remarks] = Me!txtDetailRemarks
            ![Rank] = Me!txtRank
            ![Phone Number DSN] = Me!txtPhone
            ![WorkLog] = txtWorklog.Value
            ![Status] = cmbStatus.Value
            ![Ticket Number] = rst![Ticket Number]
        .Update
        wrkDefault.CommitTrans
        .Close
        FrmChg = 12
    End With
 
 
    With rst
        .Delete
        .Close
    End With
 
End If
 
DoCmd.Close
DoCmd.OpenForm "IMO", , , , , acDialog
 
End Sub

What happens is that if I try to update more than one control at a time, when I click update and the form tries to close and reopen I get the "this record has been changed by another user while you were editing" box with the options to save, copy to clipboard or drop the changes. If I copy or drop everything stays as it should be. My data looks exactly like I want it to, except that from here on out every time I close the form regardless of whether I made changes I get the same message. However if I click save changes then for some reason I cant explain rst![WorkLog] is deleted from the record.

My questions are: Is there any reason that I sould not be able to make multiple changes to the record at once? (IE rst![WorkLog] and
rst![Status] both pull changed data from the form) Why is the Worklog field deleted? and how is my code making changes to the recordset after the wrkDefault.CommitTrans command when the only code being executed is:

End If
 
DoCmd.Close
DoCmd.OpenForm "IMO", , , , , acDialog

I apologize for the long drawn out explanation

Any help would be great!!

 

[nIGHTmAYOR]

ok let me clear what your problem is :
You are currently accessing the same record twice , one time visualy through form and the other programaticaly through vba.

how to solve this is simple , you need to halt the form record access during your programatical approach, how you do this vary depending on your setting .. ( you can simply insert a code that moves the record curser to Next record then run your rst.update function and then go back but hey how you achieve unlocking technique depends apon your level of professionality and form complexity )

 

[Bergja]

This seems to make sense however my utter lack of database experience is preventing me from seeing how I am accessing the same record twice. I am sure I am showing my ignorance here but it seems your telling me that the form has the same record open as my recordset code at the same time. At what point does my form open the record? Is it because the record source of the form is the same table as the recordset? I dont want the form to open any records at all, I only want the code to pull data and display it on the form. So I guess what I need to do is instead of halting the form access only during the code access, I want to halt the form access altogether. Now that I am on the right track I should be able to figure this out on my own, but in the sake of expedience if anyone feels the need to enlighten me I would appreciate it

 

[Bergja]

I have fixed the problem, I feel totally retarded because it all makes complete sense now. I had the record source of the form itself set as the same table the recordset was pulling from. Which was causing me no end of greif. It has all been resolved now, thanks for the help!