Insert into statement only completing once

[BenRatcliffe]

Hi there,

I was wondering if anyone could help me. I have a comlpex database with a number of forms that have data entered on them and then saved into the correct table etc. In this instance I am trying to get the data on a form to save the data in the current table where it is stored, insert the data into 2 new tables all on a click event. I have got the first 2 tables to update fine but the third I am having difficulty with.

The third table records all action, like a snapshot view of what is happening in the database. It records data when a training course is authorised and cancelled with the date it was done and the change in budget.

The problem i'm having is that the third table will update once but will not insert any more records unless that one record is deleted. I have used the insert into statement throughout the database with no problems to date but i can't seem to figure this out.

Any pointers would be a great help.

Sorry about lack of commenting its still in development stage!

Private Sub btn_accept_Click()
 
On Error GoTo Err_btn_accept_Click
 
DoCmd.SetWarnings off
 
Forms!FRM_PRE_BOOK_MANAGER!FRM_BUDGET.Form.RecordSource = ("SELECT LU_BUDGET.Year, LU_BUDGET.[Start Date], LU_BUDGET.[End Date], LU_BUDGET.Budget FROM LU_BUDGET WHERE (((LU_BUDGET.[Start Date])<[Forms]![FRM_PRE_BOOK_MANAGER]![Date_to_attend]) AND ((LU_BUDGET.[End Date])>[Forms]![FRM_PRE_BOOK_MANAGER]![Date_to_attend]));")
 
txt_budget = Me.FRM_BUDGET!budget - Me.Cost
 
Me.FRM_BUDGET!budget = txt_budget
 
If IsNull(Training_Priority) Then
 
MsgBox ("Please enter a training priority")
 
ElseIf IsNull(Me.cmb_cost_code) Then
 
MsgBox ("Please enter a Cost Code")
 
Else
 
Me.Confirmed_date = Date
Me.request_confirmed = "Yes"
 
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
DoCmd.RunSQL ("INSERT INTO TBL_COURSE_BOOKINGS ( User_ID, Course_ID, Pre_Book_ID, Date_To_Attend, Duration, Cost, Location, Cost_Code )SELECT [Forms]![FRM_PRE_BOOK_MANAGER]![User_ID] AS Expr1, [Forms]![FRM_PRE_BOOK_MANAGER]![Course_ID] AS Expr2, [Forms]![FRM_PRE_BOOK_MANAGER]![Pre_Booking_ID] AS Expr3, [Forms]![FRM_PRE_BOOK_MANAGER]![Date_To_Attend] AS Expr4, [Forms]![FRM_PRE_BOOK_MANAGER]![Duration] AS Expr5, [Forms]![FRM_PRE_BOOK_MANAGER]![Cost] AS Expr6,[Forms]![FRM_PRE_BOOK_MANAGER]![Location] AS Expr7,[Forms]![FRM_PRE_BOOK_MANAGER]![Cost_Code] AS Expr8;")
 
DoCmd.RunSQL ("INSERT INTO TBL_RECENT_ACTIVITY ( User_ID, First_Name, Last_Name, Course_ID, Course_Title, Cost)Values ([Forms]![FRM_PRE_BOOK_MANAGER]![User_ID], [Forms]![FRM_PRE_BOOK_MANAGER]![First Name], [Forms]![FRM_PRE_BOOK_MANAGER]![Surname], [Forms]![FRM_PRE_BOOK_MANAGER]![Course_ID], [Forms]![FRM_PRE_BOOK_MANAGER]![Course Title], [Forms]![FRM_PRE_BOOK_MANAGER]![Cost])")
 
If Me.First_Name + " " + Me.Surname = Forms!frm_start_page!lst_first_name + " " + Forms!frm_start_page!lst_last_name Then
 
  GoTo save_record
 
  Else
 
    tempsubject = "Training Database - Your training request has been accepted, Ref: Course :- " + Me.Course_Title
    tempbody = Forms!frm_start_page!lst_first_name + " " + Forms!frm_start_page!lst_last_name + " has accepted your training request"
 
      DoCmd.SendObject _
    , _
    , _
    , _
    Me.First_Name + " " + Me.Surname, _
    Forms!frm_start_page!lst_first_name + " " + Forms!frm_start_page!lst_last_name, _
    , _
    tempsubject, _
    tempbody, _
    False
 
    End If
 
 
 
save_record:
 
MsgBox ("Course has been Confirmed")
 
If CurrentProject.AllForms("FRM_MAN_BOOK_REQ_TAB").IsLoaded Then
 
Forms!frm_man_book_req_tab.Form.Requery
 
End If
 
Forms!frm_start_page!FRM_DUE_COURSES.Form.Requery
 
DoCmd.Close acForm, "FRM_PRE_BOOK_STAFF"
DoCmd.Close
 
End If
 
Exit_btn_accept_Click:
    Exit Sub
 
Err_btn_accept_Click:
    MsgBox Err.Description
    Resume Exit_btn_accept_Click
End Sub

by the way the 2nd insert statement is the one that is causing problems, I have tried it exactly the same as the first insert into (with the select instead of values) but to no effect!

Many thanks
Ben