loop before execute

[chainedtodesk]

i am trying to set up a job that will run when other jobs or steps have completed. i have a job that runs on a mainframe that changes a date record when the new days business starts, this can happen anytime from 2am to 6am and i need to execute a new job as soon as possible. i tried the following code to run a query verify if the date chaged. it runs through one time but then i get an error that the table used in the query is in use?? any advice, thanks

Option Compare Database

Function chkdate()

Do
DoCmd.SetWarnings False
DoCmd.Hourglass True
Debug.Print "Process Query Check Date"
DoCmd.OpenQuery ("qryCheckDate")

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As Variant

Set dbs = CurrentDb

sql = "Select DateChk from tblDateCheck"
Set rst = dbs.OpenRecordset(sql)

Loop Until sql = "YES"

End Function

 

[TomHW]

I am not exactly sure what everything in your code is attempting to accomplish. I am guessing that this code is supposed to loop contiuously until the date has changed (possibly for four hours) and then execute a new job when the date has changed.

A few things I noticed:
- Your variable 'sql' will never equal "YES" as you constantly set it to "Select DateChk from tblDateCheck". More than likely you are trying to see if the value returned in the recordset 'rst' for the field 'DateChk' is equal to "YES".

- You shouldn't declare your variables inside of the loop.

- You should close your recordset after opening it and possibly even set it to nothing before moving to the next iteration of the loop

- What is the SQL for qryCheckDate and what is the purpose of opening it? If qryCheckDate uses the table tblDateCheck, this may be a reason why you get a table in use error.


Basically I need to know what the purpose of qryCheckDate is and what exactly you want this function to accomplish.

Tom

 

[chainedtodesk]

sorry... here goes.

1)on my mainframe i have a job that changes a daily datecard. 2) i have a query "qryCheckDate" that looks to see if it has changed to match the current day/date. if it does it changes field "datechk" from "NO" to "YES". 3)when in fact that it has changed to "YES" i need it to execute a group of other queries (which i havent added just yet). but i need the job to loop and check until it has changed and can complete the processes.

 

[TomHW]

Well in that case you may want something more along the lines of:

Function chkdate()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim blnEndLoop As Boolean
    
    blnEndLoop = False
    strSQL = "SELECT DateChk FROM tblDateCheck;"
    Set dbs = CurrentDb
    
    Do Until blnEndLoop
        [green]' Check to see if datecard has changed to current date[/green]
        DoCmd.SetWarnings False
        DoCmd.Hourglass True
        Debug.Print "Process Query Check Date"
        DoCmd.OpenQuery ("qryCheckDate")
        DoCmd.Hourglass False
        DoCmd.SetWarnings True
    
        [green]' Check to see if DateChk has been set to "YES"[/green]
        Set rst = dbs.OpenRecordset(strSQL)
        With rst
            If !DateChk = "YES" Then
                ' End the loop
                blnEndLoop = True
            End If
            
            .Close
        End With
        Set rst = Nothing
        
        [green]' Give the processor some time to do some things as this loop
        '   will probably run for a very long time[/green]
        DoEvents
    Loop
    
    [green]' We are out of the loop so the datecard is now the current date,
    '   clean up and run some other jobs[/green]
    Set dbs = Nothing
 End Function

This was written off the top of my head but should be a very good starting point. This can be simplified quite a bit by removing all of the recordset code and checking for "YES" with:

If DLookup("DateChk", "tblDateCheck") = "YES" Then

If I knew the SQL for the query "qryCheckDate" then this all could probably be simplified even further.

Hope this helps,

Tom

 

[chainedtodesk]

Tom; that worked thanks very much and i see where i was causing myself issues, i will try to use that dlookup code to see where that can come into play and ease the code, have not used that before but like to learn. thanks again for your help.