Combining multiple database columns in a listbox

[sts023]

Hi guys....

I know this is (or should be) possible, and I've spent a few hours looking around, but I suspect the answer is so obvious and simple that it's escaping me!

I have a list box populated with some reference codes. When a User clicks on one of the codes, I want to display data from two tables in another listbox (which may be a bad idea?) as confirmation.
The User is selecting an MP3 code. I have a Table with the MP3 code and the generic title of the MP3 (e.g. "Access 2003 Self Help"), and because one MP3 may have several books on it, I also have a Book Table with the book title and author key (and the MP3 key), and an Author Table.
The User selects an MP3, and in the next listbox a "confirmatory" liss appears showing the Titles and Authors on that MP3. This is working fine, but what I want to achieve is for the second list box to have (in English), "Book Name, by (Author Forename Author Surname)" e.g. "Teach Yourself Access, by A Genius".
My problem is that the "confirmatory" Listbox is set up by SQL -

  CS1S = "SELECT [CD Track Listing].Title, " & _
                "Author.Forenames, " & _
                "Author.Surname "
  CS2F = "FROM Author "
  CS3J = "INNER JOIN [CD Track Listing] ON Author.pkAuthor = [CD Track Listing].fkAuthor "
  CS4W = "WHERE [CD Track Listing].[CD Number]=""" & ReturnedMP3 & """"

  Me.BooksOnMP3.RowSource = CS1S & CS2F & CS3J & CS4W

This displays the results in rows in the Listbox.
Can I combine the "Author.Forename" and "Author.Surname"?
The "confirmatory" Lisybox is set up with a Row Source Type of "Table/Query", and although there is an SQL statement in the "Row Source", I understood this would be superceded by the code.
Also, how many columns should I define the Listbox as having?

Any help would be appreciated....

Steve

 

[DCrake]

Author.Forenames & " " & Author.Surname As FullName

 

[sts023]

Thanks David, but where is this definition (in the Properties or VBA?), and how do I use FullName?

Steve.

 

[DCrake]

  CS1S = "SELECT [CD Track Listing].Title, " & _
                "Author.Forenames & " " " & Author.Surname As FullName , "

 

[sts023]

Thanks David - once I figured out the AS means Alias, and realised I could join data together, and droped the properties column count to 2 it worked like a dream!

Thanks again....

Steve