Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    31

    Exclamation Unanswered: Multi-Table Parameter Delete Query... Can you help?

    Hello. I know there's a lot of post about the subject in the forum however I could not find the answer to this one issue. I've made this Parameter Delete query which is really nice except for the fact I got it to work only one way so far.

    Tables in question:

    Agent Table
    Quality Table
    Audit Table

    What it does...

    DELETE Quality.*, Agent.AgentID
    FROM Agent INNER JOIN Quality ON Agent.AgentID = Quality.AgentID
    WHERE (((Agent.AgentID)=[Please enter agent's employee ID]));


    The code above only Removes quality monitors from Quality Table given the Agent's employee ID.

    What I wanted to do without getting an error...

    DELETE Quality.*, Audit.*, Agent.AgentID
    FROM (Agent INNER JOIN Quality ON Agent.AgentID = Quality.AgentID) INNER JOIN Audit ON Agent.AgentID = Audit.AgentID
    WHERE (((Agent.AgentID)=[Please enter agent's employee ID]));


    The code above was meant to Remove quality & audit monitors from Quality & Audit Tables given the Agent's employee ID.

  2. #2
    Join Date
    Feb 2005
    Posts
    31
    So... anyone knows?

  3. #3
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    Try this,

    Keep your first query, and then create another query that deletes from the Agents table. Then create a macro that calls both queries.

    Even easier, you could write a function in VBA code to remove the records from all three tables explicitly.

  4. #4
    Join Date
    Feb 2005
    Posts
    31
    Really? Your last idea sounds great. I just do not know how to write that fuction that deletes Selected Agent from the database together with all Quality & Audit records containing that same agent ID. Perhaps you could... guide me on the right direction? Right now I have 3 buttons that call 3 different delete queries however my fear is that the end user has to delete Quality, then Audits then Agent and sometimes people just forgets and if that happens data may be corrupted.

  5. #5
    Join Date
    Feb 2005
    Location
    Blacksburg, Virginia
    Posts
    43
    My guess is you want the user to press one button and all the deletes are performed.

    To do this, create a button on your form and press Cancel when the wizard pops up. Open the properties page for the button and click the Events tab. Go to OnClick and choose [Event Procedure]. Then hit the button to the right of the selection box (three dots) to open the VBA Window.

    Now you can just code away:

    Code:
    Dim rs As New ADODB.Recordset
        Dim sql As String
        Dim UserInput As String
        
        'get id from user
        UserInput = InputBox("Please enter agent's employee ID")
        
        'verify that id exists
        sql = "SELECT * FROM [Agent] WHERE [AgentID] LIKE '" & UserInput & "'"
        rs.Open sql, CurrentProject.Connection
        
        If rs.EOF Then
        
            'recordset has no entries, agent ID was not found
            MsgBox "agent with employee id " & UserInput & " could not be found", vbExclamation
            Exit Sub
            
        End If
        
        'delete from Audit Table
        sql = "DELETE * FROM [Audit] WHERE [AgentID] LIKE '" & UserInput & "'"
        CurrentProject.Connection.Execute sql
        
        'delete from Quality Table
        sql = "DELETE * FROM [Quality] WHERE [AgentID] LIKE '" & UserInput & "'"
        CurrentProject.Connection.Execute sql
        
        'delete from Agent Table
        sql = "DELETE * FROM [Agent] WHERE [AgentID] LIKE '" & UserInput & "'"
        CurrentProject.Connection.Execute sql
    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
  •