Link to home
Start Free TrialLog in
Avatar of nlouder
nlouder

asked on

vba query using multi field listbox

I have a access form with two list boxes called Available and Chosen the Available list box is populated with 3 fields (Agcno (number), Agency(text) & State(text) from the table TBLAgency.  When a record is selected from the Available listbox and added to the Chosen listbox I would like to update a field called Flag1 for the selected record in my table with a "false" value.  I have used the following vba code which works fine in another database using a single field listbox but is erroring out on the query using the 3 fields listbox.

Private Sub Add_Click()
   
    If Available.ItemsSelected.Count = 1 Then
        CurrentDb.Execute ("UPDATE TBLAgency SET TBLAgency.FLAG1 = False " & _
        "WHERE TBLAgency.AGCNO AND TBLAgency.AGENCY AND TBLAgency.CITY ='" & Available & "';")
        Available.Requery
        Chosen.Requery
                   
    End If
End Sub

Any ideas or solutions?
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi nlouder,

Is this complete

>       "WHERE TBLAgency.AGCNO AND TBLAgency.AGENCY AND TBLAgency.CITY
> ='" & Available & "';")

This is the same as:

      "WHERE TBLAgency.AGCNO = true AND TBLAgency.AGENCY = true AND TBLAgency.CITY ='" & Available & "';")

Is that what you mean?

Pete
ASKER CERTIFIED SOLUTION
Avatar of Leigh Purvis
Leigh Purvis
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nlouder
nlouder

ASKER

LPurvis:

It is City I'm trying to match on sorry for the error.  You're solution works great.
try:

Private Sub Add_Click()
   
    If Available.ItemsSelected.Count = 1 Then
        CurrentDb.Execute ("UPDATE TBLAgency SET TBLAgency.FLAG1 = False " & _
        "WHERE TBLAgency.AGCNO=" & Available.column(0) & ", AND TBLAgency.AGENCY ='" & Available.column(1) & ", AND TBLAgency.CITY ='" &   Available.column(2) & "';")
        Available.Requery
        Chosen.Requery
                   
    End If
End Sub

Mike