Multiselect listbox as criteria

[bigwillno2 via AccessMonster.com]

I've tried to use many sources to get my list box problem fix, but somehow it
doesnt work. this is what i currently have:

Set frm = Form!Me
Set ctl = Me.sched
strSQL = "Select * from qrEditablePO"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
intI = 0
For Each varItemS In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [OrderNumber] = " & ctl.ItemData(varItemS)
Else
strSQL = strSQL & " OR [OrderNumber]= " & ctl.ItemData(varItemS)
End If
Next varItemS

i am very new to this and all i want to do is set a listbox that i have on a
form to be the criteria for some other query. can anyone help with this
situation.

 

[bigwillno2 via AccessMonster.com]

sorry for the reposting.....i appreciate anyones help

I've tried to use many sources to get my list box problem fix, but somehow it
doesnt work. this is what i currently have:

Set frm = Form!Me
Set ctl = Me.sched
strSQL = "Select * from qrEditablePO"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
intI = 0
For Each varItemS In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [OrderNumber] = " & ctl.ItemData(varItemS)
Else
strSQL = strSQL & " OR [OrderNumber]= " & ctl.ItemData(varItemS)
End If
Next varItemS

i am very new to this and all i want to do is set a listbox that i have on a
form to be the criteria for some other query. can anyone help with this
situation.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to use a
multi-select listbox in the criteria of a field. Perhaps if you compare
that to yours, you'll see whats wrong.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[bigwillno2 via AccessMonster.com]

this seems to be it thanks...for the great help...however the data i am
bounding is the type number and when i run the query it gives me a data type
mismatch error....and i cant seems to find.....where the error might be......
i know it's between the parameters that you set to be strings..."'" ...but
can you review my code and point out which one im suppose to remove...thanks
gain....

Private Sub Command78_Click()
On Error GoTo err_command78_click
'******************** Code Start ************************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strW As String
Dim i As Integer

Set db = CurrentDb
'*****create th query based on the information on the form
strSQL = "SELECT qrALLItemsCountFxFab.* FROM qrALLItemsCountFxFab "
strW = "WHERE OrderNo IN("
For i = 0 To sched.ListCount - 1
If sched.Selected(i) Then
strW = strW & "'" & sched.Column(0, i) & "', "
End If
Next i
strW = Left(strW, Len(strW) - 2) & ");"
strSQL = strSQL & strW
'MsgBox strSQL

'*****delete the previous query
db.QueryDefs.delete "qrEditablePO"
Set qdf = db.CreateQueryDef("qrEditablePO", strSQL)

'****Open the query
DoCmd.OpenQuery "qrEditablePO", acNormal, acEdit

exit_command78_click:
Exit Sub
err_command78_click:
If Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume exit_command78_click
End If



'******************** Code end ************************
End Sub

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to use a
multi-select listbox in the criteria of a field. Perhaps if you compare
that to yours, you'll see whats wrong.

I've tried to use many sources to get my list box problem fix, but somehow
it

[quoted text clipped - 20 lines]

form to be the criteria for some other query. can anyone help with this
situation.

 

[Roger Carlson]

If it's numeric data, change this line:

strW = strW & "'" & sched.Column(0, i) & "', "

to this:
strW = strW & sched.Column(0, i) & ", "

I haven't tested, but I think this should do it.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

this seems to be it thanks...for the great help...however the data i am
bounding is the type number and when i run the query it gives me a data
type
mismatch error....and i cant seems to find.....where the error might
be......
i know it's between the parameters that you set to be strings..."'" ...but
can you review my code and point out which one im suppose to
remove...thanks
gain....

Private Sub Command78_Click()
On Error GoTo err_command78_click
'******************** Code Start ************************
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strW As String
Dim i As Integer

Set db = CurrentDb
'*****create th query based on the information on the form
strSQL = "SELECT qrALLItemsCountFxFab.* FROM qrALLItemsCountFxFab "
strW = "WHERE OrderNo IN("
For i = 0 To sched.ListCount - 1
If sched.Selected(i) Then
strW = strW & "'" & sched.Column(0, i) & "', "
End If
Next i
strW = Left(strW, Len(strW) - 2) & ");"
strSQL = strSQL & strW
'MsgBox strSQL

'*****delete the previous query
db.QueryDefs.delete "qrEditablePO"
Set qdf = db.CreateQueryDef("qrEditablePO", strSQL)

'****Open the query
DoCmd.OpenQuery "qrEditablePO", acNormal, acEdit

exit_command78_click:
Exit Sub
err_command78_click:
If Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume exit_command78_click
End If



'******************** Code end ************************
End Sub

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to use a
multi-select listbox in the criteria of a field. Perhaps if you compare
that to yours, you'll see whats wrong.

I've tried to use many sources to get my list box problem fix, but
somehow
it

[quoted text clipped - 20 lines]

form to be the criteria for some other query. can anyone help with this
situation.

 

[bigwillno2 via AccessMonster.com]

WORKS LIKE A CHARM...THANKS

If it's numeric data, change this line:

strW = strW & "'" & sched.Column(0, i) & "', "

to this:
strW = strW & sched.Column(0, i) & ", "

I haven't tested, but I think this should do it.

this seems to be it thanks...for the great help...however the data i am
bounding is the type number and when i run the query it gives me a data

[quoted text clipped - 57 lines]

 

[bigwillno2 via AccessMonster.com]

HI...

The code does what it suppose to do, however, i have a little problem. I want
to use this new query that is being formed every time the code is run on a
subform where i want to edit the content and somehow it's not letting me edit
it. it's there way you could help with this issue.

WORKS LIKE A CHARM...THANKS

If it's numeric data, change this line:

[quoted text clipped - 10 lines]