Checkboxes: to lock down user input data in Access 2003

[elecooley]

I have a checkbox on the form that says "When all data has been entered, check this box". Right now it does nothing, but I want it to lock down that particular set of data so the data cannot be changed from the data input form. I would still like to be able to run queries on the data, but would basically like to lock down the data so other users cannont change it.

Thanks for the help

[ADezii]

I have a checkbox on the form that says "When all data has been entered, check this box". Right now it does nothing, but I want it to lock down that particular set of data so the data cannot be changed from the data input form. I would still like to be able to run queries on the data, but would basically like to lock down the data so other users cannont change it.

Thanks for the help

The following code will lock the Current Record on your Input Form:

'Must Save the Record first
SendKeys "+{ENTER}", True      'Save Record
Me.AllowEdits = False

[elecooley]

The following code will lock the Current Record on your Input Form:

'Must Save the Record first
SendKeys "+{ENTER}", True      'Save Record
Me.AllowEdits = False

----

This does not seem to work. This is what the code looks like in the form after I input the above code:

Private Sub Done_Click()
'Must Save the Record first
SendKeys "+{ENTER}", True 'Save Record
Me.AllowEdits = False
End Sub

I am rather new to working on the "background" of databases, so there may be something else that I may have to do that I am not seeing.
Thanks for the help and very much appreciate it. Maybe there is something I am missing in this though.

[elecooley]

----

This does not seem to work. This is what the code looks like in the form after I input the above code:

Private Sub Done_Click()
'Must Save the Record first
SendKeys "+{ENTER}", True 'Save Record
Me.AllowEdits = False
End Sub

I am rather new to working on the "background" of databases, so there may be something else that I may have to do that I am not seeing.
Thanks for the help and very much appreciate it. Maybe there is something I am missing in this though.

When a user puts in the data and then checks the box, i would like the boxes where they put in data to "grey out" and not be able to put any more data in it. The only way a user could put more data in would be to uncheck the check box and then they could put more data in. Is this possible?

[ADezii]

When a user puts in the data and then checks the box, i would like the boxes where they put in data to "grey out" and not be able to put any more data in it. The only way a user could put more data in would be to uncheck the check box and then they could put more data in. Is this possible?

Private Sub Done_Click()
  Me![<your text box name>].Enabled = Not Me![Done].Value
End Sub

[elecooley]

Thanks. This was an awesome help. It works just like I wanted.

[elecooley]

That worked great, but when I close out of the document, then get back in it the data is able to be changed, even with the checkbox checked. If i uncheck the checkbox, then RE check it the data gets greyed out like I wanted, but I need it to ALLWays be greyed out when the check box is allways checked (even when the database is closed out then reopened).

Hopefully that makes sense?! :)

Thanks for all the help.

[elecooley]

Private Sub Done_Click()
  Me![<your text box name>].Enabled = Not Me![Done].Value
End Sub

I reposted another question.

[ADezii]

That worked great, but when I close out of the document, then get back in it the data is able to be changed, even with the checkbox checked. If i uncheck the checkbox, then RE check it the data gets greyed out like I wanted, but I need it to ALLWays be greyed out when the check box is allways checked (even when the database is closed out then reopened).

Hopefully that makes sense?! :)

Thanks for all the help.

You are 100% right - the code is only session specific. In order to make this scheme persist, I can write a value to the Registry indicating the current state of the Check Box. When the Form is again opened, it can read this value than Enable/Disable Controls accordingly. The other option would be to write the value to a Table from which it can be read and acted upon. Is this what you want?

[NeoPa]

That worked great, but when I close out of the document, then get back in it the data is able to be changed, even with the checkbox checked. If i uncheck the checkbox, then RE check it the data gets greyed out like I wanted, but I need it to ALLWays be greyed out when the check box is allways checked (even when the database is closed out then reopened).

Hopefully that makes sense?! :)

Thanks for all the help.

In that case you need to :

1. Have a field in your table to store this value.
2. Bind your CheckBox to this field.
3. Add the same code to your Form_Current() procedure.

BTW I'm not sure what your latest post was about. I can't imagine it was an expression of impatience after only seven minutes.

[elecooley]

In that case you need to :

1. Have a field in your table to store this value.
2. Bind your CheckBox to this field.
3. Add the same code to your Form_Current() procedure.

BTW I'm not sure what your latest post was about. I can't imagine it was an expression of impatience after only seven minutes.

No, It was not impatience. I am still getting familiar with the postings and how they work, that is all. Thanks for calling me on it.

[elecooley]

