Help reqd. with the Macro "SetValue" property.

I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the Combo's
"AfterUpdate" property.

This works very well for one field, but how
can I use this one Macro to fill in more than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here are
filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I can
insert values into more than one field.

Frank

In a macro group, you can have multiple macros. From the view menu, turn on
the "Macro Names" column and give your macro a name.

Now you can add additional Actions, by adding "..." (3 dots without the
quotes) in the name column, and SetValue again with the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't written a macro in well over 10 years, so
I'm not sure if the 3 dots are still used. In Code it would be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote in message
...
I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the Combo's
"AfterUpdate" property.

This works very well for one field, but how
can I use this one Macro to fill in more than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I can
insert values into more than one field.

Frank

Thank you very much. I will try this.
Regards, Frank


"Arvin Meyer [MVP]" wrote in
message
...
In a macro group, you can have multiple
macros. From the view menu, turn on the
"Macro Names" column and give your macro a
name.

Now you can add additional Actions, by
adding "..." (3 dots without the quotes) in
the name column, and SetValue again with
the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't written
a macro in well over 10 years, so I'm not
sure if the 3 dots are still used. In Code
it would be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote in
message
...
I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the
Combo's
"AfterUpdate" property.

This works very well for one field, but
how
can I use this one Macro to fill in more
than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here
are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I
can
insert values into more than one field.

Frank

"Arvin Meyer [MVP]" wrote in
message
...
In a macro group, you can have multiple
macros. From the view menu, turn on the
"Macro Names" column and give your macro a
name.

Now you can add additional Actions, by
adding "..." (3 dots without the quotes) in
the name column, and SetValue again with
the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't written
a macro in well over 10 years, so I'm not
sure if the 3 dots are still used. In Code
it would be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote in
message
...
I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the
Combo's
"AfterUpdate" property.

This works very well for one field, but
how
can I use this one Macro to fill in more
than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here
are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I
can
insert values into more than one field.

Frank

This method works but now I need some way to
send a value to a control on an unrelated
form, and the "setvalue" function does not
work.

Is there something similar that will do the
job?

Frank


"Arvin Meyer [MVP]" wrote in
message
...
In a macro group, you can have multiple
macros. From the view menu, turn on the
"Macro Names" column and give your macro a
name.

Now you can add additional Actions, by
adding "..." (3 dots without the quotes) in
the name column, and SetValue again with
the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't written
a macro in well over 10 years, so I'm not
sure if the 3 dots are still used. In Code
it would be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote in
message
...
I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the
Combo's
"AfterUpdate" property.

This works very well for one field, but
how
can I use this one Macro to fill in more
than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here
are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I
can
insert values into more than one field.

Frank

Define what you mean by ' the "setvalue" function does not work' statement.
SetValue is used to write a specific value into a control on an open form.
--

Ken Snell
MS ACCESS MVP



"Frank Martin" wrote in message
...
This method works but now I need some way to send a value to a control on
an unrelated form, and the "setvalue" function does not work.

Is there something similar that will do the job?

Frank


"Arvin Meyer [MVP]" wrote in message
...
In a macro group, you can have multiple macros. From the view menu, turn
on the "Macro Names" column and give your macro a name.

Now you can add additional Actions, by adding "..." (3 dots without the
quotes) in the name column, and SetValue again with the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't written a macro in well over 10 years,
so I'm not sure if the 3 dots are still used. In Code it would be
simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote in message
...
I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the Combo's
"AfterUpdate" property.

This works very well for one field, but how
can I use this one Macro to fill in more than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I can
insert values into more than one field.

Frank

I can insert a value in an open form OK with
the macro SetValue funtion, but not into an
unopened form or query. Now I need to do
it for an unopened form or query.

The reason is to insert a value (boolean)
into a field of the lookup macro so that I
can filter out values I have already used.

Frank


"Ken Snell (MVP)"
wrote in
message
...
Define what you mean by ' the "setvalue"
function does not work' statement. SetValue
is used to write a specific value into a
control on an open form.
--

Ken Snell
MS ACCESS MVP



"Frank Martin" wrote in
message
...
This method works but now I need some way
to send a value to a control on an
unrelated form, and the "setvalue"
function does not work.

Is there something similar that will do
the job?

Frank


"Arvin Meyer [MVP]" wrote in
message
...
In a macro group, you can have multiple
macros. From the view menu, turn on the
"Macro Names" column and give your macro
a name.

