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>
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)"
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" <
[email protected]>
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)"
<
[email protected]>
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" <
[email protected]>
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
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" <
[email protected]>
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