You are 100% right - the code is only session specific. In order to make this scheme persist, I can write a value to the Registry indicating the current state of the Check Box. When the Form is again opened, it can read this value than Enable/Disable Controls accordingly. The other option would be to write the value to a Table from which it can be read and acted upon. Is this what you want?

Honestly, I am not sure which one is better. I am not familiar with "adding a value to the registry". Is this in the database itself?

Again, thanks for the wonderful help.

[NeoPa]

In that case you need to :

1. Have a field in your table to store this value.
2. Bind your CheckBox to this field.
3. Add the same code to your Form_Current() procedure.

BTW I'm not sure what your latest post was about. I can't imagine it was an expression of impatience after only seven minutes.

No, It was not impatience. I am still getting familiar with the postings and how they work, that is all. Thanks for calling me on it.

...but this solved your problem? Or not?

[elecooley]

...but this solved your problem? Or not?

1) I do have the filed in my table to store the value.

--Under "FieldName" I have named it "Done".
--Under "DataType" I have "Yes/No"
--Under "Description" I have a general description of what it does

2) The checkbox is bound to the field "Done"
3) i do not have a Form_Current that I can see under my forms. I do have this code under my cmdCLose:

Private Sub Done_Click()
  Me![Comments].Enabled = Not Me![Done].Value
  Me![PgmTypeID].Enabled = Not Me![Done].Value
  Me![StartDate].Enabled = Not Me![Done].Value
  Me![EndDate].Enabled = Not Me![Done].Value
  Me![LocationID].Enabled = Not Me![Done].Value
  Me![frm2003PgmSubFaculty].Enabled = Not Me![Done].Value
  Me![HotelName].Enabled = Not Me![Done].Value
  Me![TransportName].Enabled = Not Me![Done].Value
  Me![frm2003PgmSubCoord].Enabled = Not Me![Done].Value
  Me![frm2003PgmSubParticipant].Enabled = Not Me![Done].Value
  Me![TotalPart].Enabled = Not Me![Done].Value
 
 
End Sub

-------

All of this DOES gray out the fields, but when I close out and reenter the db they are no longer grayed out, but the checkbox is still checked. Do I need to do the suggestions made by ADezii ?

Thanks,

[NeoPa]

What you need to do is to follow instruction #3 in post #10.

In that case you need to :

1. Have a field in your table to store this value.
2. Bind your CheckBox to this field.
3. Add the same code to your Form_Current() procedure.

Let me know if you have any difficulty with this.

[elecooley]

I think there is a "conflict" with this (see the BOLD code I highlighted) but I do not know how to resolve it.


----------------------------------------------------------------

I guess I need the checkbox code to "trump" the above code when the form is reopened. Hopefully that makes sense. The above code is only for when a NEW document is opened, then after all the new information is put in, a user should be able to "check" the checkbox and every thing will grey out. Then if the user closes the form and gets back in it, it should still be greyed out until the user UNCHECKS the checkbox.


Here is all the code contained, that I am working with:

Option Compare Database
Option Explicit
 
Private Sub Comments_BeforeUpdate(Cancel As Integer)
 
End Sub
Private Sub Done_Click()
  Me![Comments].Enabled = Not Me![Done].Value
  Me![PgmTypeID].Enabled = Not Me![Done].Value
  Me![StartDate].Enabled = Not Me![Done].Value
  Me![EndDate].Enabled = Not Me![Done].Value
  Me![LocationID].Enabled = Not Me![Done].Value
  Me![frm2003PgmSubFaculty].Enabled = Not Me![Done].Value
  Me![HotelName].Enabled = Not Me![Done].Value
  Me![TransportName].Enabled = Not Me![Done].Value
  Me![frm2003PgmSubCoord].Enabled = Not Me![Done].Value
  Me![frm2003PgmSubParticipant].Enabled = Not Me![Done].Value
  Me![TotalPart].Enabled = Not Me![Done].Value
 
End Sub
Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
End Sub
 
Private Sub cmdClose_Click() '7/30
On Error GoTo Err_cmdClose_Click
    DoCmd.Close
 
Exit_cmdClose_Click:
    Exit Sub
 
Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
End Sub
 
Private Sub cmdClose2_Click()
On Error GoTo Err_cmdClose2_Click
    DoCmd.Close
 
Exit_cmdClose2_Click:
    Exit Sub
 
Err_cmdClose2_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose2_Click
End Sub
 
Private Sub Form_Activate()
    'DoCmd.Requery
    End Sub
Private Sub LocationID_AfterUpdate()
     Me!frm2003PgmSubParticipant!frm2003PgmSubPartQuest.Requery
     'Me.Refresh   '*** needed 11/12
     DoEvents
End Sub
 
