Solved Update SQL not working

[Momma]

Hi there
I have a form with two listboxes plus a subform. What I'm trying to achieve is to update the CouncilID field in the tblLocalities to the CouncilID selected in the subform.
Because there are a few thousand records in the first table I use a SearchAsYouType Listbox to find the Locality. I then select the record and move it to the second listbox with the Select Command button. I then select the record or records listed in the second listbox to update the CouncilID field for those records. I have a unbounded text box called txtCouncil on the main form.
What I don't understand is the following. The two command buttons, Select and Unselect, both have the same SQL statement but it only works for the Select button. Nothing happens when I click the Unselect button.
Also, the Update command button has two SQL statements, one to update the CouncilID and the other one to change the IsSelected field back to false.
On this one, only the IsSelected field is updated, not the CouncilID.
I've tried it so many different ways, but just can't get the CoulcilID update to work.
I do get an error when I open the form though, which is "3061 Too few parameters. Expected 1" which could be the reason. I just can't see what's causing this.
Please, if one of you friendly people could help me overcome this
Thank you

Private Sub CmdSelect_Click()
    If IsNull(ComList1) Then Exit Sub

    DoCmd.RunSQL "UPDATE tblLocalities SET IsSelected=TRUE " & _
        " WHERE LocalityID=" & ComList1
        
    RequeryListBoxes
End Sub

Private Sub CmdUnselect_Click()

If IsNull(ComList2) Then Exit Sub

    DoCmd.RunSQL "UPDATE tblLocalities SET IsSelected=FALSE " & _
        " WHERE LocalityID=" & ComList2
    
    RequeryListBoxes
End Sub

Private Sub CmdUpdate_Click()

    Dim strSQL As String
    Dim TxtCouncil As Long
    
    strSQL = "UPDATE tblLocalities SET CouncilID = " & Me!TxtCouncil & " WHERE IsSelected = True ;"
    
    strSQL = "UPDATE tblLocalities SET IsSelected=FALSE WHERE IsSelected=true "
    
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    RequeryListBoxes

End Sub

 

[June7]

Cause of the parameters error is RowSource for ComList1 has criteria referencing txtState. txtState has expression referencing combobox. Combobox does not have value when form opens.

Not seeing need for txtState.

The first UPDATE doesn't happen because you reset the strSQL variable. Don't need this variable.

CurrentDb.Execute "UPDATE tblLocalities SET CouncilID = " & Me!TxtCouncil & " WHERE IsSelected = True ;"

CurrentDb.Execute "UPDATE tblLocalities SET IsSelected=FALSE WHERE IsSelected=true "

The Unselect code doesn't work because ComList2 is set to allow multi-select. If you want to allow multi-select in these listboxes, code will have to be quite different. Requires looping through listbox SelectedItems.

Is this intended to be a split multi-user database? Multiple users setting value of IsSelected field could conflict.

 

[Momma]

Cause of the parameters error is RowSource for ComList1 has criteria referencing txtState. txtState has expression referencing combobox. Combobox does not have value when form opens.

Not seeing need for txtState.

The first UPDATE doesn't happen because you reset the strSQL variable. Don't need this variable.

CurrentDb.Execute "UPDATE tblLocalities SET CouncilID = " & Me!TxtCouncil & " WHERE IsSelected = True ;"

CurrentDb.Execute "UPDATE tblLocalities SET IsSelected=FALSE WHERE IsSelected=true "

The Unselect code doesn't work because ComList2 is set to allow multi-select. If you want to allow multi-select in these listboxes, code will have to be quite different. Requires looping through listbox SelectedItems.

Is this intended to be a split multi-user database? Multiple users setting value of IsSelected field could conflict.

Thank you for your help, June. It will only be one user working on this and it will only be used until all the localities are linked with a council.
I have done the changes as you suggested and it's all working except for the 3061 error which still comes up.
What would the code be for a multi-select listbox?

 

[June7]

Have to remove dynamic parameter from listbox RowSource to eliminate that error.

Here is example using multi-select listbox to build filter criteria http://allenbrowne.com/ser-50.html

 

[Momma]

Have to remove dynamic parameter from listbox RowSource to eliminate that error.

Here is example using multi-select listbox to build filter criteria http://allenbrowne.com/ser-50.html

I changed it as follows: [forms]![frmLocalitiesSelected]![StateCombo].