Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    61

    Angry Unanswered: Conditional macro (solution in VB?)

    I have a database in which I daily upload all kinds of info. After the upload I run several delete update and append queries (all from one macro). Now I want to have a safety built in that it is not possible to run the macro twice since that screws up my tables.

    The verification that I want acces to do before it runs the macro is to go to a table called Tbl_today and look in the column business date (which has the same value in every record) and check it against the the date in a form (which is opened). Whenever these dates are equal the macro should stop running.

    I tried to achieve this by building a condition into the macro but it won't let me get values from a table only from a form! Is there a way around this?? Either in the condition or in VB
    Nobody dies a virgin because life screws us all!

  2. #2
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15

    Re: Conditional macro (solution in VB?)

    Originally posted by Kabuki_jo
    I have a database in which I daily upload all kinds of info. After the upload I run several delete update and append queries (all from one macro). Now I want to have a safety built in that it is not possible to run the macro twice since that screws up my tables.

    The verification that I want acces to do before it runs the macro is to go to a table called Tbl_today and look in the column business date (which has the same value in every record) and check it against the the date in a form (which is opened). Whenever these dates are equal the macro should stop running.

    I tried to achieve this by building a condition into the macro but it won't let me get values from a table only from a form! Is there a way around this?? Either in the condition or in VB
    I don't know much about macros, only to avoid using them at all costs.

    Anyway, please let me know how the form's date relates to the dates in your table "Tbl_today." Is the date on the form today? Or can it be any date?

    And I don't understand how you're using this table. Why does every record has the same value?

    In any event the condition you want to impose can be implemented pretty easily with code - I just need to know more precisely what it should look like.

  3. #3
    Join Date
    Oct 2003
    Posts
    61
    When the date in the table equals the date on the form than that means that the macro has already been run (since it creates a complete new Tbl_Today which will automatically has the same date as the one on the form) Therefore the macro should not be run again and should stop.

    So if the date in the form equals the date in the table than it should stop running the macro (consisting of a couple of update make table and delete queries )
    Nobody dies a virgin because life screws us all!

  4. #4
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    15
    Originally posted by Kabuki_jo
    When the date in the table equals the date on the form than that means that the macro has already been run (since it creates a complete new Tbl_Today which will automatically has the same date as the one on the form) Therefore the macro should not be run again and should stop.

    So if the date in the form equals the date in the table than it should stop running the macro (consisting of a couple of update make table and delete queries )
    ok - it sounds like Tbl_today is a one-record table which shows the date of the last update?

    If you're willing to convert your macro to VB I'd be happy to help, but I'll need to know where to put the code. When does your macro run? Is it invoked when a user hits a button, or when the form opens? Or when the database opens?

    I'll take a shot here with some assumptions:

    - the code runs when a user hits a button (keep in mind, though, the code can be run on any number of different events).

    - the data you're updating, deleting, etc. exists in tables within the current database

    - the data you're uploading has dates that are greater than the last time you ran the updates.

    -----------------------------------

    Private Sub cmd_mycommand_button_Click()

    dim db as database
    dim rs as recordset
    dim stop_date as date

    set db = currentdb()
    set rs = db.openrecordset("Tbl_today")
    rs.movelast
    rs.movefirst

    stop_date = rs("date_field's_name")
    rs.close

    'open recordset of first data table to update, delete,
    set rs = db.openrecordset("Tbl_first_table")
    rs.movelast
    rs.movefirst

    if rs("relevant_date") > stop_date then

    'put your code here

    end if

    End Sub

    -----------------------------------

    I think this might be the basic structure you'd want to use. Obviously I'm not sure because I have no idea what you're actually doing with your Update, Delete, etc statements.

    Hope this helps.

Posting Permissions

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