display full name on form (1 Viewer)

[merlin777]

I'm using a form to enter data into a table.

I'm getting the data from namestable which is ID, firstname, lastname.

I'm using a combo box which displays the firstname and lastname in the pull down list.

Only the firstname is displayed in the combox box after selection. Is there a simple way to display the first and last names in the combo box?

Sorry this is such a basic question. I've read other posts about this and just haven't grasped the answer - it always seems so much more complex for something relatively simple!

 

[pbaldy]

You can use a row source that combines the names:

SELECT ID, FirstName & " " & LastName As FullName
FROM TableName

 

[merlin777]

You can use a row source that combines the names:

SELECT ID, FirstName & " " & LastName As FullName
FROM TableName

I've seen that answer before and not really understood it.

do you mean 'namestable' rather than TableName?

So am I right in thinking this expression effectively says:

I understand the 'FirstName & " " & LastName' but what does SELECT ID refer to and where does 'As FullName come from?

 

[pbaldy]

Well, "TableName" meant to replace that with whatever the actual table name is. Is the existing row source just the table? If so, click on the ellipsis to the right and say yes you want to build a query. Select the ID field and then put

FirstName & " " & LastName

as the second field. You'll see that Access aliases that to Expr1, which you can change to FullName if you want. Change to SQL view and you'll see the SQL that the builder creates.

 

[merlin777]

Well, "TableName" meant to replace that with whatever the actual table name is. Is the existing row source just the table? If so, click on the ellipsis to the right and say yes you want to build a query. Select the ID field and then put

FirstName & " " & LastName

as the second field. You'll see that Access aliases that to Expr1, which you can change to FullName if you want. Change to SQL view and you'll see the SQL that the builder creates.

so FullName is just an arbitrary label for me as a reminder that this is what Expr1 is?

Does that mean I can use 'Fullname' in other places to replace @FirstName & " " & LastName?

 

[pbaldy]

Yes, it's called an alias.

No, FullName would only exist in this query. Later Access versions have calculated fields in tables. I haven't used it, but this is the type of thing you could use a calculated field for. That you could use anywhere the table was, it would show as another field.

 

[merlin777]

Yes, it's called an alias.

No, FullName would only exist in this query. Later Access versions have calculated fields in tables. I haven't used it, but this is the type of thing you could use a calculated field for. That you could use anywhere the table was, it would show as another field.

i'm getting 'the syntax of the subquery in this expression is incorrect'

In the fieldbox of the first column of the query I have:

SELECT ID, [FirstName] & " " & [surName] As FullName

Have I made a typo?

 

[pbaldy]

What I gave you was SQL. If you're using the design grid, you'd want ID in the first field and

FullName: [FirstName] & " " & [surName]

 

[merlin777]

What I gave you was SQL. If you're using the design grid, you'd want ID in the first field and

FullName: [FirstName] & " " & [surName]

If I was using SQL, where would that go? As you can tell I'm a novice.... but I'm getting the sense I'll need to embrace SQL soon!

 

[pbaldy]

The design grid is a GUI to create SQL. If you want to type it in directly, you can change the view. One way to learn is to build something in the GUI, then switch to SQL view and examine the resulting SQL.

 

[merlin777]

The design grid is a GUI to create SQL. If you want to type it in directly, you can change the view. One way to learn is to build something in the GUI, then switch to SQL view and examine the resulting SQL.

Aha. I found the SQL view in the SQL menu. I guess the design grid is the name of the table at the bottom of the screen I have been using so far?

 

[pbaldy]

I'm not sure where you're referring to, but probably not. The design grid is a GUI that would let you create SQL (a query) that pulled from any table or combination of tables (within certain parameters). When the row source was just a table, clicking on the ellipsis invoked the GUI, but you could have created something unrelated to that table if you wanted.

 

[merlin777]

I'm not sure where you're referring to, but probably not. The design grid is a GUI that would let you create SQL (a query) that pulled from any table or combination of tables (within certain parameters). When the row source was just a table, clicking on the ellipsis invoked the GUI, but you could have created something unrelated to that table if you wanted.

Where do I find the ellipsis to invoke the GUI? Its usually in the property sheet but I can't see one anywhere in design view?

 

[pbaldy]

It's on the property sheet, to the far right of any property where it's appropriate. Outside a form, you can click on Query Design on the Create tab of the ribbon to start the GUI. That would let you create a query that wasn't just used behind the property. You could create a query and call it GetNames and then use that as the row source for this combo as well as others throughout the db.

 

[mike60smart]

Hi

The Elipse is displayed when you are in the Design View of the Form.

When you select the Control and view the Property Sheet the Row Source will be displayed as follows:-



When you click on the Elipse the Select statement will display as follows in the Query Design View:-



Then when you switch to Form View the result will be as follows:-