Synchronizing Two List Boxes problem - Access 2003

[suslikovich@yahoo.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_AfterUpda te()
Me.List4.RowSou rce = """SELECT QuoteId FROM" & _
" Quote WHERE AccountIdName = " & Me.AccountIdNam e & """ ORDER
BY QuoteId"
Me.List4 = Me.List4.ItemDa ta(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]

Re: Synchronizing Two List Boxes problem - Access 2003

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

- TO -

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

NOTE: where I put the single quotes

[suslikovich@yahoo.com]

Re: Synchronizing Two List Boxes problem - Access 2003

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

Private Sub List0_AfterUpda te()
Me.List4.RowSou rce = ""SELECT QuoteId FROM" & _
" Quote WHERE AccountIdName = '" & Me.AccountIdNam e & "'" ORDER
BY QuoteId"
Me.List4 = Me.List4.ItemDa ta(0)
End Sub

Stan

[Randy Harris]

Re: Synchronizing Two List Boxes problem - Access 2003


<suslikovich@ya hoo.com> wrote in message
news:1120852462 .658887.237730@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> 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_AfterUpda te()
> Me.List4.RowSou rce = """SELECT QuoteId FROM" & _
> " Quote WHERE AccountIdName = " & Me.AccountIdNam e & """ ORDER
> BY QuoteId"
> Me.List4 = Me.List4.ItemDa ta(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[/color]

Stan, you're pretty darned close.

Private Sub List0_AfterUpda te()
Me.List4.RowSou rce = "SELECT QuoteId FROM Quote " & _
"WHERE AccountIdName = '" & Me.AccountIdNam e & "' " & _
" ORDER BY QuoteId"
Me.List4 = Me.List4.ItemDa ta(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.AccountIDNa me).

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_AfterUpda te()
Me.List4.RowSou rce = "SELECT QuoteId FROM Quote " & _
"WHERE AccountIdName = """ & Me.AccountIdNam e & """ " & _
" ORDER BY QuoteId"
Me.List4 = Me.List4.ItemDa ta(0)
End Sub

Hope this helps,
Randy

[suslikovich@yahoo.com]

Re: Synchronizing Two List Boxes problem - Access 2003

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]

Re: Synchronizing Two List Boxes problem - Access 2003


<suslikovich@ya hoo.com> wrote in message
news:1120863165 .635976.80330@g 47g2000cwa.goog legroups.com...[color=blue]
> 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
>[/color]

You probably need to requery the ListBox

Private Sub List0_AfterUpda te()
Me.List4.RowSou rce = "SELECT QuoteId FROM Quote " & _
"WHERE AccountIdName = '" & Me.AccountIdNam e & "' " & _
" ORDER BY QuoteId"
Me.List4.Requer y
End Sub