Update Query Follow-up

  • Thread starter Thread starter Luther
  • Start date Start date
L

Luther

This is in response to the person who answered. It worked
great, but is there any way that you can combine the 2
update statements in ONE QUERY. Anything?????

Thanks again.
 
You should post as the reply to the answer post rather than as a new thread.
The original respondent is more likely to watch the original thread in case
you need further help.

Since I can find the original thread, I can only answer with the info on
this thread.

Generally in JET, you cannot combine 2 Update statements into one. However,
you can write a short sub with 2 VBA statements, each to execute 1 Update
SQL String.
 
Thank you for responding. I am not too familiar with VBA
and will need some hand holding; can you give me an
example of the code that I need? Thanks.
 
Create a VBA Standard Module (check your Access books) and enter code
similar to:

***Untested***
Public Sub Run2UpdateSQLs()
DoCmd.RunSQL {Your first SQL string}
DoCmd.RunSQL {Your second SQL String}
End Sub
******

There are other more efficient methods but this is the easiest code to do.

You can then run the Sub which will execute the 2 SQL Strings.
 
Thank you very much for your help....Now I have another
question. With the update queries that I created, is it
also possible to have a Date field update itself with the
current date when these queries are run. I can do it in
forms, when you actually do something to that record, but
I kind of want a global thing when the table is updated
with all these codes.
Looking forward to hear from you.
 
You can use an SQL like:

UPDATE YourTable
SET DateField = Now()
WHERE {criteria if required}

See Access Help on UPDATE Query / SQL
 
Hi,

How would I incorporate this SQL in a parameter query to
update the date field when records are changed/updated.
The parameter query opens a form to display the records
found.

Thanks

1. I have a parameter query that opens a form
 
Post the SQL String of your current Update Query and the Date Field name of
the Field you want to update.
 
This is the query that find the records. I want to put in
an update query in there so that when the records are
changed/updated, the date field (Last_Updated) is stamped
with the current date.

Private Sub Find_Plans_Click()
On Error GoTo Err_Find_Plans_Click

Dim stDocName As String

stDocName = "Find Plans"
DoCmd.OpenForm stDocName, acNormal, acEdit

Exit_Find_Plans_Click:
Exit Sub

Err_Find_Plans_Click:
MsgBox Err.Description
Resume Exit_Find_Plans_Click
End Sub
 
This is also the parameter query SQL. the post below just
opens the form...sorry.

SELECT HIPlanNames.PlanName, HIPlanNames.STATE,
HIPlanNames.FFS, HIPlanNames.HMO, HIPlanNames.PPO,
HIPlanNames.POS, HIPlanNames.DEFAULT, HIPlanNames.SOURCE,
HIPlanNames.ENROLLMENT, HIPlanNames.STAFF,
HIPlanNames.IPA, HIPlanNames.NETWORK, HIPlanNames.GROUP,
HIPlanNames.PPO_IND, HIPlanNames.MEDICARE,
HIPlanNames.MEDICAID, HIPlanNames.LastUpdated
FROM HIPlanNames
WHERE (((HIPlanNames.PlanName) Like "*" & [Enter a few
letters of the plan name:] & "*"))
ORDER BY HIPlanNames.PlanName;
 
You cannot incorporate an update into a Select Query. The Queru must be
either a Select Query or an Update Query, NOT both.

If you want UpdateTimeStamp, you should use the Form_AfterUpdate Event.

Check Access VB Help on the AfterUpdate Event of a Form.
 
Back
Top