Now you can add additional Actions, by
adding "..." (3 dots without the quotes)
in the name column, and SetValue again
with the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't
written a macro in well over 10 years, so
I'm not sure if the 3 dots are still
used. In Code it would be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote
in message
...
I have to append a table with some
records
derived from a query.

I can convert a field to a Combo and
then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd
fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the
Combo's
"AfterUpdate" property.

This works very well for one field, but
how
can I use this one Macro to fill in more
than
one field? (there are three.)

There are two fields at the bottom of
this
Macro (the "SetValue" case), which, here
are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that
I can
insert values into more than one field.

Frank

You cannot insert a value into an unopened form. You also cannot insert a
value into an unopened query. You can filter a query so that it will limit
its records when it runs, but this likely will require VBA programming to
do. You can filter a form as it opens (the WHERE argument for the OpenForm
action).

Can you tell us specifically what you are wanting to do with this value --
step-by-step would be good -- so that we can assist you with a solution?
--

Ken Snell
MS ACCESS MVP






"Frank Martin" wrote in message
...
I can insert a value in an open form OK with the macro SetValue funtion,
but not into an unopened form or query. Now I need to do it for an
unopened form or query.

The reason is to insert a value (boolean) into a field of the lookup macro
so that I can filter out values I have already used.

Frank


"Ken Snell (MVP)" wrote in message
...
Define what you mean by ' the "setvalue" function does not work'
statement. SetValue is used to write a specific value into a control on
an open form.
--

Ken Snell
MS ACCESS MVP



"Frank Martin" wrote in message
...
This method works but now I need some way to send a value to a control
on an unrelated form, and the "setvalue" function does not work.

Is there something similar that will do the job?

Frank


"Arvin Meyer [MVP]" wrote in message
...
In a macro group, you can have multiple macros. From the view menu,
turn on the "Macro Names" column and give your macro a name.

Now you can add additional Actions, by adding "..." (3 dots without the
quotes) in the name column, and SetValue again with the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't written a macro in well over 10
years, so I'm not sure if the 3 dots are still used. In Code it would
be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote in message
...
I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the Combo's
"AfterUpdate" property.

This works very well for one field, but how
can I use this one Macro to fill in more than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I can
insert values into more than one field.

Frank

I have to enter creditor payments into the
general ledger.

I do this by having a combo box in a ledger
field which calls up the previously-entered
"SuppPayments" query, which contains all
payments recorded as cheques are drawn.

They are not entered into the ledger until
they appear on the bank statement.
Therefore I have then to consult the
"SuppPayments" query (via the combo above)
and insert the statement values into the
ledger with the "SetValue" macro.

My problem is to filter from the
"SuppPayments" query all the transactions
that have now been entered into the ledger so
that they no longer appear in the combo
(there a several thousand.)

I seek to do this by inserting a value (
Boolean or otherwise) into the "SuppPayments"
query *from the ledger combo* so that this
can be the basis of a filter.

Regards, Frank


PS The tables relating to the ledger &
SuppPayments are not related, and it might be
very difficult to make them so.














"Ken Snell (MVP)"
wrote in
message
...
You cannot insert a value into an unopened
form. You also cannot insert a value into
an unopened query. You can filter a query
so that it will limit its records when it
runs, but this likely will require VBA
programming to do. You can filter a form as
it opens (the WHERE argument for the
OpenForm action).

Can you tell us specifically what you are
wanting to do with this value --
step-by-step would be good -- so that we
can assist you with a solution?
--

Ken Snell
MS ACCESS MVP






"Frank Martin" wrote in
message
...
I can insert a value in an open form OK
with the macro SetValue funtion, but not
into an unopened form or query. Now I
need to do it for an unopened form or
query.

The reason is to insert a value (boolean)
into a field of the lookup macro so that I
can filter out values I have already used.

Frank


"Ken Snell (MVP)"
wrote
in message
...
Define what you mean by ' the "setvalue"
function does not work' statement.
SetValue is used to write a specific
value into a control on an open form.
--

Ken Snell
MS ACCESS MVP



"Frank Martin" wrote
in message
...
This method works but now I need some
way to send a value to a control on an
unrelated form, and the "setvalue"
function does not work.

Is there something similar that will do
the job?

Frank


"Arvin Meyer [MVP]" wrote in
message
...
In a macro group, you can have multiple
macros. From the view menu, turn on the
"Macro Names" column and give your
macro a name.

Now you can add additional Actions, by
adding "..." (3 dots without the
quotes) in the name column, and
SetValue again with the value of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't
written a macro in well over 10 years,
so I'm not sure if the 3 dots are still
used. In Code it would be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin"
wrote in message
...
I have to append a table with some
records
derived from a query.

