Speed up Query AND Make a selection from given options

[pimpzter]

The guy who created this cluster of a database no longer works here. There is a section in it that has a user select an option (0-30) then click a button. When that button is clicked it runs a series of queries based on the selection. Each selection chosen takes 90 minutes to update. I am looking to see if there is any way to do the same in less time. Additionally.. I am having to select 1, click the button, wait 90 minutes. select 2, click the button, wait 90 minutes. select 3, click the button, wait 90 minutes.... and so on. Therefore I am also trying to come up with a way to have to have a button that will do selections 1-10 all in one run. Any and all help is greatly appreciated.

Private Sub btn_download_Click()
On Error GoTo Err_btn_download_Click


Dim db As Database, qwo As QueryDef, rs As Recordset, x, numrec As Long, rt As Recordset, rwo As Recordset
Dim stDocName As String, i As Long, rwot As Recordset, qwot As QueryDef, qs As QueryDef, xcount As Long, xmsg As Integer
Dim stLinkCriteria As String, xtag_name As String, x_tag_name As String, x_tag_suffix_name As String, x_short_name As String
Dim qrq As QueryDef, rrq As Recordset, qd As QueryDef, RD As Recordset, qb As QueryDef, rb As Recordset
Dim q_sched As QueryDef, r_sched As Recordset, x_sched As String, x_sched_start_date As Date


Dim q_wowrk As QueryDef, q_wrreq As QueryDef, q_ecpms As QueryDef, q_echdr As QueryDef, qm As QueryDef
Dim r_wowrk As Recordset, r_wrreq As Recordset, r_ecpms As Recordset, r_echdr As Recordset, rm As Recordset
Dim q_wrstr As QueryDef, r_wrstr As Recordset, x_task As Integer, x_count As Long, ru As Recordset, qu As QueryDef


Dim r_rev As Recordset


If IsNull(Me!opt_t) Then
    MsgBox "No T Schedule has been selected.", 16, "Stop"
    Exit Sub
End If


