Need to Simplify this INSERT INTO - Challenging question

S

Steve5

Registered User.
Local time
Yesterday, 18:02
Joined
Apr 23, 2003
Messages
42
Hello everyone,
I have been a member for quite awhile here on this site and I always enjoy everyone’s input to various solutions. Therefore, I figure I would post an INSERT INTO process that deletes a APPEND query then recreates it with the filters selected by the user built into the recreated APPEND Query and then runs it.
I have a user form using ACCESS 2007 that will have anywhere up to 60 different filter options for the from the user and I am trying to figure out the best way to manage these options and the code below works, but as you can see it is very long, and I wondered if there is a way to shorten this code to make it more manageable for change?
:o



Private Sub cmdRefresh_Click()
Dim i As Integer
Dim intRow As Integer, intCol As Integer
Dim arrCount(1 To 20, 1 To 2) As Variant
Dim SQL As String

TrimString = Trim(PN)
intRow = 1
For i = 0 To Me.Count - 1
If TypeOf Me(i) Is TextBox Or TypeOf Me(i) Is ComboBox Then
If StrConv(Me(i).Value, vbUpperCase) <> "ALL" _
And Me(i).Value <> acNull _
And Me(i).Value <> 0 _
And Me(i).Value <> "Lam Number" _
And Me(i).Value <> "" _
And Me(i).Name <> "ComboPlant" _
And Me(i).Name <> "ComboSloc" _
And Me(i).Name <> "TextuserID" _
And Me(i).Name <> "TextuserLVL" _
Then
intCol = 1 'Col 1 = Tag,
arrCount(intRow, intCol) = Me(i).Tag
intCol = 2 'Col 2 = Value
arrCount(intRow, intCol) = Me(i).Value
intRow = intRow + 1
Else
End If
End If
Next i
intRow = intRow - 1

'-----APPEND QUERY RECREATED with FILTERS -------------------------------------------------------------------

Dim db As DAO.Database
Dim qdFieldName As DAO.QueryDef
Dim rsFieldName As DAO.Recordset
Dim strSQLFieldName As String
Dim intFieldName As Integer
Dim qdCustomQuery As DAO.QueryDef
Dim rsCustomQuery As DAO.Recordset
Dim strSQLCustomHavingQuery As String
Dim strSQLCustomQuery As String
Set db = CurrentDb
db.QueryDefs.Refresh

'new stuff added
For Each qdCustomQuery In db.QueryDefs
If qdCustomQuery.Name = "a_PT_main" Then
db.QueryDefs.Delete qdCustomQuery.Name
End If
Next qdCustomQuery


