Results 1 to 8 of 8

Thread: Access (97)

  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access (97)

    Hi

    What I need to occur is when a user (not sure whether it needs to be a specific user) opens a runtime DB it prompts the user to run a report, which say is scheduled to run once a week. It needs to prompt the user on the day the report is scheduled to run, or if no user opened the db that day, the next time they open it after the scheduled day.

    I hope I have made myself clear

    Thanks
    WTH

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access (97)

    You just need a form to run when the database opens. You probably will need to store the date of the last time the report was run somewhere, probably in a company record. Compare the current date to this stored date. You can make this form the first form Access uses, by making selection in Tools | Startup.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (97)

    Hi

    Thanks. I should have explained further that the DB is only used to run Crystal Reports using the shell function in a module. I have solved most of it by creating a table to store dates, report names, sub names, and as suggested a form (based on switchboard) to display overdue reports. The problem I have run into is calling the sub to run the shell function. For some reason using a variable to call the sub is not recognised, where the variable is the sub name stored in the table (not sure whether I explained that very well). Any ideas

    Regards
    WTH

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access (97)

    >>The problem I have run into is calling the sub to run the shell function. For some reason using a variable to call the sub is not recognised, where the variable is the sub name stored in the table (not sure whether I explained that very well).<<

    You will have to explain that a bit better. What is the code used to call the sub? And what does the sub declaration line look like? And exactly what is your problem; that is, are you getting an error message or just not getting a value passed?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access (97)

    You may also have to use a function rather than a sub, since you can't call even a public sub from a lot of places (i.e., switchboards and commandbars).
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (97)

    Hi

    Thanks for the advice. I had tried Function as well, but with no success. The error message is "Compile error, expected sub, function, or property". The relevant command lines in the module are:

    Dim Rptcode as string

    Call Rptcode 'this causes the error message.

    where Rptcode is
    Rptcode = MyTable.Fields("ReportCode") which selects the sub (or function) name - Overdue_apps()stored in the table. The name is selected by openrecordset and seek method.

    The Overdue_apps() sub is as follows:

    Sub Overdue_apps()
    Dim Overdueapps
    Overdueapps = Shell("g:isacreportsOverdue Applications with Advisors or Applicants.exe", vbNormalFocus)
    End Sub

    While using this method is prefered, I can work around it by using macros and docmd.runmacro option.

    Thanks
    WTH

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access (97)

    I didn't realize what you were trying to do. You can't call a routine that way. The Call keyword expects to be followed by a Function or Sub, not a variable. Why don't you simply use the string that contains the name to branch into code that calls that particular procedure? You could do something like this:

    Select Case Rptcode
    Case "Overdue_apps"
    Call Overdue_apps
    Case Else
    End Select
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access (97)

    Hi

    Many thanks for the solution. If the brain had gotten out of first gear then I should have come up with this solution, but that's why they have forums like this to kick start it when it stays in neutral.

    Regards
    WTH <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

Posting Permissions

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