Select Case Me!opt_t
    Case 0
        If Me!T0_Downloaded = -1 Then
            MsgBox "T0 Schedule has already been downloaded for Week " & Me!TO_START, 16, "Stop"
            Exit Sub
        End If
    Case 1
        If Me!T1_DOWNLOADED = -1 Then
            MsgBox "T1 Schedule has already been downloaded for Week " & Me!T1_START, 16, "Stop"
            Exit Sub
        End If
    Case 2
        If Me!T2_DOWNLOADED = -1 Then
            MsgBox "T2 Schedule has already been downloaded for Week " & Me!T2_START, 16, "Stop"
            Exit Sub
        End If
    Case 3
        If Me!T3_DOWNLOADED = -1 Then
            MsgBox "T3 Schedule has already been downloaded for Week " & Me!T3_START, 16, "Stop"
            Exit Sub
        End If
    Case 4
        If Me!T4_DOWNLOADED = -1 Then
            MsgBox "T4 Schedule has already been downloaded for Week " & Me!T4_START, 16, "Stop"
            Exit Sub
        End If
    Case 5
        If Me!T5_DOWNLOADED = -1 Then
            MsgBox "T5 Schedule has already been downloaded for Week " & Me!T5_START, 16, "Stop"
            Exit Sub
        End If
    Case 6
        If Me!T6_DOWNLOADED = -1 Then
            MsgBox "T6 Schedule has already been downloaded for Week " & Me!T6_START, 16, "Stop"
            Exit Sub
         End If
    Case 7
        If Me!T7_DOWNLOADED = -1 Then
            MsgBox "T7 Schedule has already been downloaded for Week " & Me!T7_START, 16, "Stop"
            Exit Sub
        End If
    Case 8
        If Me!T8_DOWNLOADED = -1 Then
            MsgBox "T8 Schedule has already been downloaded for Week " & Me!T8_START, 16, "Stop"
            Exit Sub
        End If
    Case 9
        If Me!T9_DOWNLOADED = -1 Then
            MsgBox "T9 Schedule has already been downloaded for Week " & Me!T9_START, 16, "Stop"
            Exit Sub
        End If
    Case 10
        If Me!T10_DOWNLOADED = -1 Then
            MsgBox "T10 Schedule has already been downloaded for Week " & Me!T10_START, 16, "Stop"
            Exit Sub
        End If
    Case 11
        If Me!T11_DOWNLOADED = -1 Then
            MsgBox "T11 Schedule has already been downloaded for Week " & Me!T11_START, 16, "Stop"
            Exit Sub
        End If
    Case 12
        If Me!T12_DOWNLOADED = -1 Then
            MsgBox "T12 Schedule has already been downloaded for Week " & Me!T12_START, 16, "Stop"
            Exit Sub
        End If
    Case 13
        If Me!T13_DOWNLOADED = -1 Then
            MsgBox "T13 Schedule has already been downloaded for Week " & Me!T13_START, 16, "Stop"
            Exit Sub
        End If
    Case 14
        If Me!T14_DOWNLOADED = -1 Then
            MsgBox "T14 Schedule has already been downloaded for Week " & Me!T14_START, 16, "Stop"
            Exit Sub
        End If
    Case 15
        If Me!T15_DOWNLOADED = -1 Then
            MsgBox "T15 Schedule has already been downloaded for Week " & Me!T15_START, 16, "Stop"
            Exit Sub
        End If
    Case 16
        If Me!T16_DOWNLOADED = -1 Then
            MsgBox "T16 Schedule has already been downloaded for Week " & Me!T16_START, 16, "Stop"
            Exit Sub
        End If
    Case 17
        If Me!T17_DOWNLOADED = -1 Then
            MsgBox "T17 Schedule has already been downloaded for Week " & Me!T17_START, 16, "Stop"
            Exit Sub
        End If
    Case 18
        If Me!T18_DOWNLOADED = -1 Then
            MsgBox "T18 Schedule has already been downloaded for Week " & Me!T18_START, 16, "Stop"
            Exit Sub
        End If
    Case 19
        If Me!T19_DOWNLOADED = -1 Then
            MsgBox "T19 Schedule has already been downloaded for Week " & Me!T19_START, 16, "Stop"
            Exit Sub
        End If
    Case 20
        If Me!T20_DOWNLOADED = -1 Then
            MsgBox "T20 Schedule has already been downloaded for Week " & Me!T20_START, 16, "Stop"
            Exit Sub
        End If
    Case 21
        If Me!T20_DOWNLOADED = -1 Then
            MsgBox "T21 Schedule has already been downloaded for Week " & Me!T21_START, 16, "Stop"
            Exit Sub
        End If
    Case 22
        If Me!T22_DOWNLOADED = -1 Then
            MsgBox "T22 Schedule has already been downloaded for Week " & Me!T22_START, 16, "Stop"
            Exit Sub
        End If
    Case 23
        If Me!T23_DOWNLOADED = -1 Then
            MsgBox "T23 Schedule has already been downloaded for Week " & Me!T23_START, 16, "Stop"
            Exit Sub
        End If
    Case 24
        If Me!T24_DOWNLOADED = -1 Then
            MsgBox "T24 Schedule has already been downloaded for Week " & Me!T24_START, 16, "Stop"
            Exit Sub
        End If
    Case 25
        If Me!T25_DOWNLOADED = -1 Then
            MsgBox "T25 Schedule has already been downloaded for Week " & Me!T25_START, 16, "Stop"
            Exit Sub
        End If
    Case 26
        If Me!T26_DOWNLOADED = -1 Then
            MsgBox "T26 Schedule has already been downloaded for Week " & Me!T26_START, 16, "Stop"
            Exit Sub
        End If
    Case 27
        If Me!T27_DOWNLOADED = -1 Then
            MsgBox "T27 Schedule has already been downloaded for Week " & Me!T27_START, 16, "Stop"
            Exit Sub
        End If
    Case 28
        If Me!T28_DOWNLOADED = -1 Then
            MsgBox "T28 Schedule has already been downloaded for Week " & Me!T28_START, 16, "Stop"
            Exit Sub
        End If
    Case 29
        If Me!T29_DOWNLOADED = -1 Then
            MsgBox "T29 Schedule has already been downloaded for Week " & Me!T29_START, 16, "Stop"
            Exit Sub
        End If
    Case 30
        If Me!T30_DOWNLOADED = -1 Then
            MsgBox "T30 Schedule has already been downloaded for Week " & Me!T30_START, 16, "Stop"
            Exit Sub
        End If


