Hi all!

I am sorry if my question is stupid but I am new to access.

I am using access database (.mdb) that is controlled from excel. I wanted to
ask if it is possible that for any new record, the default value (string) of
one field will be the value of another field with a constant prefix?
If it is possible I will be glad to now how to do it.

Thanks in advance

Eli

When you say "controlled from Excel", do you mean that Excel uses automation
to run some features in Access?

Or does your Access database "point to" an Excel spreadsheet to get some
data?

Does your Access database have tables of its own, other than the Excel
"table"?

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"???" <@discussions.microsoft.com> wrote in message
news:B621CD62-9D37-4BAE-B468-(E-Mail Removed)...
> Hi all!
>
> I am sorry if my question is stupid but I am new to access.
>
> I am using access database (.mdb) that is controlled from excel. I wanted
> to
> ask if it is possible that for any new record, the default value (string)
> of
> one field will be the value of another field with a constant prefix?
> If it is possible I will be glad to now how to do it.
>
> Thanks in advance
>
> Eli

In the Default property of the field, try an expression along these lines:

= "CONSTVAL" & nz([FieldName], "")

Where CONSTVAL is the string of the constant you wish to prefix, and
FieldName is the name of the field you want to reference.

However, I would guess that on a new record the other field that you want to
reference may be null, which would leave you with just your constant value.

You may want to take the AfterUpdate event of the field you want to
reference... after this field is updated, set your other field with the
constant and value of the field (you would do this through controls rather
than the actual underlying fields)...

Private Sub Control1_AfterUpdate()
If Not IsNull(Me.Control1) Then
Me.Control2 = "CONSTVAL" & Me.Control1
End If
End Sub


One more thing to note... the data storage in a case like this suggests
storing the same data in more than one place, with is a bad normalization
practice. Perhaps you can use a calculated field in a query instead?

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



"אלי" wrote:

> Hi all!
>
> I am sorry if my question is stupid but I am new to access.
>
> I am using access database (.mdb) that is controlled from excel. I wanted to
> ask if it is possible that for any new record, the default value (string) of
> one field will be the value of another field with a constant prefix?
> If it is possible I will be glad to now how to do it.
>
> Thanks in advance
>
> Eli

It is not good database design to have one column dependent on another
column. Why do you need to do this?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"אלי" wrote:

> Hi all!
>
> I am sorry if my question is stupid but I am new to access.
>
> I am using access database (.mdb) that is controlled from excel. I wanted to
> ask if it is possible that for any new record, the default value (string) of
> one field will be the value of another field with a constant prefix?
> If it is possible I will be glad to now how to do it.
>
> Thanks in advance
>
> Eli

Use a query in places where you need that computed value. The SQL view of
that query can be:

SELECT *, "constant" & field AS computedValue
FROM tablename

and save it as query, which can be use as if it was a table, in most places.


Vanderghast, Access MVP


"אלי" <@discussions.microsoft.com> wrote in message
news:B621CD62-9D37-4BAE-B468-(E-Mail Removed)...
> Hi all!
>
> I am sorry if my question is stupid but I am new to access.
>
> I am using access database (.mdb) that is controlled from excel. I wanted
> to
> ask if it is possible that for any new record, the default value (string)
> of
> one field will be the value of another field with a constant prefix?
> If it is possible I will be glad to now how to do it.
>
> Thanks in advance
>
> Eli

Indeed, a default value is only used at creation time, strictly speaking,
not at consultation time by a human reading the data. But many times,
casually, 'default value' may mean 'if nothing is supplied, yet, this is the
value to be used'. If that is what the OP means, then a query may be
appropriate:

SELECT *, NZ( fieldW, "Constant" & refField)
FROM table

where refField is the field to be used for the computed "default" value
(in that casual definition of 'default value') and fieldW is the field name
which can hold a "over write" value, a potentially supplied value (but may
be not supplied by anyone, yet).

The problem is that a computed expression is NOT updateable, so it is likely
preferable to use FORM and form events to carry over the wanted logic
generally required in such casual definition of 'default value', since that
default value has to be eventually written over (updated) at consultation
time (rather than at creation time).


Vanderghast, Access MVP



"Jack Leach" <dymondjack at hot mail dot com> wrote in message
news:6AD18CB0-E86C-4ADD-82A9-(E-Mail Removed)...
> In the Default property of the field, try an expression along these lines:
>
> = "CONSTVAL" & nz([FieldName], "")
>
> Where CONSTVAL is the string of the constant you wish to prefix, and
> FieldName is the name of the field you want to reference.
>
> However, I would guess that on a new record the other field that you want
> to
> reference may be null, which would leave you with just your constant
> value.
>
> You may want to take the AfterUpdate event of the field you want to
> reference... after this field is updated, set your other field with the
> constant and value of the field (you would do this through controls rather
> than the actual underlying fields)...
>
> Private Sub Control1_AfterUpdate()
> If Not IsNull(Me.Control1) Then
> Me.Control2 = "CONSTVAL" & Me.Control1
> End If
> End Sub
>
>
> One more thing to note... the data storage in a case like this suggests
> storing the same data in more than one place, with is a bad normalization
> practice. Perhaps you can use a calculated field in a query instead?
>
> hth
> --
> Jack Leach
> www.tristatemachine.com
>
> "I haven''t failed, I''ve found ten thousand ways that don''t work."
> -Thomas Edison (1847-1931)
>
>
>
> "אלי" wrote:
>
>> Hi all!
>>
>> I am sorry if my question is stupid but I am new to access.
>>
>> I am using access database (.mdb) that is controlled from excel. I wanted
>> to
>> ask if it is possible that for any new record, the default value (string)
>> of
>> one field will be the value of another field with a constant prefix?
>> If it is possible I will be glad to now how to do it.
>>
>> Thanks in advance
>>
>> Eli