'----------- Internal table
strSQLCustomQuery = "INSERT INTO t_PT_main ( ID, PT_ID, PART_ID, LOCATION_ID, LOCATION_NAME, PART_NAME, PART_DESCRIPTION, PART_TYPE, WORKING, RELEASED, APPROVED, SAP_TSL_NO, SPO_TSL_NO, DELTA_TSL_NO, SAP_TSL_DLRS, SPO_TSL_DLRS, DELTA_TSL_DLRS, ROP, ROQ, LAST_OVERRIDE_TYPE, OVERRIDE_QTY, OVERRIDE_BEG_DATE, OVERRIDE_EXP_DATE, CYCLE_CODE, LAST_APPROVAL_DATE, DMD12, DMD6, DMDLT, STDCOST, FC6, SERVICE_LEVEL, PC2000_SERVICE_LEVEL, X_PLANT_STATUS, NETWORK_ID, NETWORK, MRP_TYPE, MRP_CTRL, SP_PROC_KEY, ZFSE_NORM, NEW_BUY_LEAD_TIME, TRANSIT_TIME, CURR_TSL, CURR_FC, CURR_CP, LOAD_ROP, LOAD_ROQ, LOAD_MS, LOAD_CP, LOAD_F1, LOAD_F2, LOAD_F3, LOAD_F4, LOAD_F5, LOAD_F6, LOAD_F7, CURR_F1, CURR_F2, CURR_F3, CURR_F4, CURR_F5, CURR_F6, CURR_F7, PLANT, SLOC, FCLT, NO_OF_OVERRIDES, WORK_STATUS, LOC_DEFAULT, OVR_ROP, OVR_ROQ, ROP_PLANT, ROP_SLOC, ROQ_SLOC, MAX_STOCK, SAFETY_STOCK, MIN_SAFETY_STOCK, LOAD_ROP_PLANT, LOAD_ROP_SLOC, LOAD_ROQ_SLOC, LOAD_MAX_STOCK,"
strSQLCustomQuery = strSQLCustomQuery & "LOAD_SAFETY_STOCK, LOAD_MIN_SAFETY_STOCK, SPO_FC6, SPO_FCLT, LPATTRIB17, HOLD_LOAD_F1, "
strSQLCustomQuery = strSQLCustomQuery & "HOLD_LOAD_F2, HOLD_LOAD_F3, HOLD_LOAD_F4, HOLD_LOAD_F5, HOLD_LOAD_F6, HOLD_LOAD_F7, LOAD_TSL_NO, LOAD_TSL_DLRS, LOAD_DELTA_TSL_NO, LOAD_DELTA_TSL_DLRS, FC_RATIO, ON_HAND, OPEN_QTY, PBP_TSL, SEGMENTS, BUYER, SUPPLIER, KEY_SITE_IND, FIRST_RECEIPT_DATE, LAST_RECEIPT_DATE, LAST_RECEIPT_QTY, ALT_PART_IND, CURRENT_IB, FUTURE_IB_3MTH, FUTURE_IB_ALL, ACTUAL_ROTABLE_POOLSIZE, REQUIRED_ROTABLE_POOLSIZE ) "
strSQLCustomQuery = strSQLCustomQuery & "SELECT t_LAM_WQT_STATIC_DATA_2.ID, t_LAM_WQT_STATIC_DATA_2.PT_ID, t_LAM_WQT_STATIC_DATA_2.PART_ID, t_LAM_WQT_STATIC_DATA_2.LOCATION_ID, t_LAM_WQT_STATIC_DATA_2.LOCATION_NAME, t_LAM_WQT_STATIC_DATA_2.PART_NAME, t_LAM_WQT_STATIC_DATA_2.PART_DESCRIPTION, t_LAM_WQT_STATIC_DATA_2.PART_TYPE, t_LAM_WQT_STATIC_DATA_2.WORKING, t_LAM_WQT_STATIC_DATA_2.RELEASED, t_LAM_WQT_STATIC_DATA_2.APPROVED, t_LAM_WQT_STATIC_DATA_2.SAP_TSL_NO, t_LAM_WQT_STATIC_DATA_2.SPO_TSL_NO, t_LAM_WQT_STATIC_DATA_2.DELTA_TSL_NO, t_LAM_WQT_STATIC_DATA_2.SAP_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.SPO_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.DELTA_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.ROP, t_LAM_WQT_STATIC_DATA_2.ROQ, t_LAM_WQT_STATIC_DATA_2.LAST_OVERRIDE_TYPE, t_LAM_WQT_STATIC_DATA_2.OVERRIDE_QTY, t_LAM_WQT_STATIC_DATA_2.OVERRIDE_BEG_DATE, t_LAM_WQT_STATIC_DATA_2.OVERRIDE_EXP_DATE, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.CYCLE_CODE , t_LAM_WQT_STATIC_DATA_2.LAST_APPROVAL_DATE , t_LAM_WQT_STATIC_DATA_2.DMD12, t_LAM_WQT_STATIC_DATA_2.DMD6, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.DMDLT , t_LAM_WQT_STATIC_DATA_2.STDCOST, t_LAM_WQT_STATIC_DATA_2.FC6, t_LAM_WQT_STATIC_DATA_2.SERVICE_LEVEL, t_LAM_WQT_STATIC_DATA_2.PC2000_SERVICE_LEVEL, t_LAM_WQT_STATIC_DATA_2.X_PLANT_STATUS, t_LAM_WQT_STATIC_DATA_2.NETWORK_ID, t_LAM_WQT_STATIC_DATA_2.NETWORK, t_LAM_WQT_STATIC_DATA_2.MRP_TYPE, t_LAM_WQT_STATIC_DATA_2.MRP_CTRL, t_LAM_WQT_STATIC_DATA_2.SP_PROC_KEY, t_LAM_WQT_STATIC_DATA_2.ZFSE_NORM, t_LAM_WQT_STATIC_DATA_2.NEW_BUY_LEAD_TIME, t_LAM_WQT_STATIC_DATA_2.TRANSIT_TIME, t_LAM_WQT_STATIC_DATA_2.CURR_TSL, t_LAM_WQT_STATIC_DATA_2.CURR_FC, t_LAM_WQT_STATIC_DATA_2.CURR_CP, t_LAM_WQT_STATIC_DATA_2.LOAD_ROP, t_LAM_WQT_STATIC_DATA_2.LOAD_ROQ, t_LAM_WQT_STATIC_DATA_2.LOAD_MS, t_LAM_WQT_STATIC_DATA_2.LOAD_CP, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.LOAD_F1 , t_LAM_WQT_STATIC_DATA_2.LOAD_F2 , t_LAM_WQT_STATIC_DATA_2.LOAD_F3, t_LAM_WQT_STATIC_DATA_2.LOAD_F4, t_LAM_WQT_STATIC_DATA_2.LOAD_F5, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.LOAD_F6 , t_LAM_WQT_STATIC_DATA_2.LOAD_F7, t_LAM_WQT_STATIC_DATA_2.CURR_F1, t_LAM_WQT_STATIC_DATA_2.CURR_F2, t_LAM_WQT_STATIC_DATA_2.CURR_F3, t_LAM_WQT_STATIC_DATA_2.CURR_F4, t_LAM_WQT_STATIC_DATA_2.CURR_F5, t_LAM_WQT_STATIC_DATA_2.CURR_F6, t_LAM_WQT_STATIC_DATA_2.CURR_F7, t_LAM_WQT_STATIC_DATA_2.PLANT, t_LAM_WQT_STATIC_DATA_2.SLOC, t_LAM_WQT_STATIC_DATA_2.FCLT, t_LAM_WQT_STATIC_DATA_2.NO_OF_OVERRIDES, t_LAM_WQT_STATIC_DATA_2.WORK_STATUS, t_LAM_WQT_STATIC_DATA_2.LOC_DEFAULT, t_LAM_WQT_STATIC_DATA_2.OVR_ROP, t_LAM_WQT_STATIC_DATA_2.OVR_ROQ, t_LAM_WQT_STATIC_DATA_2.ROP_PLANT, t_LAM_WQT_STATIC_DATA_2.ROP_SLOC, t_LAM_WQT_STATIC_DATA_2.ROQ_SLOC, t_LAM_WQT_STATIC_DATA_2.MAX_STOCK, t_LAM_WQT_STATIC_DATA_2.SAFETY_STOCK, t_LAM_WQT_STATIC_DATA_2.MIN_SAFETY_STOCK, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.LOAD_ROP_PLANT , t_LAM_WQT_STATIC_DATA_2.LOAD_ROP_SLOC, t_LAM_WQT_STATIC_DATA_2.LOAD_ROQ_SLOC, t_LAM_WQT_STATIC_DATA_2.LOAD_MAX_STOCK, t_LAM_WQT_STATIC_DATA_2.LOAD_SAFETY_STOCK, t_LAM_WQT_STATIC_DATA_2.LOAD_MIN_SAFETY_STOCK, t_LAM_WQT_STATIC_DATA_2.SPO_FC6, t_LAM_WQT_STATIC_DATA_2.SPO_FCLT, t_LAM_WQT_STATIC_DATA_2.LPATTRIB17, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F1, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F2, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F3, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F4, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F5, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F6, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F7, t_LAM_WQT_STATIC_DATA_2.LOAD_TSL_NO, t_LAM_WQT_STATIC_DATA_2.LOAD_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.LOAD_DELTA_TSL_NO, t_LAM_WQT_STATIC_DATA_2.LOAD_DELTA_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.FC_RATIO, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.ON_HAND , t_LAM_WQT_STATIC_DATA_2.OPEN_QTY, t_LAM_WQT_STATIC_DATA_2.PBP_TSL, t_LAM_WQT_STATIC_DATA_2.SEGMENTS, t_LAM_WQT_STATIC_DATA_2.BUYER, t_LAM_WQT_STATIC_DATA_2.SUPPLIER, t_LAM_WQT_STATIC_DATA_2.KEY_SITE_IND, t_LAM_WQT_STATIC_DATA_2.FIRST_RECEIPT_DATE, t_LAM_WQT_STATIC_DATA_2.LAST_RECEIPT_DATE, t_LAM_WQT_STATIC_DATA_2.LAST_RECEIPT_QTY, t_LAM_WQT_STATIC_DATA_2.ALT_PART_IND, t_LAM_WQT_STATIC_DATA_2.CURRENT_IB, t_LAM_WQT_STATIC_DATA_2.FUTURE_IB_3MTH, t_LAM_WQT_STATIC_DATA_2.FUTURE_IB_ALL, t_LAM_WQT_STATIC_DATA_2.ACTUAL_ROTABLE_POOLSIZE, t_LAM_WQT_STATIC_DATA_2.REQUIRED_ROTABLE_POOLSIZE "
strSQLCustomQuery = strSQLCustomQuery & "From t_LAM_WQT_STATIC_DATA_2 "
strSQLCustomQuery = strSQLCustomQuery & "GROUP BY t_LAM_WQT_STATIC_DATA_2.ID, t_LAM_WQT_STATIC_DATA_2.PT_ID, t_LAM_WQT_STATIC_DATA_2.PART_ID, t_LAM_WQT_STATIC_DATA_2.LOCATION_ID, t_LAM_WQT_STATIC_DATA_2.LOCATION_NAME, t_LAM_WQT_STATIC_DATA_2.PART_NAME, t_LAM_WQT_STATIC_DATA_2.PART_DESCRIPTION, t_LAM_WQT_STATIC_DATA_2.PART_TYPE, t_LAM_WQT_STATIC_DATA_2.WORKING, t_LAM_WQT_STATIC_DATA_2.RELEASED, t_LAM_WQT_STATIC_DATA_2.APPROVED, t_LAM_WQT_STATIC_DATA_2.SAP_TSL_NO, t_LAM_WQT_STATIC_DATA_2.SPO_TSL_NO, t_LAM_WQT_STATIC_DATA_2.DELTA_TSL_NO, t_LAM_WQT_STATIC_DATA_2.SAP_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.SPO_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.DELTA_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.ROP, t_LAM_WQT_STATIC_DATA_2.ROQ, t_LAM_WQT_STATIC_DATA_2.LAST_OVERRIDE_TYPE, t_LAM_WQT_STATIC_DATA_2.OVERRIDE_QTY, t_LAM_WQT_STATIC_DATA_2.OVERRIDE_BEG_DATE, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.OVERRIDE_EXP_DATE , t_LAM_WQT_STATIC_DATA_2.CYCLE_CODE, t_LAM_WQT_STATIC_DATA_2.LAST_APPROVAL_DATE , t_LAM_WQT_STATIC_DATA_2.DMD12, t_LAM_WQT_STATIC_DATA_2.DMD6, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.DMDLT, t_LAM_WQT_STATIC_DATA_2.STDCOST, t_LAM_WQT_STATIC_DATA_2.FC6, t_LAM_WQT_STATIC_DATA_2.SERVICE_LEVEL, t_LAM_WQT_STATIC_DATA_2.PC2000_SERVICE_LEVEL, t_LAM_WQT_STATIC_DATA_2.X_PLANT_STATUS, t_LAM_WQT_STATIC_DATA_2.NETWORK_ID, t_LAM_WQT_STATIC_DATA_2.NETWORK, t_LAM_WQT_STATIC_DATA_2.MRP_TYPE, t_LAM_WQT_STATIC_DATA_2.MRP_CTRL, t_LAM_WQT_STATIC_DATA_2.SP_PROC_KEY, t_LAM_WQT_STATIC_DATA_2.ZFSE_NORM, t_LAM_WQT_STATIC_DATA_2.NEW_BUY_LEAD_TIME, t_LAM_WQT_STATIC_DATA_2.TRANSIT_TIME, t_LAM_WQT_STATIC_DATA_2.CURR_TSL, t_LAM_WQT_STATIC_DATA_2.CURR_FC, t_LAM_WQT_STATIC_DATA_2.CURR_CP, t_LAM_WQT_STATIC_DATA_2.LOAD_ROP, t_LAM_WQT_STATIC_DATA_2.LOAD_ROQ, t_LAM_WQT_STATIC_DATA_2.LOAD_MS, t_LAM_WQT_STATIC_DATA_2.LOAD_CP, t_LAM_WQT_STATIC_DATA_2.LOAD_F1, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.LOAD_F2 , t_LAM_WQT_STATIC_DATA_2.LOAD_F3, t_LAM_WQT_STATIC_DATA_2.LOAD_F4, t_LAM_WQT_STATIC_DATA_2.LOAD_F5, t_LAM_WQT_STATIC_DATA_2.LOAD_F6, t_LAM_WQT_STATIC_DATA_2.LOAD_F7, t_LAM_WQT_STATIC_DATA_2.CURR_F1, t_LAM_WQT_STATIC_DATA_2.CURR_F2, t_LAM_WQT_STATIC_DATA_2.CURR_F3, t_LAM_WQT_STATIC_DATA_2.CURR_F4, t_LAM_WQT_STATIC_DATA_2.CURR_F5, t_LAM_WQT_STATIC_DATA_2.CURR_F6, t_LAM_WQT_STATIC_DATA_2.CURR_F7, t_LAM_WQT_STATIC_DATA_2.PLANT, t_LAM_WQT_STATIC_DATA_2.SLOC, t_LAM_WQT_STATIC_DATA_2.FCLT, t_LAM_WQT_STATIC_DATA_2.NO_OF_OVERRIDES, t_LAM_WQT_STATIC_DATA_2.WORK_STATUS, t_LAM_WQT_STATIC_DATA_2.LOC_DEFAULT, t_LAM_WQT_STATIC_DATA_2.OVR_ROP, t_LAM_WQT_STATIC_DATA_2.OVR_ROQ, t_LAM_WQT_STATIC_DATA_2.ROP_PLANT, t_LAM_WQT_STATIC_DATA_2.ROP_SLOC, t_LAM_WQT_STATIC_DATA_2.ROQ_SLOC, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.MAX_STOCK , t_LAM_WQT_STATIC_DATA_2.SAFETY_STOCK, t_LAM_WQT_STATIC_DATA_2.MIN_SAFETY_STOCK , t_LAM_WQT_STATIC_DATA_2.LOAD_ROP_PLANT, t_LAM_WQT_STATIC_DATA_2.LOAD_ROP_SLOC, t_LAM_WQT_STATIC_DATA_2.LOAD_ROQ_SLOC, t_LAM_WQT_STATIC_DATA_2.LOAD_MAX_STOCK, t_LAM_WQT_STATIC_DATA_2.LOAD_SAFETY_STOCK, t_LAM_WQT_STATIC_DATA_2.LOAD_MIN_SAFETY_STOCK, t_LAM_WQT_STATIC_DATA_2.SPO_FC6, t_LAM_WQT_STATIC_DATA_2.SPO_FCLT, t_LAM_WQT_STATIC_DATA_2.LPATTRIB17, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F1, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F2, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F3, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F4, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F5, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F6, t_LAM_WQT_STATIC_DATA_2.HOLD_LOAD_F7, t_LAM_WQT_STATIC_DATA_2.LOAD_TSL_NO, t_LAM_WQT_STATIC_DATA_2.LOAD_TSL_DLRS, "
strSQLCustomQuery = strSQLCustomQuery & "t_LAM_WQT_STATIC_DATA_2.LOAD_DELTA_TSL_NO , t_LAM_WQT_STATIC_DATA_2.LOAD_DELTA_TSL_DLRS, t_LAM_WQT_STATIC_DATA_2.FC_RATIO, t_LAM_WQT_STATIC_DATA_2.ON_HAND, t_LAM_WQT_STATIC_DATA_2.OPEN_QTY , t_LAM_WQT_STATIC_DATA_2.PBP_TSL, t_LAM_WQT_STATIC_DATA_2.SEGMENTS, t_LAM_WQT_STATIC_DATA_2.BUYER, t_LAM_WQT_STATIC_DATA_2.SUPPLIER, t_LAM_WQT_STATIC_DATA_2.KEY_SITE_IND, t_LAM_WQT_STATIC_DATA_2.FIRST_RECEIPT_DATE, t_LAM_WQT_STATIC_DATA_2.LAST_RECEIPT_DATE, t_LAM_WQT_STATIC_DATA_2.LAST_RECEIPT_QTY, t_LAM_WQT_STATIC_DATA_2.ALT_PART_IND, t_LAM_WQT_STATIC_DATA_2.CURRENT_IB, t_LAM_WQT_STATIC_DATA_2.FUTURE_IB_3MTH, t_LAM_WQT_STATIC_DATA_2.FUTURE_IB_ALL, t_LAM_WQT_STATIC_DATA_2.ACTUAL_ROTABLE_POOLSIZE, t_LAM_WQT_STATIC_DATA_2.REQUIRED_ROTABLE_POOLSIZE "
strSQLCustomQuery = strSQLCustomQuery & "HAVING "

