Help creating a VBA Code to run an Update Query

[griztkojr]

Hello!

I don't know how to code in VBA but many have suggested that its the best way to go with what I am trying to do. I have a form named frm_Employee that captures employee work information. In this form there is a subform named subfrm_EEJobHist. When I want to create a new job I call a form named frm_AddNewJob. I need to create a button that when I am adding a new record in the frm_AddNewJob, the query grabs all the prior jobs from that employee job history table and adds the current date to the field [JobEndDate] and then adds the new job.



Hope it makes sense!

Thank you

Griztko

[ranman256]

youd build an append query. (say its called: qaAddNewJob)
using the key in the master form, grab a record from the history, and append the fields you want to the target table.
add Date() to fill in the date field.(in the query)

the code to run the query at button click:
docmd.openquery "qaAddNewJob"

[griztkojr]

youd build an append query. (say its called: qaAddNewJob)
using the key in the master form, grab a record from the history, and append the fields you want to the target table.
add Date() to fill in the date field.

the code to run the query at button click:
docmd.openquery "qaAddNewJob"

Hi ranman. Thank you for your prompt reply. So, I created a query where "criteria' is the key from the master form and JOBENDDATE is null but i don't now how to tell the query to update the field JOBENDDATE to Now(). How do i go about this?

[June7]

Put Now() in the UpdateTo row under the relevant field. Or use only VBA instead of query object.

CurrentDb.Execute "UPDATE EEJobHist SET JobEndDate = Now() WHERE JobEndDate Is Null AND EmpID = " & Me.EmpID;

[kd2017]

Put Now() in the UpdateTo row under the relevant field. Or use only VBA instead of query object.

CurrentDb.Execute "UPDATE EEJobHist SET JobEndDate = Now() WHERE JobEndDate Is Null AND EmpID = " & Me.EmpID;

Not to derail or hi-jack the thread but this leads me to a question I've wondered about before: Is there any reason I should be doing something like

Dim db AS DAO.Database
Set db = CurrentDb
db.execute "...", dbFailOnError
db.close
set db = nothing

vs the simple one liner you proposed:

CurrentDb.execute "...", dbFailOnError

[June7]

I don't bother creating variables when I use them only once.

If you prefer to not repeatedly type CurrentDb in favor of just db then establish the object variable, otherwise I am not aware of advantage. But that could be my amateur ignorance showing.

[griztkojr]

I don't bother creating variables when I use them only once.

If you prefer to not repeatedly type CurrentDb in favor of just db then establish the object variable, otherwise I am not aware of advantage. But that could be my amateur ignorance showing.

June,

Thank you for your help. The query is working perfectly. My next issue is this, when I am modifying a record, i don't want the query to run. How can i do this? This is how i coded the button.

Private Sub btnSave_Click()
On Error Resume Next
DoCmd.SetWarnings (False)
DoCmd.OpenQuery "qry_EndDateUpdate"
DoCmd.SetWarnings (True)
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close


End Sub

[June7]

Conditional code:

If Me.NewRecord Then
DoCmd.SetWarnings (False)
DoCmd.OpenQuery "qry_EndDateUpdate"
DoCmd.SetWarnings (True)
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

[griztkojr]

You are AWESOME!