I have a job-tracking system, and there is a query that returns results of all jobs that are overdue. I have a form that displays each of these jobs one-by-one, and has two buttons (Job has been completed, and Job not completed). Not completed simply shows the next record. I cannot find a way to get access to the current record to update it's contents if the "Has been Completed" button is pressed, the closest I can get is the long number which represents the records position in the form. The VBA to get the index of the record in the form is as follows.
Sub Jobcompleted(frm As Form) Dim curr_rec_num As Long curr_rec_num = frm.CurrentRecord End Sub This is my first shot at VBA, and after an hour of searching I cannot find anything to solve my problem. Am I going about this the entirely wrong way? Working in Microsoft Access 2007 Further Info All tables are normalized Vehicle Table: Contains vehicle_id(pk), as well as rego and model etc Job Table: Contains job_id(pk), vehicle_id(fk) and other info about what needs to happen, as well as the next occurance date, days between each occurance of the job (all jobs repeat) and other info Job History Table: Contains job_history_id(pk), job_id(fk), date completed and comments When the job completed button is pressed, it should create a new entry in the job history table with the current date, any comments and the job id This is the script I am trying to get working
Private Sub Command29_Click() Dim strSQL1 As String Dim strSQL2 As String Set Rs = CurrentRs Set db = CurrentDb strSQL1 = "INSERT INTO completed_jobs(JOB_ID, DATE_COMPLETED, COMMENTS) VALUES " & Rs!job.ID & ", " & Date db.Execute strSQL1, dbFailOnError strSQL2 = "UPDATE job SET JOB_NEXT_OCCURANCE = JOB_NEXT_OCCURANCE+JOB_RECURRANCE_RATE WHERE job.ID = Rs!job.ID" db.Execute strSQL2, dbFailOnError End Sub
Note: Line Set Rs = CurrentRs is completely incorrect, I believe this is what I need to figure out? This is called on button-press I am posting an image which shows the form (non-continuous). @HansUp, I get what you are saying, but I dont quite think it's applicable (I did not provide enough information first time around for you to understand I think) @sarh I believe this Recordset that you are talking about is what I need, however I cannot figure out how to use it, any hints? @Matt I am 90% sure I am using a bound form (Like I said, new to Access, been looking at everything people have suggested and learning as I go). There is of course an ID for the job (Just not shown, no need to be visible), but how would I access this to perform an operation on it? SQL I can do, integrating with Access/VBA I am new at