I can convert a field to a Combo and
then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd
fields.

But this will conflict with all the
old
values in this field.

Therefore I have to use a Macro via
its
"SetValue" property, triggered by the
Combo's
"AfterUpdate" property.

This works very well for one field,
but how
can I use this one Macro to fill in
more than
one field? (there are three.)

There are two fields at the bottom of
this
Macro (the "SetValue" case), which,
here are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so
that I can
insert values into more than one
field.

Frank

What I recommend is that you add (if it's not already there) a field in the
table that holds the records that are filling the SuppPayments query, and
this field would be a Boolean field that is True if it's been on a
statement, or False if it's not. You'd then use this field as a criterion in
your combobox's RowSource query to filter out SuppPayments records that are
on statements.

You then may want a form that allows you to see records from the
SuppPayments data and let's you edit the data in that Boolean field to show
that the record is now on a statement. Let this form do the updates to the
table so that your SuppPayments combobox query works as you wish.
--

Ken Snell
MS ACCESS MVP




"Frank Martin" wrote in message
...
I have to enter creditor payments into the general ledger.

I do this by having a combo box in a ledger field which calls up the
previously-entered "SuppPayments" query, which contains all payments
recorded as cheques are drawn.

They are not entered into the ledger until they appear on the bank
statement. Therefore I have then to consult the "SuppPayments" query (via
the combo above) and insert the statement values into the ledger with the
"SetValue" macro.

My problem is to filter from the "SuppPayments" query all the transactions
that have now been entered into the ledger so that they no longer appear
in the combo (there a several thousand.)

I seek to do this by inserting a value ( Boolean or otherwise) into the
"SuppPayments" query *from the ledger combo* so that this can be the basis
of a filter.

Regards, Frank


PS The tables relating to the ledger & SuppPayments are not related, and
it might be very difficult to make them so.














"Ken Snell (MVP)" wrote in message
...
You cannot insert a value into an unopened form. You also cannot insert a
value into an unopened query. You can filter a query so that it will
limit its records when it runs, but this likely will require VBA
programming to do. You can filter a form as it opens (the WHERE argument
for the OpenForm action).

Can you tell us specifically what you are wanting to do with this
value -- step-by-step would be good -- so that we can assist you with a
solution?
--

Ken Snell
MS ACCESS MVP






"Frank Martin" wrote in message
...
I can insert a value in an open form OK with the macro SetValue funtion,
but not into an unopened form or query. Now I need to do it for an
unopened form or query.

The reason is to insert a value (boolean) into a field of the lookup
macro so that I can filter out values I have already used.

Frank


"Ken Snell (MVP)" wrote in message
...
Define what you mean by ' the "setvalue" function does not work'
statement. SetValue is used to write a specific value into a control on
an open form.
--

Ken Snell
MS ACCESS MVP



"Frank Martin" wrote in message
...
This method works but now I need some way to send a value to a control
on an unrelated form, and the "setvalue" function does not work.

Is there something similar that will do the job?

Frank


"Arvin Meyer [MVP]" wrote in message
...
In a macro group, you can have multiple macros. From the view menu,
turn on the "Macro Names" column and give your macro a name.

Now you can add additional Actions, by adding "..." (3 dots without
the quotes) in the name column, and SetValue again with the value
of:

ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnWhatever]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](2)

Add as many as you need. I haven't written a macro in well over 10
years, so I'm not sure if the 3 dots are still used. In Code it would
be simpler:

Me.[LedgerTxns].[Form]![TxnWhatever] =
Me.[LedgerTxns].[Form]![Reference].[Column](2)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com



"Frank Martin" wrote in message
...
I have to append a table with some records
derived from a query.

I can convert a field to a Combo and then
normally use this with the expression
"[Reference].[Column].(2)" in the
ControlSource to fill in the reqd fields.

But this will conflict with all the old
values in this field.

Therefore I have to use a Macro via its
"SetValue" property, triggered by the Combo's
"AfterUpdate" property.

This works very well for one field, but how
can I use this one Macro to fill in more than
one field? (there are three.)

There are two fields at the bottom of this
Macro (the "SetValue" case), which, here are filled as
shown:


ITEM:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![TxnDescription]

EXPRESSION:
[Forms]![FrmLedgerAccnts]![LedgerTxns].[Form]![Reference].[Column](1)


Please help me fill this fields so that I can
insert values into more than one field.

Frank