Word Merge

[Steve]

Hi,
Am using the word merge program from Albert Kallal and it works great except
for one thing.
I put the command button in a subform and is works fine while I am testing
it and only have the subform open. If I have the form and the subform open
it tries to pull the merge data from the form instead of the subform. What
can I do to make this work?
Thanks.

 

[Albert D. Kallal]

In this case, you simply have to specify the SQL that you want for the
merge.

use MergeAllWord in place of MergeSingleWord.

Just go:

dim strSql as sting

me.Refresh

strSql = "select * from tableThatisForSubForm where id = " & me!id

MergeAllWord strSql


In the above example I assumed that "id" is the primary key of a record in
the sub form, but just change ID to whatever your primary key is.

The above shows that you're not restricted to the current form for creating
data that gets merged into word. if you remove the where clause and
restrictions, then of course it would merge all records from that table....

 

[Steve]

Thanks for the quick reply:

The sql statement I used is:

Dim strSql As String

Me.Refresh

strSql = "select * from tblRequest where id = " & Me!RequestID

MergeAllWord strSql

The error message came back:
No data was created for this merge
Make sure the sql is correct
Sql was
Select * from TblRequest where id = 10896

 

[Albert D. Kallal]

Well, is the field in tblRequest calld "id", or RequestID?

From what you have, you likey want

strSql = "select * from tblRequest where RequestID = " & Me!RequestID


The above assume that RequestID is a number type field.

If it is a string type field, then

strSql = "select * from tblRequest where RequestID = '" & Me!RequestID & "'"

 

[Steve]

Thanks for your help. I'm glad you folks who have the know-how are here as
resource for those of us who don't.
Steve

 

[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

 

[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

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

 

[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!)

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

 

[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)

shouldn't it be...

strSelected = "Select * from Products where productid LIKE '" & _

Me![Combo24].Column(1) & "*'"

 

[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)

shouldn't it be...

strSelected = "Select * from Products where productid LIKE '" & _

Me![Combo24].Column(1) & "*'"

 

[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.

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

 

[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

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.

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

 

[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

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

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.

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

 

[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

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

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

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

 

[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

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

 

[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

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