Checkboxes: to lock down user input data in Access 2003

**elecooley** · Mar 14 '07, 08:27 PM

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:

Code:

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


[B]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[/B]


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** · Mar 15 '07, 01:38 AM

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** · Apr 30 '07, 03:47 PM

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** · Apr 30 '07, 09:42 PM

I noticed that the code in the OnCurrent section (Bold) tests for Null whereas the PgmTypeID_After Update 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).