Using Query Results in Macros

  • Thread starter Thread starter Elliott
  • Start date Start date
E

Elliott

How can I build a macro that uses a query to update a
single field in a Form? I have built a macro that opens
the query, goes to the first record and GoToControl to get
to the specific field I want. I have tried using SetValue
to copy the value to the Form field, but I can't get it to
work. My SetValue statement is:
SetValue [Forms]![formname]![account1], [Query]!
[queryname]![account2]. I get an error message that I
have misspelled the query, even though it is valid and
open.
 
Elliott,

I have never before seen the type of syntax you are using in your
expression [Query]![queryname]![account2] and as far as I know it would
not be valid. In fact, you can't really refer to the value in a query
like this, even if Access recognised such syntax, because how does it
know which record in the query you are referring to.

When you say "field in a form", are you referring to a bound control or
an unbound control. If it is a bound control, I would suggest looking
at using an Update Query to adjust the value of the field that the
control is bound to, to the required value derived from your existing
query. If it is an unbound control, you may be best to directly refer
to the query in its Control Source, such as...
=DLookup("[FieldFromFirstRecordInYourQuery]","NameOfYourQuery")
 
Back
Top