HOW TO Simplify following code

  • Thread starter Thread starter stuman
  • Start date Start date
S

stuman

I have an unbound form with several multi List boxes (only two shown below).
Once the User makes their selection(s) from any list box and hits command
button, the WHERE string is applied to qryByMake. I then apply those results
to qryByMake1. I am trying to simplify this process into one step and
eliminate the saved query 'qryByMNake1'. Using the second query is the only
way I have been able to make it work so far. Can anyone see or suiggest a
simpler method of code? 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 strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 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 = "[PartName] 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 - Need to
Simplify Code Below ***
strSQL = "SELECT qryByMake.* FROM qryByMake "
strSQL = strSQL & " WHERE " & strWhere
Set qdf = db.QueryDefs("qryByMake1")
qdf.SQL = strSQL

strSQL1 = "SELECT qryByMake1.ProductID, qryByMake1.Number, qryByMake1.Make,
qryByMake1.Model, qryByMake1.Brand, qryByMake1.PartName, qryByMake1.JobGroup,
qryByMake1.CO, qryByMake1.From, qryByMake1.To, qryByMake1.GroupID,
qryByMake1.SupplierID, qryByMake1.CodeID INTO tblTempMake " & vbCrLf & _
"FROM qryByMake1;"
strSQL2 = "UPDATE tblProduct SET tblProduct.TM = 0;"
strSQL3 = "UPDATE tblProduct INNER JOIN tblTempMake ON tblProduct.ProductID
= tblTempMake.ProductID SET tblProduct.TM = -1;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.OpenForm "frmProduct", acFormDS, "", "(((tblSequence.Mfg)=""IMC / OP
PARTS"") AND ((TM)=-1))", , acNormal
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
 
You have several problems here:

DoCmd.RunSQL strSQL1 will throw an error, RunSQL is for action queries only, not
for SELECT queries.

DoCmd.RunSQL strSQL2 and DoCmd.RunSQL strSQL3 have no criteria so will update
the entire tables.

I don't know your table structures but might I suggest that you build your WHERE
clause referencing the appropriate columns of tblProduct and tblTempMake and
then run one query:

strSQL = "UPDATE tblProduct LEFT JOIN tblTempMake" _
& " ON tblProduct.ProductID = tblTempMake.ProductID" _
& " SET tblProduct.TM = iif(IsNull(tblTempMake.ProductID),0,tblProduct.TM=-1)" _
& " WHERE " & strWhere

I would also suggest that you use:
CurrentDb.Execute strSQL, dbFailOnError
rather than
DoCmd.RunSQL strSQL
as the former will raise an error if your sql is invalid, rather than failing
silently.

HTH
John
##################################
Don't Print - Save trees
I have an unbound form with several multi List boxes (only two shown below).
Once the User makes their selection(s) from any list box and hits command
button, the WHERE string is applied to qryByMake. I then apply those results
to qryByMake1. I am trying to simplify this process into one step and
eliminate the saved query 'qryByMNake1'. Using the second query is the only
way I have been able to make it work so far. Can anyone see or suiggest a
simpler method of code? 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 strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 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 = "[PartName] 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 - Need to
Simplify Code Below ***
strSQL = "SELECT qryByMake.* FROM qryByMake "
strSQL = strSQL & " WHERE " & strWhere
Set qdf = db.QueryDefs("qryByMake1")
qdf.SQL = strSQL

strSQL1 = "SELECT qryByMake1.ProductID, qryByMake1.Number, qryByMake1.Make,
qryByMake1.Model, qryByMake1.Brand, qryByMake1.PartName, qryByMake1.JobGroup,
qryByMake1.CO, qryByMake1.From, qryByMake1.To, qryByMake1.GroupID,
qryByMake1.SupplierID, qryByMake1.CodeID INTO tblTempMake " & vbCrLf & _
"FROM qryByMake1;"
strSQL2 = "UPDATE tblProduct SET tblProduct.TM = 0;"
strSQL3 = "UPDATE tblProduct INNER JOIN tblTempMake ON tblProduct.ProductID
= tblTempMake.ProductID SET tblProduct.TM = -1;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3
DoCmd.OpenForm "frmProduct", acFormDS, "", "(((tblSequence.Mfg)=""IMC / OP
PARTS"") AND ((TM)=-1))", , acNormal
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...
 
My mistake, I missed the INTO in the string. The general principle still stands
though, it will be quicker and more efficient to use the criteria on the INSERT
query rather than creating a temporary table. It will also avoid the bloat that
creating and deleting rows will cause to the db file.

HTH
John
##################################
Don't Print - Save trees
Not so easy to simplified some else codes without knowing their structure and
logic, but you have some mistake here:

strSQL1 is make table query (SELECT....INTO) , so DoCmd.RunSQL will not raise
an error.
Thats why he didn't put any criteria in strSQL2 and strSQL3.


John said:
You have several problems here:

DoCmd.RunSQL strSQL1 will throw an error, RunSQL is for action queries only, not
for SELECT queries.

DoCmd.RunSQL strSQL2 and DoCmd.RunSQL strSQL3 have no criteria so will update
the entire tables.

I don't know your table structures but might I suggest that you build your WHERE
clause referencing the appropriate columns of tblProduct and tblTempMake and
then run one query:

strSQL = "UPDATE tblProduct LEFT JOIN tblTempMake" _
& " ON tblProduct.ProductID = tblTempMake.ProductID" _
& " SET tblProduct.TM = iif(IsNull(tblTempMake.ProductID),0,tblProduct.TM=-1)" _
& " WHERE " & strWhere

I would also suggest that you use:
CurrentDb.Execute strSQL, dbFailOnError
rather than
DoCmd.RunSQL strSQL
as the former will raise an error if your sql is invalid, rather than failing
silently.
I have an unbound form with several multi List boxes (only two shown below).
Once the User makes their selection(s) from any list box and hits command
Click to expand...
[quoted text clipped - 93 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top