Updating ms access from a form
The VBA to get the index of the record in the form is as follows. Execute str SQL1, db Fail On Error str SQL2 = "UPDATE job SET JOB_NEXT_OCCURANCE = JOB_NEXT_OCCURANCE JOB_RECURRANCE_RATE WHERE job. @Hans Up, 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?This is my first shot at VBA, and after an hour of searching I cannot find anything to solve my problem. 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 str SQL1 As String Dim str SQL2 As String Set Rs = Current Rs Set db = Current Db str SQL1 = "INSERT INTO completed_jobs(JOB_ID, DATE_COMPLETED, COMMENTS) VALUES " & Rs! @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).The part I'm adding calculates an additional loyalty discount based on order history. Private Sub Form_After Update() Dim Disc As Variant Dim TF As Variant 'Calculate the total added firkins every time a new order item is entered TF = 0 With Me. However, the entered data DOES appear in the table(s). What might be causing the forms to not display that data? Add a "Yes/No" data type field to the table which is used by your form's record source. Then your form can have a check box control for completion_status.Set it's default value property to 0, which represents False or No. Newly added records will have False/No as completion_status --- the check box will appear unchecked.
So you can write something like this (not the best example, but can help you to get an idea): Consider a simpler approach.If you need to update some field of underlying dataset, you can write something like If your form has control bound to "Some Field", then the form will be updated automatically.If this will not help, you can look to a couple of another directions: 1) Update records using SQL code.I have a job-tracking system, and there is a query that returns results of all jobs that are overdue. Execute str SQL2, db Fail On Error End Sub is completely incorrect, I believe this is what I need to figure out?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). 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. This is called on button-press I am posting an image which shows the form (non-continuous).