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
