Domain Aggregate Functions

[David]

I have been using the Dlookup type functions quite a bit with my database,
but I need to now write a data back to a table. Is there a domain aggregate
type command that I can use to write a value to a table?

Thanks,

Dave

 

[Anthony]

No such thing. But--

With only one line of code you can trigger an action (Update) query like
this:

DoCmd.RunSQL ("UPDATE MyTable SET MyField = 'MyValue' WHERE MyField =
'MyCriteria'")

If you are passing the value of a variable to the "SET" field, do it like
this:

DoCmd.RunSQL ("UPDATE MyTable SET MyField = '" & MyValue & "' WHERE MyField
= 'MyCriteria'")

If you are passing a value of a NUMERIC variable to the "SET" field, do it
like this: (Leave out the ' )

DoCmd.RunSQL ("UPDATE MyTable SET MyField = " & MyValue & " WHERE MyField =
'MyCriteria'")

Good luck

 

[Anthony]

Another thing you can do.

If you want to do this repeatedly throught your project, write a function.


Sub TestMyFunction

UpdateRecord "MyTable", "MyField", MyFieldValue, "MyCriteriaField",
"MyCriteria"

End Sub

Function UpdateRecord(myTable as String, myField as String, myFieldValue as
Variant, myCriteriaField as String, myCriteria as String)

DoCmd.RunSQL ("UPDATE " & myTable & " SET [" & myField & "] = " &
iif(isnumeric(myValue),"","'") & myValue & iif(isnumeric(myValue),"","'") &
" WHERE " & _
myCriteriaField & " = " &
iif(isnumeric(myCriteria),"","'") & myCriteria &
iif(isnumeric(MyCriteria),"","'"))

End Function

 

[David]

Ok thanks Anthony. That will do the trick.

Another thing you can do.

If you want to do this repeatedly throught your project, write a function.


Sub TestMyFunction

UpdateRecord "MyTable", "MyField", MyFieldValue, "MyCriteriaField",
"MyCriteria"

End Sub

Function UpdateRecord(myTable as String, myField as String, myFieldValue
as Variant, myCriteriaField as String, myCriteria as String)

DoCmd.RunSQL ("UPDATE " & myTable & " SET [" & myField & "] = " &
iif(isnumeric(myValue),"","'") & myValue & iif(isnumeric(myValue),"","'")
& " WHERE " & _
myCriteriaField & " = " &
iif(isnumeric(myCriteria),"","'") & myCriteria &
iif(isnumeric(MyCriteria),"","'"))

End Function

I have been using the Dlookup type functions quite a bit with my database,
but I need to now write a data back to a table. Is there a domain
aggregate type command that I can use to write a value to a table?

Thanks,

Dave