Make Table Query based on a Form

[Ryan.Paquette]

Hello'

I'm trying to get a make table query (which get's it's data from 2
fields on a form) to work.
(1 Combo box, 1 List box(multi select))

It works with the Combo box or Text Boxes, but I cannot get it to work
for a List Box(Multi Select). (asks for the parameter when I use the
List Box... works fine with a cbo or text...)

What do I need to do to get the Query to recognize the List Box?
& all the separate values chosen from it?

(The query is being initiated from a command button on the form where
the list box & combo box reside)

*I tried the following but got confused: (me's a novice user)
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Ryan.Paquette]

Hello'

I'm trying to get a make table query (which get's it's data from 2
fields on a form) to work.
(1 Combo box, 1 List box(multi select))

It works with the Combo box or Text Boxes, but I cannot get it to work
for a List Box(Multi Select). (asks for the parameter when I use the
List Box... works fine with a cbo or text...)

What do I need to do to get the Query to recognize the List Box?
& all the separate values chosen from it?

(The query is being initiated from a command button on the form where
the list box & combo box reside)

*I tried the following but got confused: (me's a novice user)http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

! I'm using Access 2000 SP2

 

[ryan.paquette]

Ive tried many of the available "sample" codes from the other postings
although I can't seem to wrap my head around this one....
Here is the code below,,, (from http://www.mvps.org/access/forms/frm0007.htm)
Each time I run it, it asks for the parameter in the query.
How do I reference these listbox choices in the query?


Private Sub cmdChoose_I_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmChoose_Attendees
Set ctl = frm!lstEmpNames
strSQL = "Select * from tblEmployees where [Employee Name]="
'Assuming long [Employee Name] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Employee
Name]="
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 12)

Dim stDocNamt As String

stDocNamt = "qryTemp_Attend"
DoCmd.OpenQuery stDocNamt, acNormal, acEdit

 

[Douglas J. Steele]

Assuming that Employee Name is a text field, you need quotes around the
values.

Normally, you could use single quotes, but since names can contain
apostrophes (O'Reilly), you're best off using double quotes. As well, the
code you cited could be better (it makes a far longer string than is
required).

Try:

Private Sub cmdChoose_I_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmChoose_Attendees
Set ctl = frm!lstEmpNames
strSQL = "Select * from tblEmployees where [Employee Name] IN ("
'Assuming long [Employee Name] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & Chr$(34) & ", "
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"


Note, though, that all you're doing is creating a SQL string. You're not
using that string anywhere. Are you hoping that qryTemp_Attend will pick up
the new SQL? If so, you need:

Dim qdf As DAO.QueryDef
Dim stDocNamt As String

stDocNamt = "qryTemp_Attend"
Set qdf = CurrentDb.QueryDefs(stDocName)
qdf.SQL = strSQL

DoCmd.OpenQuery stDocNamt, acNormal, acEdit


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ive tried many of the available "sample" codes from the other postings
although I can't seem to wrap my head around this one....
Here is the code below,,, (from
http://www.mvps.org/access/forms/frm0007.htm)
Each time I run it, it asks for the parameter in the query.
How do I reference these listbox choices in the query?


Private Sub cmdChoose_I_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmChoose_Attendees
Set ctl = frm!lstEmpNames
strSQL = "Select * from tblEmployees where [Employee Name]="
'Assuming long [Employee Name] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Employee
Name]="
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 12)

Dim stDocNamt As String

stDocNamt = "qryTemp_Attend"
DoCmd.OpenQuery stDocNamt, acNormal, acEdit

 

[ryan.paquette]

Perfect... I was missing the Query Def part...Thanks