Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: Check Dir on Timer

    Hi Can anyone help?

    I have written some code to check when a file is created in a directory.

    The code check each row of a table to see if the particular filename exists in the 'Results' directory.

    I would like this piece of code to run every 30 seconds in the background until it finds all of the files.

    I'm having problem with the Timer property - can you help? Code so far:

    Dim myDir
    Dim myWalkpack As String
    Dim dbs As Database, rst As Recordset, strsql As String
    Dim rcdcount As Long
    Dim CountofRecords As Long
    Dim myFile As Integer
    Dim myCount As Integer
    Dim myResponse As String
    Set dbs = CurrentDb
    strsql = "SELECT WalkpackLogTbl.Date, WalkpackLogTbl.User, WalkpackLogTbl.District, WalkpackLogTbl.Brand, WalkpackLogTbl.[Number of Prospects], WalkpackLogTbl.[TPS'd] FROM WalkpackLogTbl WHERE (((WalkpackLogTbl.[TPS'd])=No))"
    Set rst = dbs.OpenRecordset(strsql)
    myCount = 0
    Do While Not rst.EOF
    myCount = myCount + 1
    myWalkpack = Format(rst("District"), ">") & "_" & rst("Brand") & "_" & rst("User") & "_" & Format(rst("Date"), "dd_mm_yy") & "_TPS.xls"
    myDir = Dir("\\plytrn01\sales targeting\SME Data\Results\" & myWalkpack & "")
    If myDir = myWalkpack Then
    myResponse = MsgBox("Found File: " & myDir & " Would you like to format this walkpack now?", vbYesNo + vbQuestion, "SME Walkpack Creator - Sales Targeting©")
    If myResponse = vbYes Then
    GoTo 10
    End If
    End If
    rst.MoveNext
    Loop
    10 'Format this file



    Thanks,

    Dan

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    Sub Form_Load()
    '1000 = 1 sec
    Me.TimerInterval = 30000
    End Sub

    Sub Form_Timer()

    Static RunCode As Integer
    If intShowPicture Then

    Put your code here

    End Sub

  3. #3
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Lightbulb Thats it

    Sub Form_Load()
    '1000 = 1 sec
    Me.TimerInterval = 30000
    End Sub


    Sub Form_Timer()

    Static intRunCode As Integer

    If intRunCode Then

    Put your code here

    end if
    End Sub

  4. #4
    Join Date
    Aug 2003
    Posts
    106

    Check Dir on Timer

    But does this mean i need to have a form open to run the code?

    Is there any way of simply carrying out this funtion in code only?

  5. #5
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Yes, you will need a form open... as far as I know. But you can make it 'invisible' so no one will notice it!
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  6. #6
    Join Date
    Aug 2003
    Posts
    106

    Check Dir on Timer

    Thanks Marvels! I'll give it a try...

  7. #7
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink Nope

    You can allso set it where you had your code in the first place
    and then make a new function to call it when you need it

  8. #8
    Join Date
    Aug 2003
    Posts
    106

    Another question

    Thanks!

    I have done this bit! The next bit that i would like to do, is that if it finds the file in the directory, it asks the user to format the file.

    If the user chooses yes, i would like to change a field value in a 'log' table to True.

    Set dbs = CurrentDb
    strsql = "SELECT WalkpackLogTbl.Date, WalkpackLogTbl.User, WalkpackLogTbl.District, WalkpackLogTbl.Brand, WalkpackLogTbl.[Number of Prospects], WalkpackLogTbl.[TPSD] FROM WalkpackLogTbl WHERE (((WalkpackLogTbl.[TPSD])=No))"
    Set rst = dbs.OpenRecordset(strsql)
    myCount = 0
    Do While Not rst.EOF
    myWalkpack = Format(rst("District"), ">") & "_" & rst("Brand") & "_" & rst("User") & "_" & Format(rst("Date"), "dd_mm_yy") & "_TPS.xls"
    myDir = Dir("\\plytrn01\sales targeting\SME Data\Results\" & myWalkpack & "")
    If myDir = myWalkpack Then
    myCount = myCount + 1
    myResponse = MsgBox("Found File: " & myDir & " Would you like to format this walkpack now?", vbYesNo + vbQuestion, "SME Walkpack Creator - Sales Targeting©")
    If myResponse = vbYes Then

    'change TPSD value in WalkpacksLogTbl to TRUE

    End If
    End If
    rst.MoveNext
    Loop


    Any ideas?

    Thanks
    Dan

  9. #9
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Cool Have you tryed this ?

    if myResponse = vbYes Then
    rst.edit
    rst([TPSD])=TRUE)
    rst.update
    endif

    move next
    loop




    youll have to look if you have to use 1 and true or 0 and False

  10. #10
    Join Date
    Aug 2003
    Posts
    106

    It worked!

    Thanks for this Marvels!
    Sorry for the delay in response!

    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •