Setting a default value from a specific record on a table

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Can I set default value during form design in an input box
on a form based upon a field from a specific record?

On my form I would like to place a value from an email
address field which comes from a record with default in
the name field which is the primary key field. SQL would
look something like SELECT EmailAddress FROM TEACHER WHERE
TeacherName = "default"

I couldn't figure out how I could do this in the Control
Source property using Expression Builder, so how do I do
it?
 
Hi,


No. A default value, as intended for a table, is ONLY used at creation
time of the record. At that exact precise moment, the end user has not been
able to see the record yet (it is in the process to be created, it cannot
have been displayed yet), and so, the only "values" other fields can got is
their own default values. The process involving those constants would likely
produce a constant, which is to be used as "default" value.

A possible solution consist to change the default value at the moment we
save a record, in PREVISION that a new record could be then created, a
possibility more than a certitude. You can then use the AfterUpdate event of
the record (or more precisely of the form, in case of DAO).

Consider to use a NULL value if the value is not known.

A fixed default value for a primary key does not make much sense, since
a primary key cannot have duplicated values. A pk cannot have a NULL value.
A pk has to be known. Use a surrogate key if the natural key is possibly not
known at creation time. Jet allows a UNIQUE constraint to got multiple NULL
values, so, using a surrogate as pk and leaving the natural key with just a
UNIQUE index can be of some use (but cannot be migrated to MS SQL Server
2000 which does not allow multiple NULL values under a UNIQUE constraint).



Hoping it may help,
Vanderghast, Access MVP
 
so in the TEACHER table there is a record where the actual value in the name
field is "default" (rather than "John Smith" or "Mary Brown")? *if so*, you
can probably set the default email value in the form via code, i'm thinking
maybe the BeforeInsert event.
create a module level variable (put it at the top of the VBE screen, before
the first procedure), as

Dim strDefaultEmail As String

in the form's OnOpen event procedure add the following

Private Sub Form_Open(Cancel As Integer)

strDefaultEmail = Nz(DLookUp("EmailAddress","TEACHER", _
"TeacherName = 'default'"), "")

End Sub

in the form's BeforeInsert event procedure, add the following

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!EmailAddress = strDefaultEmail

End Sub

hth
 
Back
Top