Bill said:
I forgot to add the SQL
UPDATE MasterData SET MasterData.resolved_date =
forms!frmMain!txtresolved_date
WHERE (((MasterData.CounterID)=[forms]![frmMain]![CounterID]));
Bill said:
I currently have a query that is used to update two fields on my main
form, I need to add several more fields to the form and i do not want to
have to create and update query for 8 more fields. What I would like to
know is how to add the (below) SQL to a after_update event of each of
the fields with having to run separate queries.
Thank you
Bill
what if you create a function that receives the fieldname in the table
that you want to change and then you use something like
private function fSQL(byval strfield, byval strOpenForm as string,
byval strControl as string) as string
dim strsql as string
strsql = "UPDATE MasterData SET MasterData.[" & strField &
"]=[Forms]!["&strOpenForm & "]![" & strControl &"]"
strsql = strsql & <...your filter>
fSQL=strsql
end function
then you could call the function in the after_update event of whatever
control you wanted. You'd just pass a fieldname to the SQL and then
execute it.
dim strExecSQL as string
strExecSQL = fSQL("MyField", Me.Name, Me.ActiveControl)
dbengine(0)(0).Execute strExecSQL
In a nutshell, you need a function to return the proper SQL statement,
since you won't have the QBE grid building it for you. If you build a
couple and copy and paste the SQL statements to notepad or whatever,
you should be able to figure out where you have to stick in your
variables to build a generic SQL statement. then you can assign the
result of that to a string variable and execute it. (Don't test this
on live data... if it goes wrong, you could be sorry, since
CurrentDb.Execute doesn't give any warnings.)
but if you test it on a really small nonsense dataset, you should be ok.
