I have an unbound form with several list boxes (only two listed below) that I
select my criteria from and pass it my query as the WHERE string. The code
below works but I struggled getting it to work as shown.

***I am trying to cleanup the process/code below. Deleting a query and then
creating it again doesn't make sense. Any help appreciated.***

qryQuickOrder groups several records; from there I add the WHERE string,
Delete qryQuickOrder1 and then recreate qryQuickOrder1 using qryQuickOrder
and the WHERE sttring. I then make a table using qryQuickOrder1 as the
source and update another table in the database linked to the newly created
table.


Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strDoc As String
Dim strDoc1 As String

With Me!lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With

lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
End If

With Me!lstClass
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
strDelim & "',"
End If
Next varItem
End With

lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
End If

strWhere = strWhere1

If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere & strWhere2
End If

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT qryQuickOrder.* FROM qryQuickOrder "
strSQL = strSQL & " WHERE " & strWhere
'*** delete the previous query
db.QueryDefs.Delete "qryQuickOrder1"
Set qdf = db.CreateQueryDef("qryQuickOrder1", strSQL)
'*** open the query
strDoc = "qryMakeTableQuickOrder" 'based on qryQuickOrder1
strDoc1 = "qupdOrderRankings"

DoCmd.SetWarnings False
DoCmd.OpenQuery strDoc, acNormal, acEdit
DoCmd.OpenQuery strDoc1, acNormal, acEdit
DoCmd.Close
DoCmd.RunCommand acCmdRefresh
DoCmd.SetWarnings True

End Sub

On Feb 23, 10:45*pm, NEWER USER <NEWERU...@discussions.microsoft.com>
wrote:
> I have an unbound form with several list boxes (only two listed below) that I
> select my criteria from and pass it my query as the WHERE string. The code
> below works but I struggled getting it to work as shown.
>
> ***I am trying to cleanup the process/code below. Deleting a query and then
> creating it again doesn't make sense. *Any help appreciated.***
>
> qryQuickOrder groups several records; from there I add the WHERE string,
> Delete qryQuickOrder1 and then recreate qryQuickOrder1 using qryQuickOrder
> and the WHERE sttring. *I then make a table using qryQuickOrder1 as the
> source and update another table in the database linked to the newly created
> table.
>
> Private Sub cmdOK_Click()
> * * Dim varItem As Variant
> * * Dim strWhere As String
> * * Dim strWhere1 As String
> * * Dim strWhere2 As String
> * * Dim lngLen As Long
> * * Dim strDelim As String
> * * Dim db As DAO.Database
> * * Dim qdf As DAO.QueryDef
> * * Dim strSQL As String
> * * Dim strDoc As String
> * * Dim strDoc1 As String
>
> With Me!lstGroup
> * * For Each varItem In .ItemsSelected
> * * * If Not IsNull(varItem) Then
> * * * * strWhere1 = strWhere1 & strDelim & .ItemData(varItem) &strDelim & ","
> * * End If
> * * Next varItem
> * End With
>
> lngLen = Len(strWhere1) - 1
> * If lngLen > 0 Then
> * * strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
> * * End If
>
> With Me!lstClass
> * * For Each varItem In .ItemsSelected
> * * * If Not IsNull(varItem) Then
> * * * * strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
> strDelim & "',"
> * * End If
> * * Next varItem
> * End With
>
> lngLen = Len(strWhere2) - 1
> * If lngLen > 0 Then
> * * strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
> * * End If
>
> * * strWhere = strWhere1
>
> If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
> * strWhere = strWhere & " AND " & strWhere2
> Else
> * strWhere = strWhere & strWhere2
> End If
>
> Set db = CurrentDb
>
> '*** create the query based on the information on the form
> strSQL = "SELECT qryQuickOrder.* FROM qryQuickOrder "
> strSQL = strSQL & " WHERE " & strWhere
> '*** delete the previous query
> db.QueryDefs.Delete "qryQuickOrder1"
> Set qdf = db.CreateQueryDef("qryQuickOrder1", strSQL)
> '*** open the query
> strDoc = "qryMakeTableQuickOrder" 'based on qryQuickOrder1
> strDoc1 = "qupdOrderRankings"
>
> DoCmd.SetWarnings False
> DoCmd.OpenQuery strDoc, acNormal, acEdit
> DoCmd.OpenQuery strDoc1, acNormal, acEdit
> DoCmd.Close
> DoCmd.RunCommand acCmdRefresh
> DoCmd.SetWarnings True
>
> End Sub

