Synchronizing Two List Boxes problem - Access 2003

[suslikovich]

Hi everyone,

I have a problem synchronizing two list boxes on a form. I want to
display information in the second box based on the selection in the
first box. First box (List0)lists all company names from table that
lists quotes. I diplay a company only once even if it is listed more
that one time. The second box (List4)should display quotes for a
selected company (one or more.) Here is what I have for AfterUpdate in
the first box:

Private Sub List0_AfterUpdate()
Me.List4.RowSource = """SELECT QuoteId FROM" & _
" Quote WHERE AccountIdName = " & Me.AccountIdName & """ ORDER
BY QuoteId"
Me.List4 = Me.List4.ItemData(0)
End Sub

I use double quets here because I read here on google that if my field
is not numeric or bulean I need to put qotes around it. Without qotes I
get the same error message as with. When I run it I get the following:

Syntax Error (Missing Operator) in Query Expression 'AccountIdName =
Advanced.....' (the name of one of our clients in listed.) Any idea
what is missing?

Thanks,

Stan

[SilvrT]

change this statement (" Quote WHERE AccountIdName = " &
Me.AccountIdName & """ ORDER )

- TO -

" Quote WHERE AccountIdName = ' " & Me.AccountIdName & " ' " ORDER

NOTE: where I put the single quotes

[suslikovich]

What about the double quotes in the begfining? Please, include the
whole block in your answer. Id dousn't work again:

Private Sub List0_AfterUpdate()
Me.List4.RowSource = ""SELECT QuoteId FROM" & _
" Quote WHERE AccountIdName = '" & Me.AccountIdName & "'" ORDER
BY QuoteId"
Me.List4 = Me.List4.ItemData(0)
End Sub

Stan

[Randy Harris]

<su*********@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hi everyone,

I have a problem synchronizing two list boxes on a form. I want to
display information in the second box based on the selection in the
first box. First box (List0)lists all company names from table that
lists quotes. I diplay a company only once even if it is listed more
that one time. The second box (List4)should display quotes for a
selected company (one or more.) Here is what I have for AfterUpdate in
the first box:

Private Sub List0_AfterUpdate()
Me.List4.RowSource = """SELECT QuoteId FROM" & _
" Quote WHERE AccountIdName = " & Me.AccountIdName & """ ORDER
BY QuoteId"
Me.List4 = Me.List4.ItemData(0)
End Sub

I use double quets here because I read here on google that if my field
is not numeric or bulean I need to put qotes around it. Without qotes I
get the same error message as with. When I run it I get the following:

Syntax Error (Missing Operator) in Query Expression 'AccountIdName =
Advanced.....' (the name of one of our clients in listed.) Any idea
what is missing?

Thanks,

Stan

Stan, you're pretty darned close.

Private Sub List0_AfterUpdate()
Me.List4.RowSource = "SELECT QuoteId FROM Quote " & _
"WHERE AccountIdName = '" & Me.AccountIdName & "' " & _
" ORDER BY QuoteId"
Me.List4 = Me.List4.ItemData(0)
End Sub
Here I've used single quotes (I'll show the double quotes below). Note that
the quotes are only around your WHERE clause argument (Me.AccountIDName).

If you prefer to use double quotes, they need to be doubled, so that the
compiler understands that you mean for them to be "literal" quotes, not
simply to contain a string value.

Private Sub List0_AfterUpdate()
Me.List4.RowSource = "SELECT QuoteId FROM Quote " & _
"WHERE AccountIdName = """ & Me.AccountIdName & """ " & _
" ORDER BY QuoteId"
Me.List4 = Me.List4.ItemData(0)
End Sub

Hope this helps,
Randy

[suslikovich]

Hi Randy,
I used your suggestion and I have my second box populated but it only
show very first quote regardless the selection in the first box.

Stan

[Randy Harris]

<su*********@yahoo.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Hi Randy,
I used your suggestion and I have my second box populated but it only
show very first quote regardless the selection in the first box.

Stan

You probably need to requery the ListBox

Private Sub List0_AfterUpdate()
Me.List4.RowSource = "SELECT QuoteId FROM Quote " & _
"WHERE AccountIdName = '" & Me.AccountIdName & "' " & _
" ORDER BY QuoteId"
Me.List4.Requery
End Sub