End Select


xmsg = MsgBox("This download may require 90 minutes or more to perform.  Please do not exit the database during the download.  Continue?", 36, "Stop")
If xmsg = 7 Then Exit Sub


'this section downloads the schedule and queries passport for info
Set db = CurrentDb()


'Set q_sched = db.QueryDefs("Q_P3E_TASK_1_PARAM")
x = create_tstrm_on_cdrive()
x = create_lco_equip_db_in_tstrm()
x = transfer_o_tags_work_requests()
x = transfer_o_schedule()
'x = transfer_o_link()


If IsNull(Me!SD) Or IsNull(Me!ED) Then
    MsgBox "Start and/or End Dates are null.", 16, "Stop"
    Exit Sub
End If


DoCmd.OpenForm "F_WAIT"
DoCmd.Hourglass True


DoCmd.SetWarnings False
DoCmd.OpenQuery "Q_P3E_TASK_1_PARAM_APPEND"




Set rt = db.OpenRecordset("Q_SCHEDULE_P3E")




'search work order descriptions and work order tasks
Set qwo = db.QueryDefs("Q_PASSPORT_WORK_ORDER_PARAM")
Set qwot = db.QueryDefs("Q_PASSPORT_WORK_ORDER_TASK_PARAM_1")
Set qs = db.QueryDefs("Q_SCHEDULE_P3E_PARAM")
Set qrq = db.QueryDefs("Q_PASSPORT_WOTRQ_PARAM")


