By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Insert into statement only completing once

BenRatcliffe
P: 9
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!

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_accept_Click()
  2.  
  3. On Error GoTo Err_btn_accept_Click
  4.  
  5. DoCmd.SetWarnings off
  6.  
  7. 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]));")
  8.  
  9. txt_budget = Me.FRM_BUDGET!budget - Me.Cost
  10.  
  11. Me.FRM_BUDGET!budget = txt_budget
  12.  
  13. If IsNull(Training_Priority) Then
  14.  
  15. MsgBox ("Please enter a training priority")
  16.  
  17. ElseIf IsNull(Me.cmb_cost_code) Then
  18.  
  19. MsgBox ("Please enter a Cost Code")
  20.  
  21. Else
  22.  
  23. Me.Confirmed_date = Date
  24. Me.request_confirmed = "Yes"
  25.  
  26. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  27.  
  28. 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;")
  29.  
  30. 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])")
  31.  
  32. If Me.First_Name + " " + Me.Surname = Forms!frm_start_page!lst_first_name + " " + Forms!frm_start_page!lst_last_name Then
  33.  
  34.   GoTo save_record
  35.  
  36.   Else
  37.  
  38.     tempsubject = "Training Database - Your training request has been accepted, Ref: Course :- " + Me.Course_Title
  39.     tempbody = Forms!frm_start_page!lst_first_name + " " + Forms!frm_start_page!lst_last_name + " has accepted your training request"
  40.  
  41.       DoCmd.SendObject _
  42.     , _
  43.     , _
  44.     , _
  45.     Me.First_Name + " " + Me.Surname, _
  46.     Forms!frm_start_page!lst_first_name + " " + Forms!frm_start_page!lst_last_name, _
  47.     , _
  48.     tempsubject, _
  49.     tempbody, _
  50.     False
  51.  
  52.     End If
  53.  
  54.  
  55.  
  56. save_record:
  57.  
  58. MsgBox ("Course has been Confirmed")
  59.  
  60. If CurrentProject.AllForms("FRM_MAN_BOOK_REQ_TAB").IsLoaded Then
  61.  
  62. Forms!frm_man_book_req_tab.Form.Requery
  63.  
  64. End If
  65.  
  66. Forms!frm_start_page!FRM_DUE_COURSES.Form.Requery
  67.  
  68. DoCmd.Close acForm, "FRM_PRE_BOOK_STAFF"
  69. DoCmd.Close
  70.  
  71. End If
  72.  
  73. Exit_btn_accept_Click:
  74.     Exit Sub
  75.  
  76. Err_btn_accept_Click:
  77.     MsgBox Err.Description
  78.     Resume Exit_btn_accept_Click
  79. 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
Apr 7 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.