INSERT INTO multiple tables (one query)

[skizzy]

Hi,

I have two queries setup to insert data into two separate tables that I want to combine into one. I am not sure how to write it. Any help would be appreciated.

QueryToSubmitData = "INSERT INTO Employee_List (ID, LName, FName, DeptId, Limit, Active) SELECT '" & Me!EmployeeID & "', '" & Me!LastName & _
                        "', '" & Me!FirstName & "', " & Me!DeptCombo.Column(0) & ", " & Me!Limit & ", -1 FROM Employee_List WHERE " & _
                        "Employee_List.ID = 1;"

QueryToSubmitBlank = "INSERT INTO Expenses (DT, EmployeeID, Amount, SourceID, Description) SELECT '01/01/2009', '" & Me!EmployeeID & "', '0', '4', 'New Hire';"

 

[HiTechCoach]

Welcome to AWF!

I could be incorrect, but AFAIK, it it not possible with Access (JET/ACE).

If it were me, I would NOT want to combine them together.

What is the some reason that you need to combine them into a single query?

You might consider using transactions. If example, if you are needing to make sure that th first query runs successful before running the second query, then create a transaction.

 

[skizzy]

I don't need the first to run prior to the second. I just want them to run together.

Let me show the whole code.

Private Sub Submit_Click()
Dim QueryToSubmitData As String
Dim QueryToSubmitBlank As String

If Me!WhatToDo = "Add Employee" Then
    If IsNull(Me.DeptCombo) Or IsNull(Me.FirstName) Or IsNull(Me.LastName) Or IsNull(Me.Limit) Or IsNull(Me.EmployeeID) Then
        Me.ErrorNotice = "You have not filled in all required fields!"
        Exit Sub
    End If
    QueryToSubmitData = "INSERT INTO Employee_List (ID, LName, FName, DeptId, Limit, Active) SELECT '" & Me!EmployeeID & "', '" & Me!LastName & _
                        "', '" & Me!FirstName & "', " & Me!DeptCombo.Column(0) & ", " & Me!Limit & ", -1 FROM Employee_List WHERE " & _
                        "Employee_List.ID = 1;"
    
    QueryToSubmitBlank = "INSERT INTO Expenses (DT, EmployeeID, Amount, SourceID, Description) SELECT '01/01/2009', '" & Me!EmployeeID & "', '0', '4', 'New Hire';"
                        
    
    Else
        QueryToSubmitData = "UPDATE Employee_List SET Employee_List.Active = 0, Employee_List.Limit = '-1' WHERE Employee_List.ID = " & Me.RemoveEmployee.Column(0) & ";"
End If
DoCmd.SetWarnings False
DoCmd.RunSQL QueryToSubmitData
DoCmd.RunSQL QueryToSubmitBlank

DoCmd.SetWarnings True

DoCmd.Close acForm, Me.Name

So basically when adding an employee everything works as intended. But when removing an employee it errors out, because it still trying to run QueryToSubmitBlank. (It still does what it needs to do, but errors out.)

I'm just not sure how to write this so it only runs QueryToSubmitBlank when adding an employee.

 

[jal]

I don't see the problem. If your code is executing the query even when the user was requesting a totally different query, then surely the problem is that your IF-Then-Else blocks aren't nested properly as to accomodate the user's requests?

 

[jal]

[SIZE=2]Private Sub Submit_Click()
Dim QueryToSubmitData As String
Dim QueryToSubmitBlank As String
 
If Me!WhatToDo = "Add Employee" Then
If IsNull(Me.DeptCombo) Or IsNull(Me.FirstName) Or IsNull(Me.LastName) Or IsNull(Me.Limit) Or IsNull(Me.EmployeeID) Then
Me.ErrorNotice = "You have not filled in all required fields!"
Exit Sub
End If
QueryToSubmitData = "INSERT INTO Employee_List (ID, LName, FName, DeptId, Limit, Active) SELECT '" & Me!EmployeeID & "', '" & Me!LastName & _
"', '" & Me!FirstName & "', " & Me!DeptCombo.Column(0) & ", " & Me!Limit & ", -1 FROM Employee_List WHERE " & _
"Employee_List.ID = 1;"

QueryToSubmitBlank = "INSERT INTO Expenses (DT, EmployeeID, Amount, SourceID, Description) SELECT '01/01/2009', '" & Me!EmployeeID & "', '0', '4', 'New Hire';"
DoCmd.RunSQL QueryToSubmitData
DoCmd.RunSQL QueryToSubmitBlank
Else
QueryToSubmitData = "UPDATE Employee_List SET Employee_List.Active = 0, Employee_List.Limit = '-1' WHERE Employee_List.ID = " & Me.RemoveEmployee.Column(0) & ";"
DoCmd.RunSQL QueryToSubmitData
End If
DoCmd.Close acForm, Me.Name
[/SIZE]

Tip - You can probably do away with "SetWarnings(False) if you use

CurrentDB.Execute SQL

instead of

DoCmd.Run SQL

 

[skizzy]

[SIZE=2]Private Sub Submit_Click()
Dim QueryToSubmitData As String
Dim QueryToSubmitBlank As String
 
If Me!WhatToDo = "Add Employee" Then
If IsNull(Me.DeptCombo) Or IsNull(Me.FirstName) Or IsNull(Me.LastName) Or IsNull(Me.Limit) Or IsNull(Me.EmployeeID) Then
Me.ErrorNotice = "You have not filled in all required fields!"
Exit Sub
End If
QueryToSubmitData = "INSERT INTO Employee_List (ID, LName, FName, DeptId, Limit, Active) SELECT '" & Me!EmployeeID & "', '" & Me!LastName & _
"', '" & Me!FirstName & "', " & Me!DeptCombo.Column(0) & ", " & Me!Limit & ", -1 FROM Employee_List WHERE " & _
"Employee_List.ID = 1;"

QueryToSubmitBlank = "INSERT INTO Expenses (DT, EmployeeID, Amount, SourceID, Description) SELECT '01/01/2009', '" & Me!EmployeeID & "', '0', '4', 'New Hire';"
DoCmd.RunSQL QueryToSubmitData
DoCmd.RunSQL QueryToSubmitBlank
Else
QueryToSubmitData = "UPDATE Employee_List SET Employee_List.Active = 0, Employee_List.Limit = '-1' WHERE Employee_List.ID = " & Me.RemoveEmployee.Column(0) & ";"
DoCmd.RunSQL QueryToSubmitData
End If
DoCmd.Close acForm, Me.Name
[/SIZE]

Tip - You can probably do away with "SetWarnings(False) if you use

CurrentDB.Execute SQL

instead of

DoCmd.Run SQL

Perfect! Thank you!

 

[skizzy]

Perfect! Thanks!