Need help with this code to set the List Row Source

T

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. I used an identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly above. But
I can't get it to work

I even tried this for the string selection and assigned the list row source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only that
item, bu the second one returns a blank list. It was giving me an error of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
 
A

Alex Dybenko

Try to enter product name like:
Like 'T*'

and then SQL expression as:
strSelected = "Select * from Products where productid= " &
Me![Combo24].Column(1)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com



tony Jacobs said:
Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help. I used an identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly above.
But
I can't get it to work

I even tried this for the string selection and assigned the list row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an error of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
 
D

Douglas J. Steele

Slight correction. If the second column of the combo box is going to return
the string

Like 'T*'

then the SQL should be

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

(i.e.: you should NOT have an equal sign in there)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Alex Dybenko said:
Try to enter product name like:
Like 'T*'

and then SQL expression as:
strSelected = "Select * from Products where productid= " &
Me![Combo24].Column(1)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com



tony Jacobs said:
Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help. I used an
identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly above.
But
I can't get it to work

I even tried this for the string selection and assigned the list row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an error
of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
Click to expand...
 
P

pietlinden

Slight correction. If the second column of the combo box is going to return
the string

Like 'T*'

then the SQL should be

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

(i.e.: you should NOT have an equal sign in there)
Click to expand...

shouldn't it be...

strSelected = "Select * from Products where productid LIKE '" & _
Me![Combo24].Column(1) & "*'"
Click to expand...
 
D

Douglas J. Steele

Alex's reponse implied that the second column of the combo box actually
contained

Like 'T*'

If that's the case, then my solution is correct.

If all it contains is

T

then your solution is correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Slight correction. If the second column of the combo box is going to
return
the string

Like 'T*'

then the SQL should be

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

(i.e.: you should NOT have an equal sign in there)
Click to expand...

shouldn't it be...

strSelected = "Select * from Products where productid LIKE '" & _
Me![Combo24].Column(1) & "*'"
Click to expand...
 
T

tony Jacobs

Doug;

I am about to wash your car for you. We are close. Like '*' works with the
below SQL however Like 'C*' does not or Like 'B*'

Like '*' it shows all records.

P.S: Should I Set the rowsource to "" before the code executes.




Douglas J. Steele said:
Slight correction. If the second column of the combo box is going to return
the string

Like 'T*'

then the SQL should be

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

(i.e.: you should NOT have an equal sign in there)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Alex Dybenko said:
Try to enter product name like:
Like 'T*'

and then SQL expression as:
strSelected = "Select * from Products where productid= " &
Me![Combo24].Column(1)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com



tony Jacobs said:
Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help. I used an
identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly above.
But
I can't get it to work

I even tried this for the string selection and assigned the list row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an error
of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
put a stop at:
Me!lstInstrIssue.RowSource = strSelected
then run in immediate window:
?strSelected

then paste resulted SQL into new query sql view and try to run it, access
will tell you what is wrong

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tony Jacobs said:
Doug;

I am about to wash your car for you. We are close. Like '*' works with the
below SQL however Like 'C*' does not or Like 'B*'

Like '*' it shows all records.

P.S: Should I Set the rowsource to "" before the code executes.




Douglas J. Steele said:
Slight correction. If the second column of the combo box is going to return
the string

Like 'T*'

then the SQL should be

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

(i.e.: you should NOT have an equal sign in there)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Alex Dybenko said:
Try to enter product name like:
Like 'T*'

and then SQL expression as:
strSelected = "Select * from Products where productid= " &
Me![Combo24].Column(1)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help. I used an
identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly above.
But
I can't get it to work

I even tried this for the string selection and assigned the list row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an
error
of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

Alex;

Thanks for Following up. I got it working. I fell into the spell of looking
at code for a long time where I wasn't seeing our errors.

The varaible we were using was ProductName.. LOL.. not ProductId. The first
combo box independently looks for that .

That took care of it and I had to use [ProductName] = &
Me![Combo28].Column(1)

Single quotes are the answer, as I explored the other cirteria with double
quote I get no answer.

Question:

How do I trap an empty dynaset i.e: if the combo box selection returns no
rows to the list. I wanted to pop up a message box

I used this: But the message box is not coming up:

If Len(strSelected) <= 0 Then

MsgBox " There are no matching Records", vbExclamation, "My Database"

Else
Me!lstInstrIssue.RowSource = strSelected
End If


Alex Dybenko said:
Hi,
put a stop at:
Me!lstInstrIssue.RowSource = strSelected
then run in immediate window:
?strSelected

then paste resulted SQL into new query sql view and try to run it, access
will tell you what is wrong

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tony Jacobs said:
Doug;

I am about to wash your car for you. We are close. Like '*' works with the
below SQL however Like 'C*' does not or Like 'B*'

Like '*' it shows all records.

P.S: Should I Set the rowsource to "" before the code executes.




Douglas J. Steele said:
Slight correction. If the second column of the combo box is going to return
the string

Like 'T*'

then the SQL should be

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

(i.e.: you should NOT have an equal sign in there)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Try to enter product name like:
Like 'T*'

