Geek Answers Handbook

How to directly update a database record from a form number (Access 2007)

I have a job tracking system, and I have a query that returns the results of all overdue jobs. I have a form that displays each of these tasks one after another and has two buttons (Job is completed, but Job is not completed). Not completed just shows the next entry. I can’t find a way to access the current record to update its contents, if the “Finished” button is pressed, the closest I can get is a long number that represents the position of the records in the form.

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 completely wrong about this? Work in Microsoft Access 2007

Additional information All tables are normalized.

Car table: Contains car_id (pk), as well as reg and model, etc.

Work table. Contains job_id (pk), vehicle_id (fk) and other information about what should happen, as well as the next completion date, days between each completion of the task (all repeated tasks) and other information

Work history table: contains job_history_id (pk), job_id (fk), date and comments

When the task completion button is pressed, it should create a new entry in the task history table with the current date, comments and task identifier

This is the script I'm trying to work.

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

. Set Rs = CurrentRs , , , ?

, ( ).

@HansUp, , , , ( , , )

@sarh , Recordset, , , , , - ?

@Matt 90% , ( , Access, , , ). , ( , ), , ? SQL, , Access/VBA,

Form for ticking off jobs as they are completed, calls code shown

+5
2

, ( ), . - , -

Me!SomeField = ...
DoCmd.RunCommand acCmdSaveRecord

, "SomeField", .

, :

1) SQL. , , , - :

Call CurrentDB.Execute( _
"UPDATE SomeTable SET SomeField = SomeValue WHERE SomeTableID = " & Me!SomeTableID, dbSeeChanges)

2) - Recordset Form , . , - ( , ):

Dim Rs as Recordset
Set Rs = Me.RecordsetClone 'make a reference copy of the form recordset
Rs.Bookmark = Me.Bookmark 'locate this recordset to the form current record
+2

. , CurrentRecord. , "", " ".

"/" , . 0, False . "complete_status". , . checkbox complete_status.

False/No complete_status --- . _ () () .

+1

More articles:


All Articles