Results 1 to 2 of 2

Thread: Parameter Help

  1. #1
    Join Date
    Apr 2009
    Posts
    15

    Unanswered: Parameter Help

    I'm currently trying to create a way to create a macro that will append data from 2 tables to two separate tables in a different database then delete the data from the original two tables.

    I've the got the 2 append queries and the deletion query up and running, and under criteria for graduation year for all 3 queries I put "[enter graduation year here]", which works fine but it brings up the parameter question 3 times.

    So My question is: is there anyway to only have to enter the parameter answer once and go through all 3 queries without having to repeat the parameter answer three times? And is it possible to get rid of the warning messages for each one?

    My Sql for the Queries and macro

    Append Base table Query

    Code:
    INSERT INTO [Base Table] ( [Student ID], [Last Name], [First Name], [Graduation Year] ) IN 'Z:\Isaiah Project Archives.mdb'
    SELECT [Base table].[Student ID], [Base table].[Last Name], [Base table].[First Name], [Base table].[Graduation Year]
    FROM [Base table]
    WHERE ((([Base table].[Graduation Year])=[Enter Graduation Year]));
    Append Project table query

    Code:
    INSERT INTO [Project Database] ( [Student ID], [Last Name], [First Name], [Graduation Year], [Project Year], [Project Name], [Project Date(s)], [Project Description], [Hours Completed] ) IN 'Z:\Isaiah Project Archives.mdb'
    SELECT [Project database].[Student ID], [Project database].[Last Name], [Project database].[First Name], [Project database].[Graduation Year], [Project database].[Project Year], [Project database].[Project Name], [Project database].[Project Date(s)], [Project database].[Project Description], [Project database].[Hours Completed]
    FROM [Project database]
    WHERE ((([Project database].[Graduation Year])=[Enter Graduation Year]));
    The deletion Query:

    Code:
    DELETE [Base table].[Student ID], [Base table].[Last Name], [Base table].[First Name], [Base table].[Graduation Year]
    FROM [Base table]
    WHERE ((([Base table].[Graduation Year])=[Enter Graduation Year of Class to be Deleted]));
    The Macro code

    Code:
    Option Compare Database
    
    '------------------------------------------------------------
    ' Deletion_Macro2
    '
    '------------------------------------------------------------
    Function Deletion_Macro2()
    On Error GoTo Deletion_Macro2_Err
    
        DoCmd.OpenQuery "Archive Student Query", acViewNormal, acEdit
        DoCmd.OpenQuery "Project Archive Query", acViewNormal, acEdit
        DoCmd.OpenQuery "Delete Query", acViewNormal, acEdit
    
    
    Deletion_Macro2_Exit:
        Exit Function
    
    Deletion_Macro2_Err:
        MsgBox Error$
        Resume Deletion_Macro2_Exit
    
    End Function

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I would create a form with a textbox or a combobox for the user to enter the graduation year. Then in the criteria of graduation year of each query add
    [Forms]![name of the form]![name of the text or combox].

    Also on the form add a button to run your macro.

Posting Permissions

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