Populating a text box on a form with the value from another table record

  • Thread starter Thread starter Rob Nicholson
  • Start date Start date
R

Rob Nicholson

Consider a simple two table scenario like this:

Requests Table:
RequestID: AutoNumber (Unique ID)
PersonID: Number (Reference to record in People table)

People Table:
PersonID: AutoNumber (Unique ID)
Name: Text (Name of person)
Email: Text (Their email address)

A simple form is requested for the request table whereby the ID is shown along with a combo box to allow [Requests]![PersonID] to be filled in.

However, we'd like a non-editable textbox below the person combo box that shows the email field when a specific person is selected. This is, in effect, a lookup of a single record in [People] using the [Requests]![PersonID] field.

I'm suspecting it's a computed text box. I can do it using VBA to fire of an ADO/DAO query, maybe even using an Access query.

But is there a way to do it without having to resort to code?

Thanks, Rob.
 
Rob,

Yes, there is.

To begin with: You need an unbound text box on the form to display the e-mail address, with its Data tab properties Enabled > No and Locked > Yes (so the user cannot change it).
Then, selecting the combo box, go to properties, tab Events, and in the On Change event select Macro builder, and create a simple macro with one action as follows:
Action: SetValue
Arguments: Item: Forms!FormName!ControlName
Expression: DLookUp("","People","[People]![PersonID]= [Forms]![FormName]![PersonID]")

Just change FormName to the actual form name and Control name to the name of the Email control on the form.

HTH,
Nikos
Consider a simple two table scenario like this:

Requests Table:
RequestID: AutoNumber (Unique ID)
PersonID: Number (Reference to record in People table)

People Table:
PersonID: AutoNumber (Unique ID)
Name: Text (Name of person)
Email: Text (Their email address)

A simple form is requested for the request table whereby the ID is shown along with a combo box to allow [Requests]![PersonID] to be filled in.

However, we'd like a non-editable textbox below the person combo box that shows the email field when a specific person is selected. This is, in effect, a lookup of a single record in [People] using the [Requests]![PersonID] field.

I'm suspecting it's a computed text box. I can do it using VBA to fire of an ADO/DAO query, maybe even using an Access query.

But is there a way to do it without having to resort to code?

Thanks, Rob.
 
Thanks - I knew there was a way to do it!!
Rob,

Yes, there is.

To begin with: You need an unbound text box on the form to display the e-mail address, with its Data tab properties Enabled > No and Locked > Yes (so the user cannot change it).
Then, selecting the combo box, go to properties, tab Events, and in the On Change event select Macro builder, and create a simple macro with one action as follows:
Action: SetValue
Arguments: Item: Forms!FormName!ControlName
Expression: DLookUp("","People","[People]![PersonID]= [Forms]![FormName]![PersonID]")

Just change FormName to the actual form name and Control name to the name of the Email control on the form.

HTH,
Nikos
Consider a simple two table scenario like this:

Requests Table:
RequestID: AutoNumber (Unique ID)
PersonID: Number (Reference to record in People table)

People Table:
PersonID: AutoNumber (Unique ID)
Name: Text (Name of person)
Email: Text (Their email address)

A simple form is requested for the request table whereby the ID is shown along with a combo box to allow [Requests]![PersonID] to be filled in.

However, we'd like a non-editable textbox below the person combo box that shows the email field when a specific person is selected. This is, in effect, a lookup of a single record in [People] using the [Requests]![PersonID] field.

I'm suspecting it's a computed text box. I can do it using VBA to fire of an ADO/DAO query, maybe even using an Access query.

But is there a way to do it without having to resort to code?

Thanks, Rob.
 
Back
Top