By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,600 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,600 IT Pros & Developers. It's quick & easy.

Use parameters from a multi select list box in a query

P: n/a
Hello - I'm a newbie to coding!

I'm working on an access 2000 database which has three tables:
tblContacts - A list of customer contacts.
tblOrgTypes - A list of organisational types.
tblPositions - A list of job descriptions (Contacts can hold more than
one position)

I want to use a multi-select list box (Containing alphabetical list of
positions) to run a query. HELP!

I've tried the example at http://support.microsoft.com/kb/q135546
(Method 2) It works ok on the northwind database. When I try to use it
on my database I get an error: "User-defined type not defined"

I cant' figure out what I need to change in the code to make it work?

Private Sub Command4_Click()

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("MultiSelect Criteria Example")
Q.SQL = "Select * From tblOrgTypes Where [OrgTypeID] In(" &
Criteria & _
");"
Q.Close

' Run the query.
DoCmd.OpenQuery "MultiSelect Criteria Example"

End Sub

Thanks
SuffrinMick
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
What is the data type of the field OrgTypeID? I'm assuming numeric. If
this is not so, ignore the following.

Say what your looking for in the where clause is, for example:

OrgTypeID in (12, 53, 23)

By including the chr(34) in the building of the string in your
command4_click procedure, what you're trying to run is:

OrgTypeID in ("12", "53", "23")

To get the former expression, rewrite your for..next statement without
the chr(34):

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = ctl.ItemData(Itm)
Else
Criteria = Criteria & "," & ctl.ItemData(Itm)
End If
Next Itm

Use the debug.print in your code when your developing to make sure
you're constructing SQL and other strings correctly. For example, in
your code, I'd dim another variable:

dim strSql as string

and then write an expression followed by the debug.print command:

strSQL = "Select * From tblOrgTypes Where [OrgTypeID] In(" &
Criteria & ")"

debug.print strSql

Your Q.SQL statement can now be written as

Q.SQL = strSql

and if it messes up, you can examine the SQL in the debug window by
pressing ctrl-G. If noting appears obvious, copoy/cut the expression
from the debug window and make a new query on the query tab. Don't
select any tables and close the Show Table window. View the query in
SQL and paste in the SQL from the debug window and try to run it.
Access will tell you where things aren't quite right.

A couple of other things, since you're new to coding.

Give your controls meaningful names. This makes things far easier to
manage when you're coding. Currently, you're using Command4, a system
generated name given by Access. Standardize your naming convention. The
Reddick convention is one way - have a look at this at
http://www.xoc.net/standards/rvbanc.asp - you won't understand
everything they are talking about, but some of the terms and coding
conventions you will...personally, I don't like the method described for
naming databse objects such as tables and use a method I see more often
in Oracle, like TBL_TABLE_NAME. Your command4 button, for example, you
could name btnOK or cmdOK.

The same goes for the criteria variable. I tend to prefix variables
with a 3 letter prefix (as per Reddick) to tell me what type of variable
I'm working with. Criteria, I would have named strCriteria, for example.
It just makes it easier for you as time goes by and code grows
SuffrinMick wrote:
Private Sub Command4_Click()

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("MultiSelect Criteria Example")
Q.SQL = "Select * From tblOrgTypes Where [OrgTypeID] In(" &
Criteria & _
");"
Q.Close

' Run the query.
DoCmd.OpenQuery "MultiSelect Criteria Example"

End Sub


--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #2

P: n/a
Tim - Thanks for your prompt and comprehensive reply, unfortunately
I'm still having the same error message:

The code "Q As QueryDef" is highlighted and a message box appears with
the message: "Compile Error: User-Defined type not defined."

The code works fine in the Northwind database!???

Any ideas?

Thanks
Suffrinmick
Tim Marshall <TI****@antarctic.flowerpots> wrote in message news:<co**********@coranto.ucs.mun.ca>...
What is the data type of the field OrgTypeID? I'm assuming numeric. If
this is not so, ignore the following.

Say what your looking for in the where clause is, for example:

OrgTypeID in (12, 53, 23)