rt.MoveFirst
Do While Not rt.EOF
    If Not IsNull(rt!USER_TEXT8) And Len(rt!USER_TEXT8) = 8 Then
        qwo.Parameters("INPUT_WORK_ORDER_NBR") = rt!USER_TEXT8
        qwot.Parameters("INPUT_WORK_ORDER_NBR") = rt!USER_TEXT8
        qwot.Parameters("INPUT_WORK_ORDER_TASK") = rt!WORK_ORDER_TASK
        qs.Parameters("INPUT_USER_TEXT8") = rt!USER_TEXT8
        qrq.Parameters("INPUT_WORK_ORDER_NBR") = rt!USER_TEXT8
        qrq.Parameters("INPUT_WORK_ORDER_TASK") = rt!WORK_ORDER_TASK
        Set rwo = qwo.OpenRecordset()
        Set rwot = qwot.OpenRecordset()
        Set rrq = qrq.OpenRecordset()
        'update work order info for each instance of the work order in the schedule
        If Not rwo.EOF Then
            rt.Edit
            rt!WO_DESCRIPTION = Trim(rwo!WO_DESCRIPTION)
            rt!MODEL_WORK_ORDER = Trim(rwo!MODEL_WO_NUMBER)
            rt!PM_ID_NUMBER = Trim(rwo!PM_ID_NUMBER)
            rt!PM_RQ_NUMBER = Trim(rwo!PM_RQ_NUMBER)
            rt!OLD_PREDEFINED_ID = Trim(rwo!REFERENCE_NBR)
            If Len(Trim(rwo!WO_DUE_LATEST_DATE)) > 0 Then
                rt!DUE_DATE = CREATE_DATE_FROM_STRING(rwo!WO_DUE_LATEST_DATE)
            End If
            If Len(Trim(rwo!WO_DUE_BY_DATE)) > 0 Then
                rt!LATE_DATE = CREATE_DATE_FROM_STRING(rwo!WO_DUE_BY_DATE)
            End If
            rt.Update
        End If
        'update work order task info for each instance of the work order in the schedule
        If Not rwot.EOF Then
            rt.Edit
            rt!WO_TSK_STATUS = rwot!WO_TSK_STATUS
            rt!EQUIPMENT_NUMBER = Trim(rwot!EQUIPMENT_NUMBER)
            rt!COMPONENT_NUMBER = Trim(rwot!COMPONENT_NUMBER)
            rt!EQUIPMENT_TYPE = Trim(rwot!EQUIPMENT_TYPE)
            rt!WO_TSK_STATUS_DATE = Trim(rwot!WO_TSK_STATUS_DATE)
            rt!WO_TSK_STATUS_TIME = Trim(rwot!WO_TSK_STATUS_TIME)
            rt!DISCIPLINE = Trim(rwot!DISCIPLINE)
            rt!POST_MAINT_TST_IND = rwot!POST_MAINT_TST_IND
            rt!PARTS = rwot!PARTS_PLAN
            If rwot!QC_RQMNTS_PLAN = "+" Then
                rt!QC_REQD = "Y"
            End If
            rt.Update
        End If
        'Look to see if tagging is required
        If Not rrq.EOF Then
            rrq.MoveFirst
            Do While Not rrq.EOF
                If Trim(rrq!EQUIP_REQUIREMENT) = "TAGOUT" Then
                    rt.Edit
                    rt!TAG_REQD = "Y"
                    rt.Update
                End If
                rrq.MoveNext
            Loop
        End If
    End If
    rt.MoveNext
Loop


'this section will look for Equipment Numbers (P3e only contains the first field)
x_count = 0
x_task = 0
Set q_ecpms = db.QueryDefs("Q_PASSPORT_ECPMS_MWO_PARAM")
Set q_echdr = db.QueryDefs("Q_PASSPORT_ECHDR_ECODE_PARAM")
Set q_wrstr = db.QueryDefs("Q_PASSPORT_WR_TO_WO_PARAM_WO")
Set q_wrreq = db.QueryDefs("Q_PASSPORT_WORK_REQUEST_PARAM")


rt.MoveFirst
Do While Not rt.EOF
    If Len(rt!USER_TEXT8) = 8 And Len(Trim(rt!EQUIPMENT_NUMBER)) < 4 Then 'confirm the record is a work order
        'look in task table then equipment table for equipment numbers
        If Not IsNull(rt!MODEL_WORK_ORDER) Then
            q_ecpms.Parameters("INPUT_MODEL_WO_NUMBER") = rt!MODEL_WORK_ORDER
            Set r_ecpms = q_ecpms.OpenRecordset()
            If Not r_ecpms.EOF Then
                r_ecpms.MoveFirst
                q_echdr.Parameters("INPUT_E_CODE") = r_ecpms!OWNER_CODE
                Set r_echdr = q_echdr.OpenRecordset()
                If Not r_echdr.EOF Then
                    r_echdr.MoveFirst
                    x_task = 1
                    rt.Edit
                    rt!EQUIPMENT_NUMBER = Trim(r_echdr!EQUIPMENT_NUMBER)
                    rt!COMPONENT_NUMBER = Trim(r_echdr!COMPONENT_NUMBER)
                    rt!EQUIPMENT_NAME = Trim(r_echdr!EQUIPMENT_NAME)
                    rt.Update
                End If
            End If
        End If
        'Try searching work requests; ignore if tasks already searched but equipment not found
        If Len(Trim(rt!EQUIPMENT_NUMBER)) < 4 And x_task = 0 Then
            q_wrstr.Parameters("INPUT_RLT_WORK_ORDER_NBR") = rt!USER_TEXT8
            Set r_wrstr = q_wrstr.OpenRecordset()
            If Not r_wrstr.EOF Then
                r_wrstr.MoveFirst
                q_wrreq.Parameters("INPUT_WO_REQ_NUMBER") = r_wrstr!WO_REQ_NUMBER
                Set r_wrreq = q_wrreq.OpenRecordset()
                If Not r_wrreq.EOF Then
                    r_wrreq.MoveFirst
                    rt.Edit
                    rt!EQUIPMENT_NUMBER = Trim(r_wrreq!EQUIPMENT_NUMBER)
                    rt!COMPONENT_NUMBER = Trim(r_wrreq!COMPONENT_NUMBER)
                    rt!EQUIPMENT_TYPE = Trim(r_wrreq!EQUIPMENT_TYPE)
                    rt!EQUIPMENT_NAME = Trim(r_wrreq!EQUIPMENT_NAME)
                    rt.Update
                End If
            End If
        End If
        'Try searching WMS for equipment numbers
        If Len(Trim(rt!EQUIPMENT_NUMBER)) < 4 And Left(rt!OLD_PREDEFINED_ID, 2) = "WM" Then
        End If
    End If
    x_task = 0
    rt.MoveNext