FWIW, you don't need to delete the query, you can just assign the
query a new SQL...
db.QueryDefs("qryQuickOrder1").SQL = strSQL

Thank you for replying. Okay, your suggestion works great. When
qryQuickOrder1 is defined, it is a Select Query. Is it possible to define
qryQuickOrder1 as a Make Table Query with output to tblQuickOrder? If so,
How do I code this? Thanks again for the help.

"(E-Mail Removed)" wrote:

> On Feb 23, 10:45 pm, NEWER USER <NEWERU...@discussions.microsoft.com>
> wrote:
> > I have an unbound form with several list boxes (only two listed below) that I
> > select my criteria from and pass it my query as the WHERE string. The code
> > below works but I struggled getting it to work as shown.
> >
> > ***I am trying to cleanup the process/code below. Deleting a query and then
> > creating it again doesn't make sense. Any help appreciated.***
> >
> > qryQuickOrder groups several records; from there I add the WHERE string,
> > Delete qryQuickOrder1 and then recreate qryQuickOrder1 using qryQuickOrder
> > and the WHERE sttring. I then make a table using qryQuickOrder1 as the
> > source and update another table in the database linked to the newly created
> > table.
> >
> > Private Sub cmdOK_Click()
> > Dim varItem As Variant
> > Dim strWhere As String
> > Dim strWhere1 As String
> > Dim strWhere2 As String
> > Dim lngLen As Long
> > Dim strDelim As String
> > Dim db As DAO.Database
> > Dim qdf As DAO.QueryDef
> > Dim strSQL As String
> > Dim strDoc As String
> > Dim strDoc1 As String
> >
> > With Me!lstGroup
> > For Each varItem In .ItemsSelected
> > If Not IsNull(varItem) Then
> > strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
> > End If
> > Next varItem
> > End With
> >
> > lngLen = Len(strWhere1) - 1
> > If lngLen > 0 Then
> > strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
> > End If
> >
> > With Me!lstClass
> > For Each varItem In .ItemsSelected
> > If Not IsNull(varItem) Then
> > strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
> > strDelim & "',"
> > End If
> > Next varItem
> > End With
> >
> > lngLen = Len(strWhere2) - 1
> > If lngLen > 0 Then
> > strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
> > End If
> >
> > strWhere = strWhere1
> >
> > If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
> > strWhere = strWhere & " AND " & strWhere2
> > Else
> > strWhere = strWhere & strWhere2
> > End If
> >
> > Set db = CurrentDb
> >
> > '*** create the query based on the information on the form
> > strSQL = "SELECT qryQuickOrder.* FROM qryQuickOrder "
> > strSQL = strSQL & " WHERE " & strWhere
> > '*** delete the previous query
> > db.QueryDefs.Delete "qryQuickOrder1"
> > Set qdf = db.CreateQueryDef("qryQuickOrder1", strSQL)
> > '*** open the query
> > strDoc = "qryMakeTableQuickOrder" 'based on qryQuickOrder1
> > strDoc1 = "qupdOrderRankings"
> >
> > DoCmd.SetWarnings False
> > DoCmd.OpenQuery strDoc, acNormal, acEdit
> > DoCmd.OpenQuery strDoc1, acNormal, acEdit
> > DoCmd.Close
> > DoCmd.RunCommand acCmdRefresh
> > DoCmd.SetWarnings True
> >
> > End Sub
>
> FWIW, you don't need to delete the query, you can just assign the
> query a new SQL...
> db.QueryDefs("qryQuickOrder1").SQL = strSQL
> .
>

On Wed, 24 Feb 2010 07:55:01 -0800, NEWER USER
<(E-Mail Removed)> wrote:

> Is it possible to define
>qryQuickOrder1 as a Make Table Query with output to tblQuickOrder?

Yes...

BUT DON'T DO IT.

MakeTable queries are *almost never needed*.

If you want to export data... export from a Query, not from a table.
If you want to create a report.... base it on a Query, not on a table.
If you want to display on a Form... base it on a Query, not on a table.

Once in a while you might have some good reason to populate a table based on a
query. IME it's almost always better to have tblQuickOrder stored in your
database as a permanent table (with field sizes, indexes, relationships, etc.
as appropriate, none of which a MakeTable query will do for you). You would
run a Delete * FROM tblQuickOrder query to empty it, and then an Append query
to populate it.
--

John W. Vinson [MVP]