List is not getting populated by the filter from Combo Box.

[tony Jacobs]

Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help

Thanks


Private Sub Combo28_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Dim varItem As Variant
Dim strSelected As String

' Set rs = Me.Recordset.Clone
' rs.FindFirst "[ProductName] = " & Str(Me![Combo28].Column(1))
' Me.Bookmark = rs.Bookmark
'lstInstrIssue.RowSourceType = string

' strSelected = "Select * from Products where [ProductName] = " &
Me![Combo28].Column(1)
strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"

Me!lstInstrIssue.RowSource = strSelected



lstInstrIssue.Requery


End Sub

 

[Larry Linson]

Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help

What column do you think you are referencing with Column(1)? It is the
second column -- not always clear as references to columns in the Properties
are one-based, but in code, references are zero-based. As we don't know
details of what your data is and how it is laid out, that might or might not
be the problem.

Once you do get it right, if Product Name is a text field, then the value
will have to be in quotes, so the following might be appropriate. If the
value in Column(1) of Combo28 were "Wheel" then strSelected should contain
the string that follows the statement

strSelected = "SELECT * FROM Products WHERE [ProductName] = """ &
Me![Combo28].Column(1) & """"

SELECT * FROM Products WHERE [ProductName] = "Wheel"

You know you can put stops in your code, and examine the contents of
variables in the Locals or the Immediate Window... that helps diagnose and
solve a lot of problems.

Larry Linson
Microsoft Office Access MVP

 

[Marshall Barton]

Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong.


Private Sub Combo28_AfterUpdate()
Dim strSelected As String

strSelected = "Select * from Products (((where [ProductName] = Me![Combo28].Column(1))))"

Me!lstInstrIssue.RowSource = strSelected

lstInstrIssue.Requery
End Sub

If ProductName is a numeric type field, try this:

strSelected = "Select * From Products " & _
"Where ProductName=" & Me!Combo28.Column(1)

If it's a Text field:

strSelected = "Select * From Products """ & _
"Where ProductName=" & Me!Combo28.Column(1) & """"


The Requery is redundant and a waste of time because setting
row source automatically reloads the list.

 

[Tony Jacobs]

Thanks Larry;

I tried posting a 375K snapshot of the form, but the server kicked it back.
I tried many iterations, and yes I am positive it is the second column
meaning column(1) (per sequence). My goal is to make the combobox selection
become the value or the criteria that shows the records in the listbox.

I have it working perfectly when the ProductId = the value of the combobox.

I would like to have the values in the combo box be Like "C*" , Like "B*" ;
( Similar to what I would put in as the criteria pane in the query field in
design mode ) to return all product types that start with a C (More than one
record; i.e all possible matches) and so forth, then after update
productname = "select * from some table where Product name = second column
in combo box as you stated below becomes the list row source string

I'll add the quotes and will let you know.

Thank you again

Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help

What column do you think you are referencing with Column(1)? It is the
second column -- not always clear as references to columns in the
Properties are one-based, but in code, references are zero-based. As we
don't know details of what your data is and how it is laid out, that might
or might not be the problem.

Once you do get it right, if Product Name is a text field, then the value
will have to be in quotes, so the following might be appropriate. If the
value in Column(1) of Combo28 were "Wheel" then strSelected should contain
the string that follows the statement

strSelected = "SELECT * FROM Products WHERE [ProductName] = """ &
Me![Combo28].Column(1) & """"

SELECT * FROM Products WHERE [ProductName] = "Wheel"

You know you can put stops in your code, and examine the contents of
variables in the Locals or the Immediate Window... that helps diagnose and
solve a lot of problems.

Larry Linson
Microsoft Office Access MVP

 

[Tony Jacobs]

Thanks Marsh! I'll Double check that, I may have missed a quote somewhere. I
see this on most answers, I was thinking the same thing it is redundant, but
I was making sure it refreshes. Thanks for your suggestion.

Please see my answer to Larry above; I would appreciate any suggestion on
how to make the combo box a criteria source to retrieve records with the
LIKE statement

Regards;

Tony

Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong.


Private Sub Combo28_AfterUpdate()
Dim strSelected As String

strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"

Me!lstInstrIssue.RowSource = strSelected

lstInstrIssue.Requery
End Sub

If ProductName is a numeric type field, try this:

strSelected = "Select * From Products " & _
"Where ProductName=" & Me!Combo28.Column(1)

If it's a Text field:

strSelected = "Select * From Products """ & _
"Where ProductName=" & Me!Combo28.Column(1) & """"


The Requery is redundant and a waste of time because setting
row source automatically reloads the list.

 

[Marshall Barton]

Thanks Marsh! I'll Double check that, I may have missed a quote somewhere. I
see this on most answers, I was thinking the same thing it is redundant, but
I was making sure it refreshes.

Ahh, I see what you are doing now. As long as the table
field contains:
Like "A*"
including the quotes, then getting rid of the = sign as
Larry said should take care of it.

 

[tony Jacobs]

Sorry; It did not work;

This is the table behind my combo box it is called : criteria

Product ID Product Name Product Description
1 Like "C*" All Chocolates
2 Like "P*" Spices
3 Like "B*" Grain
4 Like "*" ALL


I want the product name to be equal to Like "C*" and return all items that
has a product name starting with a C to display in the listbox in the form.

the products table is : Products

Product ID Product Name Product Description Serial Number
1 Chocolate Sweets 12345111
2 Milk Dairy 234511456
3 Bread Grain 567891233
4 Cinamomn Spices 9898765
5 Pepper Spices S56894
6 Bananas Fruit 14567899
7 Cigarettes Tobaco 2345678


I am selecting from products table where product name = Like "C*" because I
want to retreive multiple records in the list all possiblities. Other wise I
would say where Productname ="Chocolate".

I can not get it to work. the value of Product name is a string, it works
well is the value is a number. I have adjusted the syntax to include the "'s
for a string and without for a number. But no change

Thanks Larry;

I tried posting a 375K snapshot of the form, but the server kicked it back.
I tried many iterations, and yes I am positive it is the second column
meaning column(1) (per sequence). My goal is to make the combobox selection
become the value or the criteria that shows the records in the listbox.

I have it working perfectly when the ProductId = the value of the combobox.

I would like to have the values in the combo box be Like "C*" , Like "B*" ;
( Similar to what I would put in as the criteria pane in the query field in
design mode ) to return all product types that start with a C (More than one
record; i.e all possible matches) and so forth, then after update
productname = "select * from some table where Product name = second column
in combo box as you stated below becomes the list row source string

I'll add the quotes and will let you know.

Thank you again

Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help

What column do you think you are referencing with Column(1)? It is the
second column -- not always clear as references to columns in the
Properties are one-based, but in code, references are zero-based. As we
don't know details of what your data is and how it is laid out, that might
or might not be the problem.

Once you do get it right, if Product Name is a text field, then the value
will have to be in quotes, so the following might be appropriate. If the
value in Column(1) of Combo28 were "Wheel" then strSelected should contain
the string that follows the statement

strSelected = "SELECT * FROM Products WHERE [ProductName] = """ &
Me![Combo28].Column(1) & """"

SELECT * FROM Products WHERE [ProductName] = "Wheel"

You know you can put stops in your code, and examine the contents of
variables in the Locals or the Immediate Window... that helps diagnose and
solve a lot of problems.

Larry Linson
Microsoft Office Access MVP

 

[tony Jacobs]

I tried all of these also but no Luck:

'New Solution as of June 24th 2008

'strSelected = "Select * from Products where productid= '" &
Me![Combo24].Column(1) & "'"


' strSelected = "Select * From Products """ & "Where ProductName= " &
Me!Combo28.Column(1) & """"

strSelected = "SELECT * FROM Products WHERE [ProductName] = """ &
Me![Combo28].Column(1) & """"


'strSelected = "Select * From Products """ & "Where ProductName=" &
Me!Combo28.Column(1) & """"

'strSelected = "SELECT * FROM Products WHERE [ProductName] =" &
"Chocolate"

This last statement comes back with a prameter query box asking me for a
value for chocolate. If I enter it , then I get the chocolate record in the
List, but not from the Dropdown list (Combobox)

Me!lstInstrIssue.RowSource = strSelected

At this point; I'll wash your car ......

Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help

What column do you think you are referencing with Column(1)? It is the
second column -- not always clear as references to columns in the Properties
are one-based, but in code, references are zero-based. As we don't know
details of what your data is and how it is laid out, that might or might not
be the problem.

Once you do get it right, if Product Name is a text field, then the value
will have to be in quotes, so the following might be appropriate. If the
value in Column(1) of Combo28 were "Wheel" then strSelected should contain
the string that follows the statement

strSelected = "SELECT * FROM Products WHERE [ProductName] = """ &
Me![Combo28].Column(1) & """"

SELECT * FROM Products WHERE [ProductName] = "Wheel"

You know you can put stops in your code, and examine the contents of
variables in the Locals or the Immediate Window... that helps diagnose and
solve a lot of problems.

Larry Linson
Microsoft Office Access MVP

 

[tony Jacobs]

Marshall;
I am not sure I understand! which = sign?

 

[Marshall Barton]

the one after ProductName. Try using this:

strSelected = "SELECT * FROM Products " _
& "WHERE ProductName " & Me!Combo28.Column(1)

 

[Margaret]

Please visit [email protected]/blog
I am starting up a business and need new customers. Any help that you could
give me, I would really appreciate it. Im not spaming anyone, so please let
me know if there are anyone you know who lives in the london area and would
be interested in the purchase of medical uniforms.

Im trying to get as many customers as i can. i need help tho. Please pass
this email on to whom ever you know who would be interested. I would really
appreciate it.

Here is a little about it:

We are a business that makes Medical Uniforms which are made for Nurse's,
Doctors, and any Medical team that there is out there like PSW's.

You can find more out about it at: [email protected]/blog

We have flexible plans to accommodate growth. All size's, styles and colors
that you can think of

We are located in London, Ontario

Thank you for your time.

Margaret