Cbo box Limit to list = No write question

[Hugh self taught]

Hello All you marvelous brain boxes,

I have a form with a bound cbo box EvtNumber & "limit to list =No". A hidden
txt box "=[EvtNumber].[Column](1)". The underlying table is a temporary data
input table for calculations then it will be cleared after the relevant data
is written to the relevant tables.

As you have probably guessed my hidden text box doesn't update when I type a
value in the cbo which is not already in the table until the form is
reloaded, even though I save the record so the value is now in the table &
then requery the form and/or control.

I could use limit to list = yes but I cannot get the Not in List event to
accept an update instead of an Insert. At this point in my processing I have
already created the new record & need to add a new event number to that
record.

Option 1) would be to have the limit to list = yes & have the not in list
accept my table update with the new value. It may be possible & my syntax is
just wrong, but I keep getting error messages indicating I can't do what I'm
trying to do. I don't want to create another temporary table just for event
numbers which are already included in this table.

Option 2) would be a workaround to get the hidden text box to show the value
of EvtNumber.Column(1)

Any suggestions greatly appreciated

 

[Arvin Meyer [MVP]]

Have a look at the code at:

http://www.datastrat.com/Code/NotInListCode.txt

and the demo of that code at:

http://accessmvp.com/Arvin/NotInListDemo.zip

 

[Hugh self taught]

Hello Arvin,

Thanks for responding. I've replied twice & had the web site respond as
temporary unavailable every other time & still my previous replies have not
come through.

To my issue. I have subsequently discovered that Me.cboMyComboBox.Value
gives me the cbo box value which fulfills my 2nd option.

The table where I do my lookup is the same table that I'm writing to & is a
temp table to perform calculations from then write the results to the
necessary tables before clearing it. Hence my problem with Limit to List as
that seems to require that a record be added & at this point I've already
added a new record & am busy updating it with data when I need to add a new
Event number within the competition.

I realize this temp table does not represent a normalized database but it's
only for this single purpose & I'm loathe to create a bunch of tables just
for a temp means to perform a sequence of calculations.

If there is a way to update an existing record with the value of the cbo
with Limit to List = Yes & have acDataErrAdded respond in acceptance then I'd
be glad to know. In the interim I'll make use of the workaround whenever the
need arises.

Have a look at the code at:

http://www.datastrat.com/Code/NotInListCode.txt

and the demo of that code at:

http://accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

Hello All you marvelous brain boxes,

I have a form with a bound cbo box EvtNumber & "limit to list =No". A
hidden
txt box "=[EvtNumber].[Column](1)". The underlying table is a temporary
data
input table for calculations then it will be cleared after the relevant
data
is written to the relevant tables.

As you have probably guessed my hidden text box doesn't update when I type
a
value in the cbo which is not already in the table until the form is
reloaded, even though I save the record so the value is now in the table &
then requery the form and/or control.

I could use limit to list = yes but I cannot get the Not in List event to
accept an update instead of an Insert. At this point in my processing I
have
already created the new record & need to add a new event number to that
record.

Option 1) would be to have the limit to list = yes & have the not in list
accept my table update with the new value. It may be possible & my syntax
is
just wrong, but I keep getting error messages indicating I can't do what
I'm
trying to do. I don't want to create another temporary table just for
event
numbers which are already included in this table.

Option 2) would be a workaround to get the hidden text box to show the
value
of EvtNumber.Column(1)

Any suggestions greatly appreciated

.