By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,491 Members | 1,196 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,491 IT Pros & Developers. It's quick & easy.

Checkboxes: to lock down user input data in Access 2003

P: 13
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
Mar 7 '07 #1
Share this Question
Share on Google+
18 Replies


ADezii
Expert 5K+
P: 8,701
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:
Expand|Select|Wrap|Line Numbers
  1. 'Must Save the Record first
  2. SendKeys "+{ENTER}", True      'Save Record
  3. Me.AllowEdits = False
Mar 8 '07 #2

P: 13
The following code will lock the Current Record on your Input Form:
Expand|Select|Wrap|Line Numbers
  1. 'Must Save the Record first
  2. SendKeys "+{ENTER}", True      'Save Record
  3. 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.
Mar 8 '07 #3

P: 13
----

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?
Mar 8 '07 #4

ADezii
Expert 5K+
P: 8,701
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Done_Click()
  2.   Me![<your text box name>].Enabled = Not Me![Done].Value
  3. End Sub
Mar 8 '07 #5

P: 13
Thanks. This was an awesome help. It works just like I wanted.
Mar 8 '07 #6

P: 13
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.
Mar 8 '07 #7

P: 13
Expand|Select|Wrap|Line Numbers
  1. Private Sub Done_Click()
  2.   Me![<your text box name>].Enabled = Not Me![Done].Value
  3. End Sub
I reposted another question.
Mar 8 '07 #8

ADezii
Expert 5K+
P: 8,701
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?
Mar 9 '07 #9

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 9 '07 #10

P: 13
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.
Mar 12 '07 #11

P: 13
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.
Mar 12 '07 #12

NeoPa
Expert Mod 15k+
P: 31,768
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?
Mar 12 '07 #13

P: 13
...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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Done_Click()
  2.   Me![Comments].Enabled = Not Me![Done].Value
  3.   Me![PgmTypeID].Enabled = Not Me![Done].Value
  4.   Me![StartDate].Enabled = Not Me![Done].Value
  5.   Me![EndDate].Enabled = Not Me![Done].Value
  6.   Me![LocationID].Enabled = Not Me![Done].Value
  7.   Me![frm2003PgmSubFaculty].Enabled = Not Me![Done].Value
  8.   Me![HotelName].Enabled = Not Me![Done].Value
  9.   Me![TransportName].Enabled = Not Me![Done].Value
  10.   Me![frm2003PgmSubCoord].Enabled = Not Me![Done].Value
  11.   Me![frm2003PgmSubParticipant].Enabled = Not Me![Done].Value
  12.   Me![TotalPart].Enabled = Not Me![Done].Value
  13.  
  14.  
  15. 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,
Mar 12 '07 #14

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 12 '07 #15