Loop


'this section will find impact statements for work orders
Set qd = db.QueryDefs("Q_PASSPORT_WOSTD_OPSIMPCT_PARAM_1")
Set qb = db.QueryDefs("Q_PASSPORT_BLOB_PARAM")


rt.MoveFirst
Do While Not rt.EOF
    If Not IsNull(rt!USER_TEXT8) Then
        qd.Parameters("INPUT_REFERENCE_NBR") = rt!USER_TEXT8
        qd.Parameters("INPUT_REFERENCE_SUB_NBR") = rt!WORK_ORDER_TASK
        Set rs = qd.OpenRecordset()
        If Not rs.EOF Then
            rs.MoveFirst
            qb.Parameters("INPUT_OLE_OBJECT_ID") = rs!OLE_OBJECT_ID
            Set rb = qb.OpenRecordset()
            If Not rb.EOF Then
                rb.MoveFirst
                rt.Edit
                rt!TEXT_LENGTH = rb!TEXT_LENGTH
                rt!Text1 = rb!Text1
                rt!Text2 = rb!Text2
                rt!Text3 = rb!Text3
                rt!Text4 = rb!Text4
                rt!Text5 = rb!Text5
                rt!Text6 = rb!Text6
                rt!Text7 = rb!Text7
                rt!Text8 = rb!Text8
                rt!Text9 = rb!Text9
                rt!Text10 = rb!Text10
                rt!Text11 = rb!Text11
                rt!Text12 = rb!Text12
                rt!Text13 = rb!Text13
                rt!Text14 = rb!Text14
                rt!Text15 = rb!Text15
                rt!Text16 = rb!Text16
                rt!Text17 = rb!Text17
                rt!Text18 = rb!Text18
                rt!Text19 = rb!Text19
                rt!Text20 = rb!Text20
                If Not IsNull(rt!Text1) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text1)
                If Not IsNull(rt!Text2) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text2)
                If Not IsNull(rt!Text3) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text3)
                If Not IsNull(rt!Text4) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text4)
                If Not IsNull(rt!Text5) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text5)
                If Not IsNull(rt!Text6) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text6)
                If Not IsNull(rt!Text7) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text7)
                If Not IsNull(rt!Text8) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text8)
                If Not IsNull(rt!Text9) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text9)
                If Not IsNull(rt!Text10) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text10)
                If Not IsNull(rt!Text11) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text11)
                If Not IsNull(rt!Text12) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text12)
                If Not IsNull(rt!Text13) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text13)
                If Not IsNull(rt!Text14) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text14)
                If Not IsNull(rt!Text15) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text15)
                If Not IsNull(rt!Text16) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text16)
                If Not IsNull(rt!Text17) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text17)
                If Not IsNull(rt!Text18) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text18)
                If Not IsNull(rt!Text19) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text19)
                If Not IsNull(rt!Text20) Then rt!OPS_IMPACT = rt!OPS_IMPACT & " " & Trim(rt!Text20)
                rt.Update
            End If
        End If
    End If
    rt.MoveNext