By including the chr(34) in the building of the string in your
command4_click procedure, what you're trying to run is:

OrgTypeID in ("12", "53", "23")

To get the former expression, rewrite your for..next statement without
the chr(34):

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = ctl.ItemData(Itm)
Else
Criteria = Criteria & "," & ctl.ItemData(Itm)
End If
Next Itm

Use the debug.print in your code when your developing to make sure
you're constructing SQL and other strings correctly. For example, in
your code, I'd dim another variable:

dim strSql as string

and then write an expression followed by the debug.print command:

strSQL = "Select * From tblOrgTypes Where [OrgTypeID] In(" &
Criteria & ")"

debug.print strSql

Your Q.SQL statement can now be written as

Q.SQL = strSql

and if it messes up, you can examine the SQL in the debug window by
pressing ctrl-G. If noting appears obvious, copoy/cut the expression
from the debug window and make a new query on the query tab. Don't
select any tables and close the Show Table window. View the query in
SQL and paste in the SQL from the debug window and try to run it.
Access will tell you where things aren't quite right.

A couple of other things, since you're new to coding.

Give your controls meaningful names. This makes things far easier to
manage when you're coding. Currently, you're using Command4, a system
generated name given by Access. Standardize your naming convention. The
Reddick convention is one way - have a look at this at
http://www.xoc.net/standards/rvbanc.asp - you won't understand
everything they are talking about, but some of the terms and coding
conventions you will...personally, I don't like the method described for
naming databse objects such as tables and use a method I see more often
in Oracle, like TBL_TABLE_NAME. Your command4 button, for example, you
could name btnOK or cmdOK.

The same goes for the criteria variable. I tend to prefix variables
with a 3 letter prefix (as per Reddick) to tell me what type of variable
I'm working with. Criteria, I would have named strCriteria, for example.
It just makes it easier for you as time goes by and code grows
SuffrinMick wrote:
Private Sub Command4_Click()

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("MultiSelect Criteria Example")
Q.SQL = "Select * From tblOrgTypes Where [OrgTypeID] In(" &
Criteria & _
");"
Q.Close

' Run the query.
DoCmd.OpenQuery "MultiSelect Criteria Example"

End Sub

Nov 13 '05 #3

P: n/a
SuffrinMick wrote:
Tim - Thanks for your prompt and comprehensive reply, unfortunately
I'm still having the same error message:

The code "Q As QueryDef" is highlighted and a message box appears with
the message: "Compile Error: User-Defined type not defined."


Ah, sounds like a reference problem. I expect you're using a version of
Access before 2003. "dim Q as querydef" is DAO and my understanding is
that Access 2000 and 2002 do not have a default reference to the DAO
library set up (I use 97 and am a relative newbie to 2003).

Try this...

In your VBA window where you have the code you've quoted, go to the
Tools menu item, and select References. A window will pop up indicating
"available references" - these are, I believe, various dll files
somewhere within the bowels of your Access program files. The
references will be listed in alphabetical order EXCEPT for the
references that are selected and being used by your app which will be
listed at the top and for whom the check boxes are ticked.

What you're looking for is "Microsoft DAO 3.6 Object Library" (3.51 for
access 97). Scroll down the list until you find this reference and
check it.

Click OK to close the window and your code should run fine.

Another thing that you should really do as well is specify or
"disambiguate" (I hope I'm using that term correctly, I learned it from
Michka's Trigeminal site) your references. Instead of writing:

dim Q as querydef

specify that querydef is coming from the DAO library:

dim q as DAO.querydef
dim db as DAO.database

Anyways, try the references thing, this should get you running.
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #4

P: n/a
SuffrinMick wrote:
Tim - Thanks for your prompt and comprehensive reply, unfortunately
I'm still having the same error message:

The code "Q As QueryDef" is highlighted and a message box appears with
the message: "Compile Error: User-Defined type not defined."


Open any code module
From the menu, select Tools->References
Locate DAO and move it up until over ADO

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #5

P: n/a
Thanks you Guys - It's now working!
Suffrinmick

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.