N
NEWER USER
The following Sub is working, but I am looking to "Clean it up".
I have an unbound form with a list box. I make a selection(s) from the list
box which builds the Where string of my query (qryCoverage). From here, I
build another query (qryCoverage1). I have to delete the existing
qryCoverage1 or I get an error message that it already exists. Is this
necessary? I then build a make table query and use that table to open yet
another query to display the results. How might I simplify this process? Any
help appreciated.
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDoc As String
Dim strSQL As String
Dim strSQL1 As String
strDoc = "qryPctCoverage"
With CodeContextObject
DoCmd.SetWarnings False
With Me!lstType
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere = "[TypeID] IN (" & Left$(strWhere, lngLen) & ") "
End If
Set db = CurrentDb
'*** create the query based from the list box information on the form
strSQL = "SELECT qryByCoverage.* FROM qryByCoverage "
strSQL = strSQL & " WHERE " & strWhere
'*** delete the previous query
db.QueryDefs.Delete "qryByCoverage1"
Set qdf = db.CreateQueryDef("qryByCoverage1", strSQL)
'*** create the make table query
strSQL1 = "SELECT tblProduct.Number, tblProduct.JNumber, tblSupplier.Brand,
tblGroup.SDescription, qryByCoverage1.Group, qryByCoverage1.Subgroup,
tblCode.Code, tblProduct.CO, tblProduct.Sales,
Round([Sales]/[SumOfSales]*100,6) AS Pct, tblProduct.Tag INTO tblCoverage " &
vbCrLf & _
"FROM tblSupplier INNER JOIN (tblGroup INNER JOIN (tblCode INNER JOIN
(qryByCoverage1 INNER JOIN tblProduct ON (qryByCoverage1.TypeID =
tblProduct.TypeID) AND (qryByCoverage1.GroupID = tblProduct.GroupID)) ON
tblCode.CodeID = tblProduct.CodeID) ON tblGroup.GroupID = tblProduct.GroupID)
ON tblSupplier.SupplierID = tblProduct.SupplierID " & vbCrLf & _
"WHERE (((tblProduct.Tag)=[Forms]![frmSelectCriteriaCoverage]![Chk])) "
& vbCrLf & _
"ORDER BY tblProduct.Sales DESC;"
DoCmd.RunSQL strSQL1
End Select
End With
'Use created table as Recordsource to display values
DoCmd.OpenQuery strDoc, acViewNormal, acEdit
DoCmd.Maximize
DoCmd.SetWarnings True
End If
Exit_cmdOK_Click:
Exit Sub
Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error in 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
I have an unbound form with a list box. I make a selection(s) from the list
box which builds the Where string of my query (qryCoverage). From here, I
build another query (qryCoverage1). I have to delete the existing
qryCoverage1 or I get an error message that it already exists. Is this
necessary? I then build a make table query and use that table to open yet
another query to display the results. How might I simplify this process? Any
help appreciated.
Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDoc As String
Dim strSQL As String
Dim strSQL1 As String
strDoc = "qryPctCoverage"
With CodeContextObject
DoCmd.SetWarnings False
With Me!lstType
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere = "[TypeID] IN (" & Left$(strWhere, lngLen) & ") "
End If
Set db = CurrentDb
'*** create the query based from the list box information on the form
strSQL = "SELECT qryByCoverage.* FROM qryByCoverage "
strSQL = strSQL & " WHERE " & strWhere
'*** delete the previous query
db.QueryDefs.Delete "qryByCoverage1"
Set qdf = db.CreateQueryDef("qryByCoverage1", strSQL)
'*** create the make table query
strSQL1 = "SELECT tblProduct.Number, tblProduct.JNumber, tblSupplier.Brand,
tblGroup.SDescription, qryByCoverage1.Group, qryByCoverage1.Subgroup,
tblCode.Code, tblProduct.CO, tblProduct.Sales,
Round([Sales]/[SumOfSales]*100,6) AS Pct, tblProduct.Tag INTO tblCoverage " &
vbCrLf & _
"FROM tblSupplier INNER JOIN (tblGroup INNER JOIN (tblCode INNER JOIN
(qryByCoverage1 INNER JOIN tblProduct ON (qryByCoverage1.TypeID =
tblProduct.TypeID) AND (qryByCoverage1.GroupID = tblProduct.GroupID)) ON
tblCode.CodeID = tblProduct.CodeID) ON tblGroup.GroupID = tblProduct.GroupID)
ON tblSupplier.SupplierID = tblProduct.SupplierID " & vbCrLf & _
"WHERE (((tblProduct.Tag)=[Forms]![frmSelectCriteriaCoverage]![Chk])) "
& vbCrLf & _
"ORDER BY tblProduct.Sales DESC;"
DoCmd.RunSQL strSQL1
End Select
End With
'Use created table as Recordsource to display values
DoCmd.OpenQuery strDoc, acViewNormal, acEdit
DoCmd.Maximize
DoCmd.SetWarnings True
End If
Exit_cmdOK_Click:
Exit Sub
Err_cmdOK_Click:
If Err.Number = 3265 Then '*** if the error in 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