Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Macro help needed (2002)

    In the attached file I have a macro that I need help with. The first thing is that I'd like to give the user the option to exit the macro at the beginning in case they ran it accidentally. The second thing is more complicated.

    I recieve records from an external source on a daily basis and need to import these records into my existing database. Using a macro I import the excel spreadsheet to a temp file (import_tickets). Then paste the necessary fields into another temp file (Delivery Ticket Import). The external data uses a different primary key (ID) than we do. In the Growers List table I have both keys - ours (GrowerID) and theirs (BFP-ID). Before adding them to the main table (Delivery Tickets) I need to make sure that each grower in the imported file has an existing record in the Growers List. I need the macro to alert the user if a grower in the import file doesn't exist in our Growers List and force them to add the new grower before it appends the import records to our main Delivery Ticket table. Ideally I would like it to list the records from the import_tickets table that don't have a matching ID in the Growers List (BFP-ID).
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help needed (2002)

    For your first question (the easier one), you can use MsgBox in a macro condition and StopMacro as the action. For example, your condition could be:

    <font face="Georgia">MsgBox("You are about to import data. Continue?",3)<>1</font face=georgia>

    ("3" indicates the "Yes-No-Cancel" variant of the MsgBox and "1" is the value returned if "Yes" is selected.)

    For your second question, I'll defer to someone who know more about macro programming. Have you considered plunging into VBA as an alternative to macros? VBA gives you much, much more flexibility (including the things you are wanting to do) and is much easier to read and maintain. However, there is a learning curve...

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro help needed (2002)

    Thanks Tom. It took me a while to figure it out, but now I know how to use macro conditions. The only difference from the code you gave me is that I had to use a 6 instead of a 1 for the value. For some reason, Yes=6 in my database.
    I'm very grateful for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro help needed (2002)

    You can create a query based on the "Delivery Ticket import" table with "noname" as criteria for the Grower ID field. You can then create a form based on this query.

    Alternatively, you can use the Find Unmatched Query Wizard to create a query that returns all records from "Import_tickets" that have no matching record (on ID vs BFP-ID) in "Growers List", and create a form based on this query.

    Your macro can open this form.

    I agree with John that VBA code is more flexible and easier to maintain than a macro. You can convert your macro to VBA by selecting Save As... and choosing the As module option.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro help needed (2002)

    Thanks Hans. I added a find unmatched query as you suggested and it works fine, but this is the part where I'm stumped.

    I know I need the macro to run the query, but I dont know how to let the macro know that if the query finds unmatched records it should list them, then warn the user and if it doesn't find any unmatched records it should continue on it's merry way and add the new records to the Delivery Ticket table.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro help needed (2002)

    I wouldn't know how to do this in a macro, since I (almost) never use macros. In Visual Basic I would test if DCount("*", "name_of_the_query") is greater than 0.

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

    Re: Macro help needed (2002)

    You would have to take it in steps. Use a count on the unmatched query to determine if there are any unmatched records. The count > 0 would be one condition and you would use that step to display the unmatched query and use an ellipsis in the next line of the condition to add a cancelevent or stopmacro action. The associated actions will only execute if count >0, otherwise, the next line after the last line of the condition will execute and you can add the new records.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro help needed (2002)

    Thanks Hans & Charlotte,

    Using suggestions from both of you I got it to work. I added several steps to the macro. I used a make table query for the unmatched records and added the macro condition:

    DCount("*","Unknown Growers")>0

    I added another query to list the unmatched records for the user.

    I couldn't have done it without your help. Thanks again.

Posting Permissions

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