CODE HELP!

  • Thread starter Thread starter NEWER USER
  • Start date Start date
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
 
If you know that the query already exists, you can simply refer to the query
to change its SQL:

Set qdf = db.QueryDefs("qryByCoverage1")
qdf.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



NEWER USER said:
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
Click to expand...
 
Thank you for your help/solution.

Douglas J. Steele said:
If you know that the query already exists, you can simply refer to the query
to change its SQL:

Set qdf = db.QueryDefs("qryByCoverage1")
qdf.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



NEWER USER said:
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
Click to expand...

.
Click to expand...
 
Back
Top