Populating Form Field Based on Other Field

[fritterdog]

A simplified version of my database and question follows.

If have 3 tables. One (USER_TABLE) with user_id (key) and user_name, the other (USER_OBJ_TABLE) with user_id and object_id (both key), the third (OBJ_TABLE) with object_id (key). Note that one object may have multiple users and multiple users may use same object.

I have a form where I want to enter a new object and at the same time enter the owner/user(s). I have a main form (based on OBJ_TABLE) containing the object_id field and a subform (based on USER_OBJ_TABLE) that allows me to enter 1 or more users (assuming users already in USER_TABLE).

What I want to do, is allow the data entry person to be able to select the user by name (combo box based on the already populated USER_TABLE!name) and have the user_id automatically populate the text field called ID that is used entering the user_id on the USER_OBJ_TABLE!user_id. (i.e. I want to query the USER_TABLE and find the corresponding USER_ID for the selected user_name and use this value in the USER_OBJ_TABLE)

I can sort of make this work by using a combo box on the subform for the ID field (USER_OBJ) and putting a query in the row_source data property for that field. The problem is that this requires the data entry person to click in that box and select the one and only choice.

There has got to be a way to based on the value in one box (i.e. name) query a table, find the corresponding value (i.e. userid) and use this value to populate a new record in another table.

Thanks in advance.

 

[skittle123]

IN combo box on the subform for the ID field (USER_OBJ)
set the vllue by refereing to the value that is on the main form like so

(where Me refers to subform)
me.<idFldName>.value = Forms![&quot;MainformName&quot;].Forms.<objectname>.value

let me know if I havent answered your question

 

[fritterdog]

Thanks but thisis not quite what I meant.

You suggested

me.<idFldName>.value = Forms!&quot;MainformName&quot;].Forms.<objectname>.value

What I am looking for is something more like
me.<idFldName>.value =
select user.userid from user where user.username==
Forms![&quot;MainformName&quot;].Forms.<objectname>.value

This query is essentially what works if I use a combo box but I do not want to have to go into that box and always select from a drop down... The select statement will always return a single value.

Thanks again.