Help: Textfield value

[vuongp]

I have 2 tables called "questions" and "submitter". The table
"questions" have a submitter_ID that is also a primary key in the
"submitter" table to keep track of who submits the question. I'm
designing a form to browse through the questions and the name of the
user who submits the question (instead of the submitter ID). My form
is bound? to the "questions" table, so what's the code I need to put
into the Data field of the textfield that allows the user to see the
name of the submitter (firstName lastName)? Currently the code in the
control field is "submitter_ID", but that only allows the form to
display the userID instead of the full name, which comes from another
table. I'm a complete newbie so if you could details the steps that
would be appreciated. TIA.

Here are the tables:
questions (ID, submitter_ID, question)
submitter (submitter_ID, firstName, lastName, emailAddr)

 

[Sprinks]

One of the advantages that a relational database like Access has over a
"flat-file" database (like a spreadsheet) is the ability to bring information
from different tables together on a form. Forms can be based on (that, is
can have their RecordSource property set to) a table or a query.

Create a new query, selecting both of your tables. Access should show a
link between the two tables on submitter_id. If not, click and drag from one
to the other to establish the link.

Next, drag the fields from the Questions table to the query grid (or
double-click on them). If you wish to display the full name, create a
calculated field by typing Fullname: [Firstname] & " " [LastName] in a blank
field.

Press the Exclamation point to run the query. You should see all of the
records in the Question table and the full name of the submitter. Now base
your form on the query, and change the ControlSource of your textbox to
"Fullname".

Note: Do not include submitter_id from the submitter table (the one side)
or the query will be "non-updateable", meaning you will be unable to edit the
records.

Hope that helps.
Sprinks

 

[vuongp]

OK. Thanks for the reply Sprinks. I followed your direction to the
part of setting up a query (Query1) and links the User.ID to
Questions.UserID, but after that part I got lost of what you're telling
me to do. Mostly this step here:

Next, drag the fields from the Questions table to the query grid (or
double-click on them). If you wish to display the full name, create a
calculated field by typing Fullname: [Firstname] & " " [LastName] in a blank
field.

I only need to have the User.EmailAddress shown on a textfield on a
form that is bound to the "questions" table.

One of the advantages that a relational database like Access has over a
"flat-file" database (like a spreadsheet) is the ability to bring information
from different tables together on a form. Forms can be based on (that, is
can have their RecordSource property set to) a table or a query.

Create a new query, selecting both of your tables. Access should show a
link between the two tables on submitter_id. If not, click and drag from one
to the other to establish the link.

Next, drag the fields from the Questions table to the query grid (or
double-click on them). If you wish to display the full name, create a
calculated field by typing Fullname: [Firstname] & " " [LastName] in a blank
field.

Press the Exclamation point to run the query. You should see all of the
records in the Question table and the full name of the submitter. Now base
your form on the query, and change the ControlSource of your textbox to
"Fullname".

Note: Do not include submitter_id from the submitter table (the one side)
or the query will be "non-updateable", meaning you will be unable to edit the
records.

Hope that helps.
Sprinks

I have 2 tables called "questions" and "submitter". The table
"questions" have a submitter_ID that is also a primary key in the
"submitter" table to keep track of who submits the question. I'm
designing a form to browse through the questions and the name of the
user who submits the question (instead of the submitter ID). My form
is bound? to the "questions" table, so what's the code I need to put
into the Data field of the textfield that allows the user to see the
name of the submitter (firstName lastName)? Currently the code in the
control field is "submitter_ID", but that only allows the form to
display the userID instead of the full name, which comes from another
table. I'm a complete newbie so if you could details the steps that
would be appreciated. TIA.

Here are the tables:
questions (ID, submitter_ID, question)
submitter (submitter_ID, firstName, lastName, emailAddr)

 

[Sprinks]

What I'm saying is that to display a field from User, you must base your form
NOT on the Questions table, but on a query that links User and Questions and
includes all the fields you wish to display.

There are 3 ways to select a table field to include in your query:
1. Double-click on it in the Table field list.
2. Click and drag it to the QBE grid (labeled Field, Table, Sort, etc.)
3. Choose it from the Field: drop-down list.

In addition to selecting fields from tables, you can create calculated
fields from expressions. The syntax, entered in the row labeled "Field:", is
<CalculatedFieldName>: <expression>, e.g.,

ExtdPrice: [Qty] * [UnitPrice] or
FullName: [FirstName] & " " & [LastName]

A calculated field will be part of the query, and thus can be displayed on
your form by setting the ControlSource of a textbox to the fieldname (in this
case, ExtdPrice or Fullname).

Hope that helps.
Sprinks

OK. Thanks for the reply Sprinks. I followed your direction to the
part of setting up a query (Query1) and links the User.ID to
Questions.UserID, but after that part I got lost of what you're telling
me to do. Mostly this step here:

Next, drag the fields from the Questions table to the query grid (or
double-click on them). If you wish to display the full name, create a
calculated field by typing Fullname: [Firstname] & " " [LastName] in a blank
field.

I only need to have the User.EmailAddress shown on a textfield on a
form that is bound to the "questions" table.

One of the advantages that a relational database like Access has over a
"flat-file" database (like a spreadsheet) is the ability to bring information
from different tables together on a form. Forms can be based on (that, is
can have their RecordSource property set to) a table or a query.

Create a new query, selecting both of your tables. Access should show a
link between the two tables on submitter_id. If not, click and drag from one
to the other to establish the link.

Next, drag the fields from the Questions table to the query grid (or
double-click on them). If you wish to display the full name, create a
calculated field by typing Fullname: [Firstname] & " " [LastName] in a blank
field.

Press the Exclamation point to run the query. You should see all of the
records in the Question table and the full name of the submitter. Now base
your form on the query, and change the ControlSource of your textbox to
"Fullname".

Note: Do not include submitter_id from the submitter table (the one side)
or the query will be "non-updateable", meaning you will be unable to edit the
records.

Hope that helps.
Sprinks

I have 2 tables called "questions" and "submitter". The table
"questions" have a submitter_ID that is also a primary key in the
"submitter" table to keep track of who submits the question. I'm
designing a form to browse through the questions and the name of the
user who submits the question (instead of the submitter ID). My form
is bound? to the "questions" table, so what's the code I need to put
into the Data field of the textfield that allows the user to see the
name of the submitter (firstName lastName)? Currently the code in the
control field is "submitter_ID", but that only allows the form to
display the userID instead of the full name, which comes from another
table. I'm a complete newbie so if you could details the steps that
would be appreciated. TIA.

Here are the tables:
questions (ID, submitter_ID, question)
submitter (submitter_ID, firstName, lastName, emailAddr)