Assign value from another table if field is null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, and thanks in advance :-). Maybe I did this the wrong way, not sure. I
have a form that pops up when I open the database that sets a default value
called MarketID.

The user chooses the market from a form and when they click the command
button, a new table is created (tblSettings) with the MarketID. All new
records added to the database should have this MarketID until it is changed
by going to the form again.

So far so good, my problem is this, on my forms, how do I lookup that
MarketID and make it the default value for the field MarketID if the value is
null? And, I don't want to change any of the existing values of this field,
it should only affect new records.

Thanks for your time,
Deb
 
Since you have a startup form that sets this value, it would better to
validate the user's selection and prevent them from continuing until they
have made a valid selection.

Steve
 
I'm not entirely clear on what your application is doing. From your
description, it sounds like you are creating new tables ("...a new table is
created..."). This could make for some serious maintenance headaches,
creating tables on the fly. If you'll provide a bit more specific
description about your underlying data/domain, the folks here in the
newsgroup may be able to offer more specific suggestions.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Gladly. I'm basically creating an orders database, and every order that is
entered in pertains to a specific period of time (about 2 weeks), we're
calling it Market.

Every order has a market and rather than having the user enter the market
with every order (because there may be up to 100 new orders per market), I am
creating a table that stores the MarketID until it is changed. That way every
new order will get the same Market (until changed) which makes it easier for
data entry.

When you enter a new order, I would like to be able to pull the MarketID
stored in my table as a default value, but only on new orders. Did that help
any?

Thanks,
Deb
 
I acutally found what i'm looking for on the boards but having problems with
this second part here***:

"If you want the default value to persist if frm2 is closed and re-opened,
or if the database is closed and re-opened, you need to

(a) put code in cboSetTheValue's AfterUpdate event to store the value
somewhere, e.g. in a "Settings" table in the database or in an .ini file.

***(b) have the code in frm2's Load or Open event handler retrieve the
stored value"
 
Another way to have a "default value" persist might be to change the
control's default value, something like:

Me!txtYourControl.DefaultValue = "Market2"

in code behind one of the form's events (?AfterUpdate, or possibly even
BeforeUpdate?).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Default values only apply to newly-created records. If you were to go back
in and delete the value on an existing record, the default value would not
apply.
 
Back
Top