P: 13
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Comments_BeforeUpdate(Cancel As Integer)
  5.  
  6. End Sub
  7. Private Sub Done_Click()
  8.   Me![Comments].Enabled = Not Me![Done].Value
  9.   Me![PgmTypeID].Enabled = Not Me![Done].Value
  10.   Me![StartDate].Enabled = Not Me![Done].Value
  11.   Me![EndDate].Enabled = Not Me![Done].Value
  12.   Me![LocationID].Enabled = Not Me![Done].Value
  13.   Me![frm2003PgmSubFaculty].Enabled = Not Me![Done].Value
  14.   Me![HotelName].Enabled = Not Me![Done].Value
  15.   Me![TransportName].Enabled = Not Me![Done].Value
  16.   Me![frm2003PgmSubCoord].Enabled = Not Me![Done].Value
  17.   Me![frm2003PgmSubParticipant].Enabled = Not Me![Done].Value
  18.   Me![TotalPart].Enabled = Not Me![Done].Value
  19.  
  20. End Sub
  21. Private Sub Form_Open(Cancel As Integer)
  22.     DoCmd.Maximize
  23. End Sub
  24.  
  25. Private Sub cmdClose_Click() '7/30
  26. On Error GoTo Err_cmdClose_Click
  27.     DoCmd.Close
  28.  
  29. Exit_cmdClose_Click:
  30.     Exit Sub
  31.  
  32. Err_cmdClose_Click:
  33.     MsgBox Err.Description
  34.     Resume Exit_cmdClose_Click
  35. End Sub
  36.  
  37. Private Sub cmdClose2_Click()
  38. On Error GoTo Err_cmdClose2_Click
  39.     DoCmd.Close
  40.  
  41. Exit_cmdClose2_Click:
  42.     Exit Sub
  43.  
  44. Err_cmdClose2_Click:
  45.     MsgBox Err.Description
  46.     Resume Exit_cmdClose2_Click
  47. End Sub
  48.  
  49. Private Sub Form_Activate()
  50.     'DoCmd.Requery
  51.     End Sub
  52. Private Sub LocationID_AfterUpdate()
  53.      Me!frm2003PgmSubParticipant!frm2003PgmSubPartQuest.Requery
  54.      'Me.Refresh   '*** needed 11/12
  55.      DoEvents
  56. End Sub
  57.  
  58. Private Sub cmdGraph_Click()  'good
  59. Dim db As DAO.Database
  60. Dim myXL As Excel.Application
  61. Dim qdef As QueryDef
  62. Dim rst As DAO.Recordset
  63. Dim FilePath As String
  64. Dim SavedFilename As String
  65.  
  66. Dim i As Byte
  67. 'Dim k As Byte
  68. Dim j As Long
  69.  
  70. Set db = CurrentDb
  71. FilePath = Left(db.Name, LastOccurence(db.Name, "\"))
  72.  
  73. Set myXL = CreateObject("Excel.Application")
  74. myXL.Workbooks.Open (FilePath & "Finals_Graph_2003Template.xls")
  75. myXL.Application.Visible = True
  76. myXL.Parent.Windows(1).Visible = True
  77.  
  78. '*********** populate program info and averages
  79. Set qdef = db.QueryDefs("qry2003PgmAverage")
  80. qdef.Parameters![PgmID] = Forms!frm2003Pgm!PgmID
  81. Set rst = qdef.OpenRecordset(dbReadOnly)
  82.  
  83. rst.MoveLast    'populate recordset
  84. rst.MoveFirst
  85.  
  86. With myXL.Application
  87.         DoEvents
  88.         'Sheets("Graph").Select
  89.        .Cells(2, 14).Value = rst.Fields(0).Value  'N
  90.        .Cells(5, 14).Value = rst.Fields(14).Value  'long program name
  91.        .Cells(3, 14).Value = rst.Fields(15).Value 'program dates
  92.        .Cells(4, 14).Value = rst.Fields(16).Value 'location
  93.        .Cells(24, 15).Value = rst.Fields(18).Value  'program satisfaction
  94.  
  95.         For i = 1 To 12
  96.             .Cells(8 + i, 15).Value = rst.Fields(i).Value
  97.             .Cells(8 + i, 16).Value = rst.Fields(i + 18).Value
  98.         Next i
  99.  
  100.         'For k = 19 To 31
  101.             '.Cells(8 + k, 16).Value = rst.Fields(k).Value
  102.         'Next k
  103.  
  104.         DoEvents
  105. End With
  106.  
  107. Set rst = Nothing
  108. Set qdef = Nothing
  109.  
  110. '**********'populate text of questions
  111. Set qdef = db.QueryDefs("qry2003PgmOutcomes")
  112. qdef.Parameters![PgmID] = Forms!frm2003Pgm!PgmID
  113. Set rst = qdef.OpenRecordset(dbReadOnly)
  114.  
  115. rst.MoveLast    'populate recordset
  116. rst.MoveFirst
  117.  
  118. With myXL.Application
  119.         DoEvents
  120.         For i = 1 To 12
  121.                .Cells(8 + i, 14).Value = rst.Fields(i).Value
  122.         Next i
  123. End With
  124.  
  125. 'change the following line if you want a specific printer
  126. 'myXL.Application.ActivePrinter = "\\CS_FSPSMS01\3rd Floor Offices 5Si on Ne01:"
  127.  
  128. 'myXL.Application.SendKeys "^p"
  129. 'myXL.Application.SendKeys "{enter}", -1
  130.  
  131. SavedFilename = FilePath & rst.Fields(13).Value & _
  132.     " Finals " & rst.Fields(14) & " " & rst.Fields(15)
  133.  
  134. 'need time for printing to catch-up
  135. For j = 1 To 1000000
  136. Next j
  137. DoEvents
  138.  
  139. myXL.Application.ActiveWorkbook.SaveAs SavedFilename
  140.  
  141. 'myXL.Application.Worksheets("Graph").PrintPreview
  142.  
  143. myXL.Application.DisplayAlerts = False
  144. myXL.Application.Quit
  145.  
  146. Set myXL = Nothing
  147. Set db = Nothing
  148. Set rst = Nothing
  149. Set qdef = Nothing
  150.  
  151. End Sub
  152.  
  153.  
  154. Private Sub cmdFacilitator_Click()
  155. On Error GoTo Err_cmdFacilitator_Click
  156.  
  157.     Dim stDocName As String
  158.  
  159.     stDocName = "rpt2003Summary_Facilitator"
  160.     DoCmd.OpenReport stDocName, acPreview
  161.  
  162. Exit_cmdFacilitator_Click:
  163.     Exit Sub
  164.  
  165. Err_cmdFacilitator_Click:
  166.     MsgBox Err.Description
  167.     Resume Exit_cmdFacilitator_Click
  168.  
  169. End Sub
  170.  
  171.  
  172. Private Sub cmdNoFacilitator_Click()
  173. On Error GoTo Err_cmdNoFacilitator_Click
  174.  
  175.     Dim stDocName As String
  176.  
  177.     stDocName = "rpt2003Summary_NoFacilitator"
  178.     DoCmd.OpenReport stDocName, acPreview
  179.  
  180. Exit_cmdNoFacilitator_Click:
  181.     Exit Sub
  182.  
  183. Err_cmdNoFacilitator_Click:
  184.     MsgBox Err.Description
  185.     Resume Exit_cmdNoFacilitator_Click
  186.  
  187. End Sub
  188.  
  189.  
  190. Private Sub Form_Current()  'one record to another
  191.  
  192.  
  193.  
  194.   If IsNull(Me!PgmTypeID) Then
  195.             Me.LocationID.Enabled = False
  196.             Me.StartDate.Enabled = False
  197.             Me.EndDate.Enabled = False
  198.             Me.TotalPart.Enabled = False
  199.             Me.Comments.Enabled = False
  200.             Me.Done.Enabled = False
  201.             Me.DateMailedGoalRep.Enabled = False
  202.             Me.DatedMailedLetter.Enabled = False
  203.             Me.frm2003PgmSubFaculty.Enabled = False
  204.             Me.frm2003PgmSubCoord.Enabled = False
  205.             Me.frm2003PgmSubParticipant.Enabled = False
  206.   Else
  207.             Me.LocationID.Enabled = True
  208.             Me.StartDate.Enabled = True
  209.             Me.EndDate.Enabled = True
  210.             Me.TotalPart.Enabled = True
  211.             Me.Comments.Enabled = True
  212.             Me.Done.Enabled = True
  213.             Me.DateMailedGoalRep.Enabled = True
  214.             Me.DatedMailedLetter.Enabled = True
  215.             Me.frm2003PgmSubFaculty.Enabled = True
  216.             Me.frm2003PgmSubCoord.Enabled = True
  217.             Me.frm2003PgmSubParticipant.Enabled = True
  218.  
  219.  
  220. End If
  221.  
  222. End Sub
  223.  
  224.  
  225. Private Sub PgmTypeID_AfterUpdate()
  226.     Me!frm2003PgmSubParticipant!frm2003PgmSubPartQuest.Requery
  227.     If (PgmTypeID) <> "" Then
  228.            Me.LocationID.Enabled = True
  229.             Me.StartDate.Enabled = True
  230.             Me.EndDate.Enabled = True
  231.             Me.TotalPart.Enabled = True
  232.             Me.Comments.Enabled = True
  233.             Me.Done.Enabled = True
  234.             Me.DateMailedGoalRep.Enabled = True
  235.             Me.DatedMailedLetter.Enabled = True
  236.             Me.frm2003PgmSubFaculty.Enabled = True
  237.             Me.frm2003PgmSubCoord.Enabled = True
  238.             Me.frm2003PgmSubParticipant.Enabled = True
  239.             'Me.Refresh  ' needed?
  240.             DoEvents
  241.      Else
  242.         MsgBox "Please Select a program", , "Select Program"
  243.     End If
  244.  End Sub
  245. Private Sub cmdSummaryParticipant_Click()
  246. On Error GoTo Err_cmdSummaryParticipant_Click
  247.  
  248.     Dim stDocName As String
  249.  
  250.     stDocName = "rpt2003Summary_ParticipantVersion"
  251.     DoCmd.OpenReport stDocName, acPreview
  252.  
  253. Exit_cmdSummaryParticipant_Click:
  254.     Exit Sub
  255.  
  256. Err_cmdSummaryParticipant_Click:
  257.     MsgBox Err.Description
  258.     Resume Exit_cmdSummaryParticipant_Click
  259.  
  260. End Sub
  261.  
  262. Private Sub cmdAllSupport_Click()
  263. On Error GoTo Err_cmdAllSupport_Click
  264.  
  265.     Dim stDocName As String
  266.  
  267.      Me.Refresh
  268.     stDocName = "rpt2003Summary_SupportServices"
  269.     DoCmd.OpenReport stDocName, acPreview
  270.  
  271. Exit_cmdAllSupport_Click:
  272.     Exit Sub
  273.  
  274. Err_cmdAllSupport_Click:
  275.     MsgBox Err.Description
  276.     Resume Exit_cmdAllSupport_Click
  277. End Sub
  278. Private Sub cmdfood_Click()
  279. On Error GoTo Err_cmdfood_Click
  280.  
  281.     Dim stDocName As String
  282.  
  283.     stDocName = "rpt2003Summary_food"
  284.     DoCmd.OpenReport stDocName, acPreview
  285.  
  286. Exit_cmdfood_Click:
  287.     Exit Sub
  288.  
  289. Err_cmdfood_Click:
  290.     MsgBox Err.Description
  291.     Resume Exit_cmdfood_Click
  292.  
  293. End Sub
  294.  
  295.  
  296. Private Sub cmdHotel_Click()
  297. On Error GoTo Err_cmdHotel_Click
  298.  
  299.     Dim stDocName As String
  300.  
  301.      Me.Refresh
  302.      DoEvents
  303.  
  304.     stDocName = "rpt2003Summary_hotel"
  305.     DoCmd.OpenReport stDocName, acPreview
  306.  
  307. Exit_cmdHotel_Click:
  308.     Exit Sub
  309.  
  310. Err_cmdHotel_Click:
  311.     MsgBox Err.Description
  312.     Resume Exit_cmdHotel_Click
  313.  
  314. End Sub
  315.  
  316.  
  317. Private Sub cmdTransport2_Click()  'good
  318. On Error GoTo Err_cmdTransport2_Click
  319.  
  320.     Dim stDocName As String
  321.  
  322.      Me.Refresh
  323.     stDocName = "rpt2003Summary_transportation"
  324.     DoCmd.OpenReport stDocName, acPreview
  325. Exit_cmdTransport2_Click:
  326.     Exit Sub
  327.  
  328. Err_cmdTransport2_Click:
  329.     MsgBox Err.Description
  330.     Resume Exit_cmdTransport2_Click
  331.  
  332. End Sub
Mar 14 '07 #16

NeoPa
Expert Mod 15k+
P: 31,768
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)?
Mar 15 '07 #17

P: 13
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
Apr 30 '07 #18

NeoPa
Expert Mod 15k+
P: 31,768
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).
Apr 30 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.