Cleanup Code on Form ??

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

NEWER USER

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
 
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
Click to expand...

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.

[email protected] said:
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
Click to expand...

FWIW, you don't need to delete the query, you can just assign the
query a new SQL...
db.QueryDefs("qryQuickOrder1").SQL = strSQL
.
Click to expand...
 
Is it possible to define
qryQuickOrder1 as a Make Table Query with output to tblQuickOrder?
Click to expand...

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.
 
Back
Top