Using the listbox item selection

[George]

Good Day All,



I have a listbox "List2" on a form "CustomerList". I activate this form by a button "Button1". Button1 starts a macro which loads the form "CustomerList". I want a use a where condition to open the form with only those items associated with the name selected in the list. I am opening a form "Invoice Payments". "Invoice Payments" data source contains a table "Customers" with a field "Company"


My approach is to use this code:


[Customers]![Company] =
[List2]


Can anyone assist in this solution

[pbaldy]

This will work if the listbox is single select:

http://www.baldyweb.com/wherecondition.htm

[George]

Thanks. I will try it. But of course it can't be used in a macro?

[pbaldy]

Yes, there's a where condition argument for the OpenForm macro action. The syntax is a little different, so you may need to play with it.

[George]

Ok I tried with I cannot find the code to make it work, so I am back to coding.

This is my code but it has a hiccup. can you please comment on it. The error message suggest there is a problem with the syntax in where condition


Selection = Me.List2.Column(1, Me.List2.ListIndex)

Condition = "forms![company]" & " = " & Selection

DoCmd.OpenForm "Invoice Payments", acNormal, , Condition
End Sub][/CODE]

[George]

Ok I tried with I cannot find the code to make it work, so I am back to coding.

This is my code but it has a hiccup. can you please comment on it. The error message suggest there is a problem with the syntax in where condition

Selection = Me.List2.Column(1, Me.List2.ListIndex)

Condition = "forms![company]" & " = " & Selection

DoCmd.OpenForm "Invoice Payments", acNormal, , Condition
 End Sub

[pbaldy]

Did you look at the link? The first part is the name of the field to be filtered on, not an incomplete form reference. Presuming the first bit returns the correct reference:

Condition = "FieldName = " & Selection

[George]

I did check the link yesterday and used the 1st option like you just gave me; however the value of selection is string type. I therefore had to use option 2 on the link and it is working just as desired. Thank you so much.

[pbaldy]

Happy to help!

[George]

Your Welcome pbaldy.

This morning I am have a little problem with the related code. With this code instead of loading the requested report, a print is sent to the printer. Can you please assess what is wrong?

Private Sub List9_Click()
selection = Me.List9.Column(2, Me.List9.ListIndex)
Condition = "[Order ID]  = " & selection
 
DoCmd.OpenReport "Quotation", acNormal, , Condition

End Sub

[pbaldy]

Check the available arguments for OpenReport. acNormal prints it out.

[George]

Thanks plandy. it works

[pbaldy]

No problem.

[George]

Dear All,d

As stated before my original problem has been resolved. However a problem has developed. I did not post a new query because this problem arises out of that original problem.

Here's the situation: Although the required records are being generated there are 2 records that are prompting an error message. What I realize that is common to these two records is that they both have apostrophes; i.e: Calypso C'bbean Resort. so I conclude that these have to be handled differently. Can someone explain how these are to be treated?
Here is my code that works fine for non-apostrophes strings:

CODE]
Private Sub List2_Click()

selection = Me.List2.Column(1, Me.List2.ListIndex)
Selection2 = Me.List2.Column(2, Me.List2.ListIndex)
Condition = "company = '" & selection & "'" & "AND" & "[Order ID] =" & Selection2

DoCmd.OpenForm "Invoice Payments", acNormal, , Condition
End Sub
[/CODE]

[pbaldy]

Here's a nice tutorial:

http://www.baldyweb.com/BuildSQL.htm

Note the use of Chr(34).