and then SQL expression as:
strSelected = "Select * from Products where productid= " &
Me![Combo24].Column(1)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




Hello ; I am using the After update event code below, but the list
Click to expand...
is
not
getting populated. What am I doing wrong. Please help. I used an
identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly above.
But
I can't get it to work

I even tried this for the string selection and assigned the list row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an
error
of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
you can either check combobox ListCount property after setting
Me!lstInstrIssue.RowSource = strSelected

or you can open recordset on strSelected and check it recordcount

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tony Jacobs said:
Alex;

Thanks for Following up. I got it working. I fell into the spell of
looking
at code for a long time where I wasn't seeing our errors.

The varaible we were using was ProductName.. LOL.. not ProductId. The
first
combo box independently looks for that .

That took care of it and I had to use [ProductName] = &
Me![Combo28].Column(1)

Single quotes are the answer, as I explored the other cirteria with double
quote I get no answer.

Question:

How do I trap an empty dynaset i.e: if the combo box selection returns no
rows to the list. I wanted to pop up a message box

I used this: But the message box is not coming up:

If Len(strSelected) <= 0 Then

MsgBox " There are no matching Records", vbExclamation, "My Database"

Else
Me!lstInstrIssue.RowSource = strSelected
End If


Alex Dybenko said:
Hi,
put a stop at:
Me!lstInstrIssue.RowSource = strSelected
then run in immediate window:
?strSelected

then paste resulted SQL into new query sql view and try to run it, access
will tell you what is wrong

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

tony Jacobs said:
Doug;

I am about to wash your car for you. We are close. Like '*' works with the
below SQL however Like 'C*' does not or Like 'B*'

Like '*' it shows all records.

P.S: Should I Set the rowsource to "" before the code executes.




message
Slight correction. If the second column of the combo box is going to
return
the string

Like 'T*'

then the SQL should be

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

(i.e.: you should NOT have an equal sign in there)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Try to enter product name like:
Like 'T*'

and then SQL expression as:
strSelected = "Select * from Products where productid= " &
Me![Combo24].Column(1)

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




Hello ; I am using the After update event code below, but the list is
not
getting populated. What am I doing wrong. Please help. I used an
identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName
=
what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly
above.
But
I can't get it to work

I even tried this for the string selection and assigned the list
row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an
error
of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " &
"Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
Click to expand...
Click to expand...
 
M

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

tony Jacobs said:
Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help. I used an identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly above.
But
I can't get it to work

I even tried this for the string selection and assigned the list row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an error of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
 
S

strive4peace

Hi Margaret,

sorry to say, but you ARE spamming -- and you are mis-using these
groups. These newsgroups are for getting help with usage and coding
issues, not for advertising products.

Warm Regards,
Crystal

*
:) have an awesome day :)
*



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

tony Jacobs said:
Hello ; I am using the After update event code below, but the list is not
getting populated. What am I doing wrong. Please help. I used an
identical
thing for the first combo box. It works like a charm. Wanted to limit
/filter list box based on a criteria selection Example ProductName = what
ever the list box displays. The Coulmn (0) is ProductID, column(1) is
column
Name

I created a table
ProductId ProductName Desc
1 Like"C*" All Candy
2 Like "T*" All Tobaco

I want the user to select the Criteria from the drop list dispaly
above. But
I can't get it to work

I even tried this for the string selection and assigned the list row
source
to it after update , but still no results:

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


The first one displays perfectly when selected and the list shows only
that
item, bu the second one returns a blank list. It was giving me an
error of
type missmatch. but not any more.


Thanks




This is my First Attempt
+++++++++++++++++++++++++++++++++++++++++++++++++
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
+++++++++++++++++++++++++++++++++++++++++++++++++++++=== First Try.

The list comes out empty it will not show records.

Here is another Try:

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 [productid] = " &
Me![Combo24].Column(0)
' strSelected = "Select * from Products (((where [ProductName] =
Me![Combo28].Column(1))))"
'strSelected = "Select * from Products ((( WHERE
((([Products].[ProductName])=[Forms]![Products]![Combo28])) )))"
'DoCmd.ApplyFilter "[ProductName] = " & "Me![Combo28].Column(1)"""
strSelected = "SELECT Products.ProductID, Products.ProductName,
Products.ProductDescription, Products.SerialNumber FROM Products WHERE
(((Products.ProductName)=[Forms]![Products]![Combo28]))"
' The above line is one long line by the way

' DoCmd.ApplyFilter "criteriaselect"

Me!lstInstrIssue.RowSource = strSelected
lstInstrIssue.Requery



End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List is not getting populated by the filter from Combo Box. 10
Like "MU*" OR Like "MS*" 14
List box can't handle large stirngs for a filter. 3
Combine Fields ( Need to Adjust Code ) 3
Databinding to a DAL with ObjectDataSource-control: Update OK, DeleteNot OK ??? 5
Is this code a reasonable way to handle exception in a WebService 12
still cannot fill in other field from a dropdown list 5
webservice and using some kind of response to the client 3

Top