Multi Select List to a Report Dev Ashish

C

Cyndi

I found code on the net where I can have two list boxes and if I
choose an item in the first list it goes to the second, works
beautifully, I can make it print all or one stallion. What I need now
is to print two or more stallions.

It looks like the article on Web Access Use Multi Select Listbox to
limit records in reports is what I need but I can't make it work.

my 2nd list box(where the stallions are listed) is named lstchosen and
it is bound to StallionID and I have added a text box


When it gets to line Me!TxtCriteria it stops and says Runtime Error 5
Invalid procedure call or error


Can someone please help??

here is the code I have added to a command button on click event

Private Sub btnTestQuery_Click()
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!lstChosen.ItemsSelected
stWhat = stWhat & Me!lstChosen.ItemsSelected(vItm)
stWhat = stWhat & stCriteria
Next vItm
Me!TxtCriteria = CStr(Left$(stWhat, Len(stWhat) -
Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("WeeklyTotals FContractsQuery")
stSQL = "SELECT StallionID, "
stSQL = stSQL & "Title FROM Stallion WHERE StallionID"
stSQL = stSQL & " IN (" & Me!TxtCriteria & ")"
loqd.SQL = stSQL
loqd.Close
DoCmd.OpenQuery "qryMultiSelTest"
End Sub

All help appreciated
 
Y

Yousef Modaressi

Cyndi said:
I found code on the net where I can have two list boxes and if I
choose an item in the first list it goes to the second, works
beautifully, I can make it print all or one stallion. What I need now
is to print two or more stallions.

It looks like the article on Web Access Use Multi Select Listbox to
limit records in reports is what I need but I can't make it work.

my 2nd list box(where the stallions are listed) is named lstchosen and
it is bound to StallionID and I have added a text box


When it gets to line Me!TxtCriteria it stops and says Runtime Error 5
Invalid procedure call or error


Can someone please help??

here is the code I have added to a command button on click event

Private Sub btnTestQuery_Click()
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!lstChosen.ItemsSelected
stWhat = stWhat & Me!lstChosen.ItemsSelected(vItm)
stWhat = stWhat & stCriteria
Next vItm
Me!TxtCriteria = CStr(Left$(stWhat, Len(stWhat) -
Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("WeeklyTotals FContractsQuery")
stSQL = "SELECT StallionID, "
stSQL = stSQL & "Title FROM Stallion WHERE StallionID"
stSQL = stSQL & " IN (" & Me!TxtCriteria & ")"
loqd.SQL = stSQL
loqd.Close
DoCmd.OpenQuery "qryMultiSelTest"
End Sub

All help appreciated
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

multiple multiselect listbox to limit records in report 5
Concencate String from items in Multiselect List 5
What's wrong with the following code 1
QryDef 3
Why has this stopped working 5
multi select list box in a form as a query parameter 3
Multi Select List Box 5
Run select query from VBA code where criteria comes from a multi select ListBox 2

Top