Using Check Box with VBA

[WickidWe]

Hi I have a form to Update remove and delete team members
in my Employee table I have a yes/no field called "Active"

I wish to have a checkbox that will show whether or not a team member is active.
and when unticked to show a message box asking "are you sure" or something like that.


I would like to use vba if I can get some instruction.

thanks

[June7]

Try the Change event of the checkbox:

If Me.Checkbox = False Then
If MsgBox("Are you sure you want to deactivate?", vbYesNo) = vbNo Then Me.Checkbox = True
End If

[WickidWe]

how can I link the checkbox to the correct table or query?

[June7]

Is the form bound to Employee table or query? Is checkbox bound to Active field?

[WickidWe]

No not yet, How do I Bind it to a Particular Field from a particular table?

[June7]

With ControlSource property, just like any other data control on form.

[WickidWe]

Can I use a Query for the control source using VBA?

[June7]

I think not, but not really understanding the question.

I don't understand issue. The field to indicated status is not included in the form RecordSource?

[WickidWe]

Ok here is an overview.

I have a Employees details table
My first combo box shows the Employee Number, employee name among other things, based on a query in the record source.
when I select a name from this combo box I want the checkbox to reflect a field in the Employees Details table which is called Active and is a yes/No field.
example:

if i select "Joe Bloggs" from the combobox and the "Active" field is ticked in the Employees Details table
I want the checkbox on my form to be ticked.
and vice versa

I also want to be able to tick or untick on the form to change the status in the table to reflect the form.

Does this make any more sense?

[June7]

1. Code in the combobox AfterUpdate event could set the value of the checkbox, like:
Me.checkbox = DLookup("Active", "EmployeeDetails", "EmpID=" & Me.EmpID)

2. Code in the checkbox Change event could set value of field in table record, like:
CurrentDb.Execute "UPDATE EmployeeDetails SET Active = " & Me.checkbox & " WHERE EmpID=" & Me.EmpID

What is purpose of this form? Is this form even bound to data?

If employees can be switched between active and non-active, do you maintain a table of records that document this history?

[WickidWe]

the form is unbound
the purpose is to add new employees to "tblEmpDetails"
and when an existing employee is selected other combo boxes (previously unmentioned) from cboSearchName
are populated where they can be changed then when the "edit Button" is pressed the data is updated in the table.
currently to set an employee to Active "False" i have it as an "on Click" in vba on a button.
I want to control from the checkbox rather than a button.

here is the current VBA i am using for the Remove Button "ON Click" (remove button simply sets the Active Field to "False")
could I use a similar thing on the checkbox?

Private Sub cmdRemove_Click()
If Not (Me.listEmpDetails.Recordset.EOF And Me.listEmpDetails.Recordset.BOF) Then
     If MsgBox("Are You Sure You Want Set This Person to Inactive?", vbYesNo) = vbYes Then
CurrentDb.Execute "UPDATE tblEmpDetails " & _
    "SET Active=False " & _
"WHERE EmpID='" & Me.cboSearchName.Column(0) & "'"
        'refresh list
       Me.listEmpDetails.Requery
        'Clear Form
        cmdClear_Click
    End If
End If
End Sub

[June7]

Why are you using unbound form for data entry?

Yes, similar code as I already showed.

[WickidWe]

I am using an unbound form as I dont want the data added or updated unless a button is pressed.
but in saying that I would love the chkbox to be bound
is that possible?

[June7]

Only if the form is also bound to data (has RecordSource). Then you have to make sure form is on the record to edit. I would not mix the code you have for saving new record with a bound form. Go one way or the other. Blending probably possible but tricky.

[WickidWe]

I put

Me.chkActive = DLookup("Active", "tblEmpDetails", "EmpID='" & Me.cboSearchName)

into my cbobox after update and I am recieving the following error