Loop


'dump in non-discipline activities
Set qu = db.QueryDefs("Q_SCHEDULE_P3E_UPDATE_ACTIVITIES")
qu.Parameters("SD") = Me!SD
qu.Parameters("ED") = Me!ED
qu.Parameters("X_DEFAULT_ONLINE_PROJECT") = DEFAULT_ONLINE_PROJECT()
Set ru = qu.OpenRecordset()


If Not ru.EOF Then
    ru.MoveFirst
    Do While Not ru.EOF
        rt.AddNew
        rt!T_SCHEDULE = Me!opt_t
        rt!T_SCHEDULE_START_DATE = Me!SD
        rt!TASK_ID = ru!TASK_ID
        rt!PROJ_ID = ru!PROJ_ID
        rt!WBS_ID = ru!WBS_ID
        rt!STATUS_CODE = ru!STATUS_CODE
        rt!TASK_CODE = ru!TASK_CODE
        rt!TASK_NAME = ru!TASK_NAME
        rt!RSRC_ID = ru!RSRC_ID
        rt!TARGET_DRTN_HR_CNT = ru!TARGET_DRTN_HR_CNT
        rt!TARGET_START_DATE = ru!TARGET_START_DATE
        rt!TARGET_END_DATE = ru!TARGET_END_DATE
        'rt!USER_TEXT1 = ru!USER_TEXT1
        rt!USER_TEXT8 = Left(LTrim(ru!TASK_CODE), 8)
        rt!WORK_ORDER_NBR = Left(LTrim(ru!TASK_CODE), 8)
        'If Left(ru!TASK_DISCIPLINE, 3) = "OPS" Then
        '    rt!DISCIPLINE = "OPS"
        'Else
        '    rt!DISCIPLINE = ru!TASK_DISCIPLINE
        'End If
        If Len(ru!TASK_CODE) > 10 Then
            rt!WORK_ORDER_TASK = Mid(ru!TASK_CODE, 10, 2)
        End If
        rt!PROJ_SHORT_NAME = ru!PROJ_SHORT_NAME
        rt.Update
        ru.MoveNext
    Loop
End If


'Add in equipment numbers
Set qu = db.QueryDefs("Q_SCHEDULE_P3E_UPDATE_ACTIVITIES_2")
qu.Parameters("SD") = Me!SD
qu.Parameters("ED") = Me!ED
qu.Parameters("X_DEFAULT_ONLINE_PROJECT") = DEFAULT_ONLINE_PROJECT()
Set ru = qu.OpenRecordset()


If Not ru.EOF Then
    ru.MoveFirst
    Do While Not ru.EOF
        rt.FindFirst "[ID] = " & ru!ID
        If Not rt.NoMatch Then
            rt.Edit
            rt!USER_TEXT1 = Trim(Left(ru!USER_TEXT1, 20))
            rt.Update
        End If
        ru.MoveNext
    Loop
End If


DoCmd.Close acForm, "F_WAIT"
DoCmd.Hourglass False
DoCmd.SetWarnings False
DoCmd.OpenQuery "Q_SCHEDULE_P3E_APPEND"
DoCmd.SetWarnings True
Me.Requery
Me.Refresh
MsgBox "Download Complete.", 64, "Done"


Exit_btn_download_Click:
    Exit Sub


Err_btn_download_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_btn_download_Click
    
End Sub

[pbaldy]