strSQLCustomHavingQuery = ""


i = 1
For i = 1 To intRow
intCol = 1
If StrConv(arrCount(i, intCol + 1), vbUpperCase) <> "ALL" _
And arrCount(i, intCol + 1) <> acNull _
And arrCount(i, intCol + 1) <> 0 _
And arrCount(i, intCol + 1) <> "Lam Number" _
And arrCount(i, intCol + 1) <> "" _
And Me(i).Name <> "ComboPlant" _
And Me(i).Name <> "ComboSloc" _
Then

strSQLCustomHavingQuery = strSQLCustomHavingQuery & arrCount(i, intCol) & " " & Chr(61) & " " & Chr(34) & arrCount(i, intCol + 1) & Chr(34) & " AND "

'MsgBox arrCount(i, intCol) & arrCount(i, intCol + 1)
End If
Next i

If ComboPlant = "ALL" And ComboSloc = "ALL" Then
strSQLCustomHavingQuery = strSQLCustomHavingQuery & "t_LAM_WQT_STATIC_DATA_2.LOCATION_NAME like " & Chr(34) & Chr(42) & Chr(34)
ElseIf ComboPlant = "ALL" And ComboSloc <> "ALL" Then
strSQLCustomHavingQuery = strSQLCustomHavingQuery & "t_LAM_WQT_STATIC_DATA_2.LOCATION_NAME like " & Chr(34) & Chr(42) & Format(ComboSloc.Value, "0000") & Chr(34)
ElseIf ComboPlant <> "ALL" And ComboSloc = "ALL" Then
strSQLCustomHavingQuery = strSQLCustomHavingQuery & "t_LAM_WQT_STATIC_DATA_2.LOCATION_NAME like " & Chr(34) & ComboPlant.Value & Chr(42) & Chr(34)
ElseIf ComboPlant <> "ALL" And ComboSloc <> "ALL" Then
strSQLCustomHavingQuery = strSQLCustomHavingQuery & "t_LAM_WQT_STATIC_DATA_2.LOCATION_NAME = " & Chr(34) & ComboPlant.Value & "_" & Format(ComboSloc.Value, "0000") & Chr(34)
End If