Private Sub cmdGraph_Click()  'good
Dim db As DAO.Database
Dim myXL As Excel.Application
Dim qdef As QueryDef
Dim rst As DAO.Recordset
Dim FilePath As String
Dim SavedFilename As String
 
Dim i As Byte
'Dim k As Byte
Dim j As Long
 
Set db = CurrentDb
FilePath = Left(db.Name, LastOccurence(db.Name, "\"))
 
Set myXL = CreateObject("Excel.Application")
myXL.Workbooks.Open (FilePath & "Finals_Graph_2003Template.xls")
myXL.Application.Visible = True
myXL.Parent.Windows(1).Visible = True
 
'*********** populate program info and averages
Set qdef = db.QueryDefs("qry2003PgmAverage")
qdef.Parameters![PgmID] = Forms!frm2003Pgm!PgmID
Set rst = qdef.OpenRecordset(dbReadOnly)
 
rst.MoveLast    'populate recordset
rst.MoveFirst
 
With myXL.Application
        DoEvents
        'Sheets("Graph").Select
       .Cells(2, 14).Value = rst.Fields(0).Value  'N
       .Cells(5, 14).Value = rst.Fields(14).Value  'long program name
       .Cells(3, 14).Value = rst.Fields(15).Value 'program dates
       .Cells(4, 14).Value = rst.Fields(16).Value 'location
       .Cells(24, 15).Value = rst.Fields(18).Value  'program satisfaction
 
        For i = 1 To 12
            .Cells(8 + i, 15).Value = rst.Fields(i).Value
            .Cells(8 + i, 16).Value = rst.Fields(i + 18).Value
        Next i
 
        'For k = 19 To 31
            '.Cells(8 + k, 16).Value = rst.Fields(k).Value
        'Next k
 
        DoEvents
End With
 
Set rst = Nothing
Set qdef = Nothing
 
'**********'populate text of questions
Set qdef = db.QueryDefs("qry2003PgmOutcomes")
qdef.Parameters![PgmID] = Forms!frm2003Pgm!PgmID
Set rst = qdef.OpenRecordset(dbReadOnly)
 
rst.MoveLast    'populate recordset
rst.MoveFirst
 
With myXL.Application
        DoEvents
        For i = 1 To 12
               .Cells(8 + i, 14).Value = rst.Fields(i).Value
        Next i
End With
 
'change the following line if you want a specific printer
'myXL.Application.ActivePrinter = "\\CS_FSPSMS01\3rd Floor Offices 5Si on Ne01:"
 
'myXL.Application.SendKeys "^p"
'myXL.Application.SendKeys "{enter}", -1
 
SavedFilename = FilePath & rst.Fields(13).Value & _
    " Finals " & rst.Fields(14) & " " & rst.Fields(15)
 
'need time for printing to catch-up
For j = 1 To 1000000
Next j
DoEvents
 
myXL.Application.ActiveWorkbook.SaveAs SavedFilename
 
'myXL.Application.Worksheets("Graph").PrintPreview
 
myXL.Application.DisplayAlerts = False
myXL.Application.Quit
 
Set myXL = Nothing
Set db = Nothing
Set rst = Nothing
Set qdef = Nothing
 
End Sub
 
 
Private Sub cmdFacilitator_Click()
On Error GoTo Err_cmdFacilitator_Click
 
    Dim stDocName As String
 
    stDocName = "rpt2003Summary_Facilitator"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdFacilitator_Click:
    Exit Sub
 
Err_cmdFacilitator_Click:
    MsgBox Err.Description
    Resume Exit_cmdFacilitator_Click
 
End Sub
 
 
Private Sub cmdNoFacilitator_Click()
On Error GoTo Err_cmdNoFacilitator_Click
 
    Dim stDocName As String
 
    stDocName = "rpt2003Summary_NoFacilitator"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdNoFacilitator_Click:
    Exit Sub
 
Err_cmdNoFacilitator_Click:
    MsgBox Err.Description
    Resume Exit_cmdNoFacilitator_Click
 
End Sub
 
 
 Private Sub Form_Current()  'one record to another
 
 
 
  If IsNull(Me!PgmTypeID) Then
            Me.LocationID.Enabled = False
            Me.StartDate.Enabled = False
            Me.EndDate.Enabled = False
            Me.TotalPart.Enabled = False
            Me.Comments.Enabled = False
            Me.Done.Enabled = False
            Me.DateMailedGoalRep.Enabled = False
            Me.DatedMailedLetter.Enabled = False
            Me.frm2003PgmSubFaculty.Enabled = False
            Me.frm2003PgmSubCoord.Enabled = False
            Me.frm2003PgmSubParticipant.Enabled = False
  Else
            Me.LocationID.Enabled = True
            Me.StartDate.Enabled = True
            Me.EndDate.Enabled = True
            Me.TotalPart.Enabled = True
            Me.Comments.Enabled = True
            Me.Done.Enabled = True
            Me.DateMailedGoalRep.Enabled = True
            Me.DatedMailedLetter.Enabled = True
            Me.frm2003PgmSubFaculty.Enabled = True
            Me.frm2003PgmSubCoord.Enabled = True
            Me.frm2003PgmSubParticipant.Enabled = True
 
 
End If
 
End Sub
 
 
Private Sub PgmTypeID_AfterUpdate()
    Me!frm2003PgmSubParticipant!frm2003PgmSubPartQuest.Requery
    If (PgmTypeID) <> "" Then
           Me.LocationID.Enabled = True
            Me.StartDate.Enabled = True
            Me.EndDate.Enabled = True
            Me.TotalPart.Enabled = True
            Me.Comments.Enabled = True
            Me.Done.Enabled = True
            Me.DateMailedGoalRep.Enabled = True
            Me.DatedMailedLetter.Enabled = True
            Me.frm2003PgmSubFaculty.Enabled = True
            Me.frm2003PgmSubCoord.Enabled = True
            Me.frm2003PgmSubParticipant.Enabled = True
            'Me.Refresh  ' needed?
            DoEvents
     Else
        MsgBox "Please Select a program", , "Select Program"
    End If
 End Sub
Private Sub cmdSummaryParticipant_Click()
On Error GoTo Err_cmdSummaryParticipant_Click
 
    Dim stDocName As String
 
    stDocName = "rpt2003Summary_ParticipantVersion"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdSummaryParticipant_Click:
    Exit Sub
 
Err_cmdSummaryParticipant_Click:
    MsgBox Err.Description
    Resume Exit_cmdSummaryParticipant_Click
 
End Sub
 
Private Sub cmdAllSupport_Click()
On Error GoTo Err_cmdAllSupport_Click
 
    Dim stDocName As String
 
     Me.Refresh
    stDocName = "rpt2003Summary_SupportServices"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdAllSupport_Click:
    Exit Sub
 
Err_cmdAllSupport_Click:
    MsgBox Err.Description
    Resume Exit_cmdAllSupport_Click
End Sub
Private Sub cmdfood_Click()
On Error GoTo Err_cmdfood_Click
 
    Dim stDocName As String
 
    stDocName = "rpt2003Summary_food"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdfood_Click:
    Exit Sub
 
Err_cmdfood_Click:
    MsgBox Err.Description
    Resume Exit_cmdfood_Click
 
End Sub
 
 
Private Sub cmdHotel_Click()
On Error GoTo Err_cmdHotel_Click
 
    Dim stDocName As String
 
     Me.Refresh
     DoEvents
 
    stDocName = "rpt2003Summary_hotel"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdHotel_Click:
    Exit Sub
 
Err_cmdHotel_Click:
    MsgBox Err.Description
    Resume Exit_cmdHotel_Click
 
End Sub
 
 
Private Sub cmdTransport2_Click()  'good
On Error GoTo Err_cmdTransport2_Click
 
    Dim stDocName As String
 
     Me.Refresh
    stDocName = "rpt2003Summary_transportation"
    DoCmd.OpenReport stDocName, acPreview
Exit_cmdTransport2_Click:
    Exit Sub
 
Err_cmdTransport2_Click:
    MsgBox Err.Description
    Resume Exit_cmdTransport2_Click
 
End Sub

[NeoPa]

I'm not sure where you're going with this.
Is this a response to my last post? It's not recognisable as such.
What do you really think I'm going to do with a mountain of your code (Not even in [code] tags)?

[elecooley]

NeoPa --

Ok, sorry about posting the long set of code. I am trying to learn all the ins and outs of this, and it is going slow.

Post #15: I have done this, and it works. So, when I am on the current form and click the checkbox, it "locks down" the data (grays it out). That is what I want. But again, when I exit that form and go back to the main switchboard, then go back into the previous form, the checkbox is still checked, but the data is no longer locked down (or grayed out). I know, I am probably missing something simple. I have tried writing code so that when the form opens it looks at the checkbox to see if it is checked, and if it is then it locks down the data. But I cant seem to get it to work?

Thanks for any help again

[NeoPa]

I noticed that the code in the OnCurrent section (Bold) tests for Null whereas the PgmTypeID_AfterUpdate code tests for "" (empty string).
Otherwise you handle both the changing of the status and the finding of the status in your code (which is a good sign).
Neither bit of code seems to refer to a CheckBox though (which your last post refers to).