Text Field default value based on function...

[TonyK]

Hello fellow Access-users,

I am struggling with the following problem:

I have a Table wich has an id, code and name of a person
I would like to insert records in another Table called History (which
has a person_id in it (which is linked to the id from the person
Table))
The person code is gained from a function which detects the current
user on the PC
Now I have a form and would like to automatically display the name of
the person...
And insert the id of that person into the History Table (person_id)
when the form is filled and I click on "next" in the navigation (which
inserts a record).

I have looked around, searched and tried several ways to get me to
accomplish this, but no luck sofar. I really would appreciate some help
(how would one normally tackle this "problem"?).

(I really can't believe that the textfield object in the form is soo
limited not even speaking of the limitations of the default value).

Greets,

Tony

[pietlinden]

Keep looking. you haven't found it yet.
http://www.mvps.org/access/api/api0008.htm

[pietlinden]

Keep looking. you haven't found it yet.
http://www.mvps.org/access/api/api0008.htm

Funny... when I put the code in the module as instructed and then set
the control's default value to
=fOSUserName()

it works a charm.

I really can't believe you rant that much without looking around. did
you search the NG for an answer? Google for it?

[TonyK]

Hi,

Yes I am using that function (excuse me for "ranting"...lol) and yes
you can set that for the default value, but ...it's not that specific
value I want displayed. What the function displays is the code of the
person, I would like to display the name of the person (from the same
Table) and would want to use the id value (from the same Table) when a
new record is inserted to link it to the other Table that has a
matching id value (where the main record is inserted).

[Rog]

Put a DLookup in the default value, something like
=DLookUp("ID","PersonTable","[PersonName]='" & fOSUserName() & "'")

[TonyK]

Thnx Rog, but this gives me a syntaxis error (says it's missing an
operand/operator).
(are you sure you can use a DLookup in the default value position?)

[Rog]

Yes, it works, I checked (before posting my answer) :-)
You may be missing a quotation mark or apostrophe somewhere.
The condition (the third argument) of the dlookup function should be
spelled as follows:
quote [personname] equals apostrophe quote ampersand fOSUserName()
ampersand quote apostrope quote.

[Rog]

Yes, I tried it (before posting my answer) :-)
You may be missing an apostrope or quotation mark somewhere.
The criteria (the third argument of the dlookup function) should be
spelled:
quote [personname] equals apostrophe quote ampersand fOSUserName()
ampersand quote apostrope quote.

[TonyK]

I found out what was the problem with the DLookup, for some reason I
need to use ; instead of comma's in my code...thnx!

The only problem left is that I need to use the id value for inserting
a new record, but would like to display the name value (the id needs to
match the id value in the main table I store the records in). Is there
a way to accomplish this?

Thnx in advance for your help!

[Rog]

The semicolon is due to your regional setting in Windows. You will
need semicolons instead of commas to separate the arguments in all
functions.

If it's the username you want to display, just add an unbound textbox
to your form (make it disabled, locked, and flat) and enter
=fosusername() as the control source.

Groetjes,
Rog

[TonyK]

Hmm...ok, so much for regional settings..lol

If I would just would want to display it, then that would be a viable
solution...however how would Access know what id to insert when
creating a new record?

Groeten terug ;)

Tony

[Rog]

In that case, make it a bound textbox, i.e. the control source should
be the name of a field in the table or query which serves as the
recordsource of your form. Enter the lookup function as described
above as the default value of that textbox.

[TonyK]

Well, using a combobox seems to be the only solution (since you can
just hide the id and display the name, while keeping the id as the
related value). But regarding the textfield we seem to be back at the
start. When I select the id field from the History Table in the control
source (since that's where the id from the person Table is related to),
it will only want to display an id-value in the textfield. I don't see
a way to tell Access to display the name, but keep track of the id when
it comes to inserting a record...

[Rog]

You can use two text boxes, one hidden and bound to the ID field, one
visible and unbound to display the name.

[TonyK]

Ok, thnx will give that a try