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!
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
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!
Code:
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
Many thanks
Ben