strSQLCustomQuery = strSQLCustomQuery & strSQLCustomHavingQuery & " ORDER BY t_LAM_WQT_STATIC_DATA_2.LOCATION_NAME, t_LAM_WQT_STATIC_DATA_2.PART_NAME;"
Set qdCustomQuery = db.CreateQueryDef("a_PT_main", strSQLCustomQuery)
Set qdCustomQuery = Nothing

'activate below comments below to run the update of location tables below
DoCmd.SetWarnings False
'---- Clear out t_PT_login
SQL = "DELETE from t_PT_main"
DoCmd.RunSQL SQL
DoCmd.OpenQuery "a_PT_main"
DoCmd.SetWarnings True
'Me.PT_main2.SetFocus
Me.PN.SetFocus
Me.Refresh
DoCmd.Close
DoCmd.openForm "PT_main1", acNormal

MsgBox " Process complete "

Set db = Nothing

End Sub
 
Well since you only have one table in your from ("From t_LAM_WQT_STATIC_DATA_2 ") you can basicaly remove all the "t_LAM_WQT_STATIC_DATA_2." from your statement, that should reduce it considerably.

If your inserting/copying (if copy, why for goodness sake? Generaly copying data is *not done*), and the tables are of the same layout you can just do...
Insert into t_PT_main
Select *
From ...

Finaly, I do not see any Max/Avg/sum/count going on with the naked Eye, if your not using it why the Group by clause?
 
You must log in or register to reply here.

Similar threads

Frothingslosh
  • Locked
Import Excel Data into Access via VBA
Replies
8
Views
67,075
Frothingslosh
Frothingslosh
K
New to VB & Module and trying to make this work
Replies
6
Views
1,361
jdraw
jdraw
K
Linking to .accdb from Excel
Replies
3
Views
6,151
kesmith
K
A
Create a Form That updates one table and inserts edits into a new table
Replies
2
Views
1,334
AccessWillKill
A
J
Need help to Insert an OLE object into a table using SQL
Replies
0
Views
7,014
jcr50
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom