Combobox Header

[TheSmileyCoder]

I have a table tbl_Folder with the following fields
KEY_Folder (autonumber)
tx_Name (text,50)
ID_ParentFolder (long)

The tx_Name field has the caption "Name of Folder"

Each record CAN be related back into the same table, linking a folder to its parent folder through ID_ParentFolder .

I have a combobox in which the user is meant to make a selection, of where to place a new document. Since sometimes folders will have similar names but different parent folders, I need to include the parent folder name in the combobox dropdown.
Example: The Folder "ATC" is located in the folder "PD-Preliminary Design"
Another folder also named ATC is located in the folder "DD-Detailed Design"

So in my dropdown I would like it to look:

ATC   | PD-Preliminary Design
ATC   | DD-Detailed Design

That part is not a problem and I have it done.

My problem is that I want a header at the top of each column in the combobox, reading "Folder | Parent Folder" and no matter what I try the only header I can get comes from the fields Caption property "Name of Folder | Name of Folder", and I dont seem to be able to override this.
My combobox Rowsource is:

SELECT tbl_Folder.KEY_Folder, 
       tbl_Folder.tx_Name AS Folder, 
       aTbl_ParentFolder.tx_Name AS [Parent Folder], 

 FROM  tbl_Folder 
       INNER JOIN 
       tbl_Folder AS aTbl_ParentFolder 
 ON 
       tbl_Folder.ID_ParentFolder=
       aTbl_ParentFolder.KEY_Folder;

How can I specify the column headings I wish for my combobox?

[sierra7]

Hi Smilie
Your code looks good at first glance but is "Folder" not a Reserved word?

Does putting it into Square [brackets] achieve anything?

I have not come accross this as a problem before. Normally, when you execute the query the column headings are what you expect to see if you choose to display them in the ComboBox; and you can set them just as you have done.

I generally don't bother with captions on fields , maybe this is an issue?

S7

[TheSmileyCoder]

Nope, it is not a reserved word as can be seen from:
List of reserved words in Access 2002 and in later versions of Access

Generally I dont bother either (Allthough I do see that it could probably save me time on developing if I bothered to set them at the start).

[sierra7]

Hi again
I have been curious about this behaviour but have only just had time to run some test.

These confirm your own findings, that if a Caption is specified for a field in a table, then that takes precidence over all-else, rather than just being a default value.

I had a table with a field called DRAWING and I gave it the caption 'My Drawing Number'. In my query I tried to shorten this to MDN by writing "SELECT DRAWING AS MDN....." but the full name was returned for the column heading.

Curiously, when I saved that query and used it as the basis of another query, the field that was available (from the first query) was 'MDN' but when that query was run the heading again resumed the verbose caption. This may well have been why I stopped using them long ago.

Two other points of curiosity (a) you were right about 'Folder' not being a reserved word (b) even if it had been, putting it in square brackets would have made the situation worse. i.e I used the word 'Form' instead of 'Folder' and removed the caption from the table. So "SELECT DRAWING AS Form,..." works but "SELECT DRAWING AS [Form].." does not work! "Form" in quotes also works OK but the quotation marks are displayed in the column heading.

If you include a space in the alias name then the whole must be in quotation marks; square brackets won't do it, but Access then puts square brackets around outside the quotation marks! I won't go further, but I am just glad that I never uses spaces in names. This is all new terratory!
S7