Data Entry Manipulation

[Dinger_80]

I am working on updating some features and code for my database. I have different access levels to my database. This system was intended to collect requests from users for testing to be conducted. A supervisor form to handle the requests was set up. I set up that form and forms that could allow multiple users to access the same record to only allow one user to access the record at a time. At any time I could have conflicts as often as 2 or 3 times a week. Which if trying to maintain any semblance of integrity isn't good. That being said, my problem is on the supervisor form. What I am doing is allowing the supervisor to delete information from the record. Now they can only delete to fields worth of information. So I set up an update query to null the fields out. The problem I have is that when the record is selected it isn't possible to update the fields to null because the record is locked out. I found this out by running the query while having the particular record selected. So I played around some and found out if I change the status of the Data Entry for the form, I can manipulate the record as I had wanted to. The problem I am running into is that when I turn the Data Entry from false to true, other fields have their value changed as well. One is a combo box, the other a check box and even a date field. Now none of the changes that happen on the form are saved to the record, however it just looks all messed up. I certainly have methods for which I can make things look proper again, I just am looking to see if there is a better way or what may be causing the toggling of the Data Entry to false has on the form itself. Any advice is appreciate. Here is my code also if anyone wants to have a look.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim CHID As String
Dim FLName As String
Dim TRN As DAO.Recordset

Set db = CurrentDb
    Set rs = db.OpenRecordset("UserInformationQuery")
        CHID = rs!ID
        FLName = rs!FullName
    rs.Close
    
If Me.IsSampleHere = True Then
    Exit Sub
    DoCmd.OpenForm "SampleEntryForm"
    DoCmd.GoToRecord , , acNewRec
    Forms!SampleEntryForm!TRNumberCombo = Me.TRNumberCombo.Column(0)
    Forms!SampleEntryForm!SampleInputDate = Format(Now, "MM/DD/YYYY")
    Forms!SampleEntryForm!TechnicianApproval = CHID
    Forms!SampleEntryForm!TechnicianFullName = FLName
Else
    If MsgBox("A sample has alread been logged into the system." & _
                vbNewLine & _
                "Are you intentionally marking the sample as not " & _
                vbNewLine & _
                "being here?", vbQuestion + vbYesNo, "Sample Not Here?") = vbNo Then
        Me.IsSampleHere = True
    Else
        If MsgBox("Do you want to remove the sample entry from the database?", vbQuestion + vbYesNo) = vbYes Then
            Me.DataEntry = True
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "DeleteSampleEntryQuery"
            DoCmd.OpenQuery "UpdateSampleDeletedLocationQuery"
            DoCmd.SetWarnings True
            Me.DataEntry = False
            
            Set TRN = Me.RecordsetClone
            TRN.FindFirst "[TRNumber] = """ & Me.TRNumberCombo.Column(1) & """"
            If TRN.NoMatch Then
                MsgBox "There was a problem with the record you selected."
            Else
                Me.Bookmark = TRN.Bookmark
            End If
    Set TRN = Nothing
        Else
            MsgBox "Please contact a local admin for assistance.", vbInformation
            Exit Sub
        End If
    End If
End If

 

[JLCantara]

I just don't understand why you get into this complicated procedure. If the data is displayed why can't your supervisor null the field?

 

[Dinger_80]

JLCantara,

The reason my supervisor can't null the specified fields that are being nulled out by the update query are because they are not a part of the form. The form is simply designed to allow the supervisor to select a record, verify the contents of the folder, change the status of a record as need be, assign a technician to the record, add any comments, verify the sample is here, delay testing of the record, verify if a tech has completed the record and mark the record as completed. I don't have the information regarding the sample because 99% of the time, there will never be a reason to have to view the information, much less delete the information. This code is simply for the rare times when something out of the ordinary happens. Being as there is already a bunch of information on the form, I don't want to clutter the form. This way it keeps things cleaner and doesn't allow for accidental deletion of information that otherwise wouldn't need to be deleted.

 

[JLCantara]

OK. Here is a procedure that will always work.
1- Make sure that all the annullable field are present on the form but invisible i.e. stored in an invisible form footer.
2- When annulation is triggered, put some code that sets them to null.
3- Add a docmd that will force save the record.

That should do the job.

 

[Dinger_80]

Fair enough. I will go along with that. That makes life a lot easier. Thank you for idea.

 

[spikepl]

I don't know whether your problem is solved or not, but in the future, use the usual tools for presenting written info such as line breaks and empty lines to break it into paragraphs concerning contiguous thoughts.

You have presented one big black blob (try to repeat these 3 words a few times in a row, but fast )which is basically unreadable for lazy people like me who scan many people's posts. So I skipped it. I suspect others did too.