Using VBA to update table 1

[AKMonkeyboy]

I have been using queries that run on the "AfterUpdate" event, but have a form that is opening as a subform in several other forms and to avoid having to create multiple versions of forms/queries I'd like to be able to update tables using fields found in my forms (not sure I'm saying that so it makes sense...). See if this does any better...

How could I take the following query and execute it from a form WITHOUT the references to the particular form?

UPDATE tblPrescriptions SET tblPrescriptions.Dose = [Forms]![FrmPatientMeds]![Dose], tblPrescriptions.Frequency = [Forms]![FrmPatientMeds]![Frequency], tblPrescriptions.Description = [Forms]![FrmPatientMeds]![Note], tblPrescriptions.Route = [Forms]![FrmPatientMeds]![Route]
WHERE (((tblPrescriptions.DrugName)=[Forms]![FrmPatientMeds]![Prescription]));

I have some code that allows updating using the rs.update, but I'm not sure how to apply that when I only want certain rows / fields updated.

Totally confused,

Monkey




Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[PHV]

If the event procedure is in FrmPatientMeds, you may consider something like this:

Dim strSQL As string
strSQL = "UPDATE tblPrescriptions" _
  & " SET Dose='" & Me![Dose] & "',Frequency='" & Me![Frequency] & "'" _
  & ",Description='" & Me![Note] & "',Route='" & Me![Route] & "'" _
  & " WHERE DrugName='" & Me![Prescription] & "'"
DoCmd.RunSQL strSQL

For any field defined as numeric get rid of the corresponding single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AKMonkeyboy]

GENIUS!

Thanks PHV - Have a star!



Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.