Update Checkbox Value In Table Based On Combo Box Selection

[Twinnie]

I have a combobox with staff names and for each selected staff name, the second combo box should not display the staff name that has been selected.


To achieve this, I put a checkbox inside the table that contains the staff details and I wrote this update query.

UPDATE Staff_Details SET Staff_Details.YesNo = -1
WHERE (((Staff_Details.FullName)=[Forms]![AccountabilityForm]![cboStaffName]));


The staff table is Staff_Details
The Field on the table to be checked depending on whether the staff has been selected or not is YesNo.
The form name is AccountabilityForm and the combobox name is cboStaffName.
I want the corresponding checkbox for the selected staff on the combobox to be ticked immediately the staff is selected and I want to write a select query that will only display staff names without an Active checkbox for the second combo selection.
How can I achieve this?

[June7]

Really should use ID field in criteria instead of name. The ID field should be the first column of the combobox and hidden. Should be saving ID instead of name into the data table. Names make very poor unique identifiers/key fields.

Combobox RowSource:

SELECT StaffID, FullName FROM Staff_Details WHERE YesNo = False;

I use only VBA, not macros. Code to set the checkbox field and requery the combobox:

CurrentDb.Execute "UPDATE Staff_Details SET YesNo = True WHERE StaffID=" & Me.cboStaffName
Me.cboStaffName.Requery

[burrina]

Try this for your SQL statement.

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Staff_Details SET Staff_Details.YesNo = True " & _
"WHERE (((Staff_Details.FullName)='" & Me.cboStaffName.Value & "'));"
DoCmd.SetWarnings True

For your query just enter True for your YesNo criteria.

HTH

Didn't see your post June!