90 minutes?!? Are you dealing with Amazon's historical transaction database or something? I'd be annoyed if something took 90 seconds. In any case, you should be able to use a For/Next loop to process 1-10. Programmatically set the value, then run this process.

[pimpzter]

Thank you for the quick reply. Im not exactly sure where to add the for next loop. Can you give me a little guidance ? Yeah.. the 90 minutes each part does cause a little headache and it ties up at least 1 PC during the time its running. Im sure there is a way to speed it up but I dont even know where to start.. lol

[pbaldy]

If you want to leave that process alone, perhaps a second button that runs 1-10?

As to speed, I might set a breakpoint and step through the code to see which steps take a lot of time. Recordset loops are handy tools, but are often overused. An update query is typically a faster way to update a batch of records, if the situation permits.

[pimpzter]

If you want to leave that process alone, perhaps a second button that runs 1-10?

thats exactly what im looking to do. ive tried copying the code to the new button on click and manipulating it to do just that but no luck so far.

As to speed, I might set a breakpoint and step through the code to see which steps take a lot of time. Recordset loops are handy tools, but are often overused. An update query is typically a faster way to update a batch of records, if the situation permits.

If you cant see anything that jumps out at you from looking at the code I know I surely wouldnt be able to find a way. This database is a mess. This is a very small portion of the entire mdb

[pimpzter]

I had only posted the code from the onclick for that 1 particular button. Shall I post all the code for that particular form ?

[pbaldy]

I probably wouldn't bother copying the code to the new button. I'd try this within a For/Next loop:


Me!opt_t = YourLoopCounterVariable
Call btn_download_Click()

[pimpzter]

I probably wouldn't bother copying the code to the new button. I'd try this within a For/Next loop:


Me!opt_t = YourLoopCounterVariable
Call btn_download_Click()

inside the code for the current button?

exactly where would be a good place to put this ? im not even 100% sure what checks to see what option is selected.

[pimpzter]

update

 Dim Tloop As Integer
Tloop = 28
Me!opt_t = Tloop

above

If IsNull(Me!opt_t) Then
    MsgBox "No T Schedule has been selected.", 16, "Stop"
    Exit Sub
End If

and removed the lines that caused popup warnings. Looks like this runs the code for the "1" selected - perfect. I havent let it completely run yet because of that whole 90 minute thing.. If you could speed that up I would worship you. .lol

still fishing... I think i have it running starting with "1" and working its way to "10" as long its case me!t?_downloaded does not equal -1. So now I am trying to figure out how to... when it starts with 1 and it has already been downloaded.. to skip to the next case until it get to one that needs to be downloaded.

Thank you for the continued guidance

[pbaldy]

No, new button:

Dim Tloop As Integer

For Tloop = 1 To 10
  Me!opt_t = Tloop
  Call btn_download_Click()
Next Tloop

You could temporarily put something like this at the beginning of the old button's code:

MsgBox "Now working on option " & Me!opt_t
Exit Sub

Which should make you get 10 message boxes with the appropriate value.

As to speeding up the code, I've already suggested setting a breakpoint and stepping through. If a particular line takes a long time to execute, you're on the way to determining the culprit. It's likely outside the scope of this forum for anybody here to get far with that, as we can't play with it. Plus, you'd need to describe what the process is intended to accomplish.

[pimpzter]

thank you very much for your help and time. Once I get this part down... I will try your suggestion for an attempt to speed this code up. It may not be possible because of all the tables and queries involved.

[pbaldy]

No problem. 90 minutes might be a record. I work with any number of tables with millions of records, and like I said I'd be annoyed by 90 seconds.

[pimpzter]

No problem. 90 minutes might be a record. I work with any number of tables with millions of records, and like I said I'd be annoyed by 90 seconds.

hmmm.. wonder whats taking this one so long to run then. Ill see what i can figure out but im still having issues with the simple stuff.. like what we worked on earlier... thought it was working but it just told me it wasnt.. uh