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 <TIMMY!@antarctic.flowerpots> wrote in message news:<cofjkb$2hh$1@coranto.ucs.mun.ca>...[color=blue]
> 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:
>[color=green]
> > 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[/color][/color]
