Simplify Code-Delete Query

N

NEWER USER

Towards the bottom, I am having to delete a query and then rebuild another to
make this work. Looking for a simpler/cleaner method. Any help appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_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

'This is what I want to cleanup below.

'*** 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
DoCmd.SetWarnings False

'The below Make Table query uses qryQuickOrder1 as its source.
strDoc = "qryMakeTableQuickOrder"
strDoc1 = "qupdOrderRankings"

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

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdOK_Click
End If

End Sub
 
A

Arvin Meyer [MVP]

Use a querydef without a name. Access won't save it. Here's the relevant
code:

Dim db as SAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
' Create a temporary querydef
Set qdf = db.CreateQueryDef("")
 
N

NEWER USER

Without a name, how do I run the following Make table query where
qryQuickOrder1 is the source for the query?

Arvin Meyer said:
Use a querydef without a name. Access won't save it. Here's the relevant
code:

Dim db as SAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
' Create a temporary querydef
Set qdf = db.CreateQueryDef("")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



NEWER USER said:
Towards the bottom, I am having to delete a query and then rebuild another
to
make this work. Looking for a simpler/cleaner method. Any help
appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_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

'This is what I want to cleanup below.

'*** 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
DoCmd.SetWarnings False

'The below Make Table query uses qryQuickOrder1 as its source.
strDoc = "qryMakeTableQuickOrder"
strDoc1 = "qupdOrderRankings"

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

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume
on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_cmdOK_Click
End If

End Sub
Click to expand...


.
Click to expand...
 
A

Arvin Meyer [MVP]

Why are you using a Make-Table, then deleting the query. Change your query
to an Append query, and precede running it with a delete query. Or even
better, Access will automatically delete a table if a new table is created
with a Make-Table query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


NEWER USER said:
Without a name, how do I run the following Make table query where
qryQuickOrder1 is the source for the query?

Arvin Meyer said:
Use a querydef without a name. Access won't save it. Here's the relevant
code:

Dim db as SAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
' Create a temporary querydef
Set qdf = db.CreateQueryDef("")
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



NEWER USER said:
Towards the bottom, I am having to delete a query and then rebuild
another
to
make this work. Looking for a simpler/cleaner method. Any help
appreciated.

Private Sub cmdOK_Click()
On Error GoTo Err_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

'This is what I want to cleanup below.

'*** 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
DoCmd.SetWarnings False

'The below Make Table query uses qryQuickOrder1 as its source.
strDoc = "qryMakeTableQuickOrder"
strDoc1 = "qupdOrderRankings"

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

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error is the query is
missing
Resume Next '*** then skip the delete line and
resume
on
the next line
Else
MsgBox Err.Description '*** write out the error and exit
the
sub
Resume Exit_cmdOK_Click
End If

End Sub
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combox box value used in Query 1
Using Combo Box and Multi List Box on Form - Send Parameters to Qu 1
HOW TO Simplify following code 2
Create Report On the Fly 1
HELP with ERROR Message 9
Cleanup Code on Form ?? 3
Coding Problem 7
Using multi-select list box data in a Query 1

Top