Locking main form controls causes data in subform to disappear?

[rps04]

Hi all,

So I am creating a Complaint Tracking Database with Access 2013. In my 'complaint entry form' I have added to ability to "Open" and "Close" the complaint. While the complaint is open the user is able to enter/edit data in the complaint (new or old). When the complaint is "Closed" (done so with a button on the form that prompts the user to enter a date of their choice) the user should not be able to enter/edit data in the given complaint. In my original thinking, I placed checks on various events (on load, after update, on click) that checked to see if the current complaint had a date in the "Close date" field. If the close date field was null then that meant that the complaint was open and the the controls would NOT be locked. If the close date field was NOT null (i.e. a date was present) then the complaint was closed and the main form fields were locked but the data was still viewable and unless they "re-opened" the complaint they could not change the data. Now, in the complaint entry form, I have a subform that acts as a action tracker that is used by the user to keep track of the actions taken on the complaint (i.e. they requested documentation from such and such on this date..etc). My problem through all of this is upon closing the complaint and locking the main form controls, my subforms data just vanishes! At first I figured the problem might be with the linking of master and child fields, so I made sure that I left the ID in the main form unlocked when I loop through my controls during the locking process. This did not solve my problem. Does anyone have any insight or experience with this issue? Any help is greatly appreciated thanks! I've posted my code below:

Private Sub Form_Load()
Dim ctl As Control
    
If Not IsNull(Me.close_date) Then
    Me.FormHeader.Visible = True
    Me.btnClosed.Enabled = False
    Me.btnOpen.Enabled = True
    For Each ctl In Me.Controls
        If ctl.Tag = "?" Then
            ctl.Locked = True
        End If
    Next
    
    
Else
    Me.btnClosed.Enabled = True
    Me.btnOpen.Enabled = False
    For Each ctl In Me.Controls
        If ctl.Tag = "?" Then
            ctl.Locked = False
        End If
    Next
End If
DoCmd.SetWarnings True
End Sub

Private Sub Form_AfterUpdate()
Dim ctl As Control
    
If Not IsNull(Me.close_date) Then
    Me.FormHeader.Visible = True
    Me.btnClosed.Enabled = False
    Me.btnOpen.Enabled = True
    For Each ctl In Me.Controls
        If ctl.Tag = "?" Then
            ctl.Locked = True
        End If
    Next
    
    
Else
    Me.btnClosed.Enabled = True
    Me.btnOpen.Enabled = False
    For Each ctl In Me.Controls
        If ctl.Tag = "?" Then
            ctl.Locked = False
        End If
    Next
End If
DoCmd.SetWarnings True
End Sub

Private Sub btnClosed_Click()
Dim ctl As Control
Dim ret As Integer
Dim Answer As String
ReTry:
Answer = InputBox("Please enter the date in the following format: mm/dd/yyyy", "Enter Date", Format(Date, "mm/dd/yyyy"))
 If Answer = "" Then Exit Sub
 'valid dates will be 1900 to 2099. Edit to extend the range
 If Not IsDate(Answer) Or Not Answer Like "[0-3]#/[01]#/[12][09]##" Then
 If MsgBox("Invalid date or invalid date format. " & _
 "Please enter the date in the correct format.", vbRetryCancel) = vbRetry Then
GoTo ReTry
Else: Exit Sub
End If

 End If
 Me.close_date = Answer
 btnClosed.Enabled = False
 btnOpen.Enabled = True
    For Each ctl In Me.Controls
        If ctl.Tag = "?" Then
            ctl.Locked = True
        End If
    Next
  Me.FormHeader.Visible = True

End Sub

Private Sub btnOpen_Click()
Dim ctl As Control

If MsgBox("Are you sure you would like to re-open this complaint?", vbYesNo, "Re-Open Complaint") = vbYes Then
    Me.close_date.Value = Null

Me.btnClosed.Enabled = True
Me.btnOpen.Enabled = False
    For Each ctl In Me.Controls
        If ctl.Tag = "?" Then
            ctl.Locked = False
        End If
    Next
    
Me.FormHeader.Visible = False
End If
End Sub

 

[BlueIshDan]

If you are using your main form's control's values as parameters to your subforms RowSource (QUERY) then upon locking the main form's controls, you might be disabling outside access to their values. Causing the Subform to have a query looking for no values.

Try setting the main forms controls' .Enabled property to false.

 

[rps04]

BlueIshDan,

I switched the .Locked property in my code to .Enabled however the problem still persists. My record source for my subform is a table and has linked master and child fields on the complaint ID, it does not use a lookup query as the record source.

 

[BlueIshDan]

Then try that way. Maybe the .Enabled is breaking the bridge as well.

If this subform is onyl called in this one form. Try the way of calling the main forms controls' values directly inside of a query.

SELECT field1, field2, field3
FROM table1
WHERE field1 = [mainfrmname].[ctrl1], field2 LIKE "*" & [mainfrmname].[ctrl2] & "*"

'... etc

I've done it before, BUT haven't tried with lock/enabled features.

Make a copy of your subform before you do this.