Clear Yes/No check box

[Design by Sue]

I am using my form to add job descriptions and if the user checks the check box for current, it adds the description to the table as the current job description. I need to clear the check box for the next entry but the box retains the choice so when the user selects another description, the form saves it as current even if the box shows no check in the box. I tried the code below, but that only changes the state of the box, but not the reality of the state of the box. Help please.

Me.CheckBox.Value = No

Sue

 

[MarkK]

I don't get how the "state of the box" and the "reality of the state of the box" can differ.
If you are using a checkbox click event to trigger a process, consider using a button instead.

 

[Design by Sue]

I don't know either but when I set the check box to no - there is no check in the box, but when I try to save the next record ( a selection from a combo box) Access "still sees" it as yes and calls the new selection current. I button won't work for this.

Maybe better stated: The user selects the description from a combo box, then either leaves the check box unchecked for "not current" or puts a check in the box for "current" The clicks a save button which adds the description and the choice of current or not in a table. Problem is happening when the user selects a description and clicks the current, hits the save button, then selects another description and clicks save. The check box is unchecked because of the coding Me.Current.Value = No but when the save button is clicked, the newly selected job description is added to the table as a current description.

Sue

 

[boblarson]

In the code for the Add Job Description button, just put it at the end

Me.CheckBoxName = False

it isn't NO, but FALSE.

 

[boblarson]

The check box is unchecked because of the coding Me.Current.Value = No but when the save button is clicked, the newly selected job description is added to the table as a current description.

Sue

Let's see the current code you have for the save button.

 

[Design by Sue]

Will get back to you shortly with this - thanks

 

[MarkK]

And if 'No' didn't cause a compile error, then you need to Require Variable Declaration. In a code window, go to menu Tools -> Options -> Editor tab -> Code Settings section -> Require Variable Declaration check box and make sure it's checked. This adds 'Option Explicit' to the top of your code modules which raises an error if you use undeclared variables.

Without this setting VBA implicitly declares a new variant variable for any undeclared identifier, which essentially hides typos from the compiler, and makes them really hard to find.

 

[Design by Sue]

Getting closer - below is the part of the code that triggers when the user selects a job description and the check box for current is selected.

'checks for previous current job description and if one give option to update to new one
If DCount("*", "Employee Job Description TBL", "[Employee Number]='" & Me.Parent![Employee #] & "' AND [Current]=True") And Me.Current.Value = True Then
  Dim strSQL2 As String
If MsgBox("A current Job Descripton is already assigned to this employee.  Do you want to replace it with this new one?", vbYesNo + vbDefaultButton2 + vbQuestion, "Please Confirm") = vbYes Then
strSQL2 = "UPDATE [Employee Job Description TBL] SET [Current] = 0 WHERE [Employee Number] = '" & Me.Parent![Employee #] & "' AND [Current] = 1"
CurrentDb.Execute strSQL2, dbFailOnError

   'Updates Employee Job Description TBL with new info
  Dim strSQL3 As String
  strSQL3 = "INSERT INTO [Employee Job Description TBL] ([Employee Number],[Job Description], Current) VALUES ('" & Parent.[Employee #] & "', '" & [Job Description Combo] & "', " & Current & ")"
  CurrentDb.Execute strSQL3, dbFailOnError

Me.Current.Value = False
Else
Cancel = True
End If
End If

What I am trying to do in the following line is clear any true values in the table for the current description

strSQL2 = "UPDATE [Employee Job Description TBL] SET [Current] = 0 WHERE [Employee Number] = '" & Me.Parent![Employee #] & "' AND [Current] = 1"

So that when this code is triggered, there is not a current selected and the record will be written in the table

  strSQL3 = "INSERT INTO [Employee Job Description TBL] ([Employee Number],[Job Description], Current) VALUES ('" & Parent.[Employee #] & "', '" & [Job Description Combo] & "', " & Current & ")"

The strSQL2 is not working because I am getting an error message 3022 (duplicates in table) caused by there already being a true in one of the current records already.

I think my error is in AND [Current] = 1" because I don't know how to write it.

(I believe I have all of the check box issues worked out and now this is my current problem - probably was causing some of the check box problems too)



Sue

 

[Design by Sue]

I also tried the following, because in all reality the current record can be set to false for all records for the chosen employee, but that failed also, so maybe the problem is not as I through with the AND [Current] = 1 part. The error message is saying would create duplicates in primary key. The primary key for this table is the Employee Number and the Job Description together, maybe that helps diagnose my problem.

strSQL2 = "UPDATE [Employee Job Description TBL] SET [Current] = 0 WHERE [Employee Number] = '" & Me.Parent![Employee #] & "'"

 

[Design by Sue]

Sorry - I figured out the problem - I am missing an exit sub - the coding is actually working though I have to do a little tweaking.

Thanks all for looking at this.