3 Replies - 4150 Views - Last Post: 24 October 2013 - 12:24 PM

#1 thechazm  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 24-October 13

Custom selection in combo box results

Posted 24 October 2013 - 11:07 AM

Hello All,


I am simply trying to add a selection in a combo box that says "Add New Personnel" that's located at the top of the list and for some reason I can't seem to nail down the syntax of what I am doing wrong. Access does not seem to be helping me either with it's arbitrary error's.


I would greatly appreciate any help on figureing out what the problem is in the below SQL Statement.




SELECT "-1" as [Personnel.ID], 

"(Add New Personnel)" as [FullName] FROM Personnel UNION SELECT "0" as [Personnel.ID], 

[Last Name] & ", " & [First Name] & " " & [MI] AS FullName, 

Personnel.Badge, 

[Badge Colors].[Badge Color], 

Personnel.Phone


FROM Personnel INNER JOIN [Badge Colors] ON Personnel.[Badge Color] = [Badge Colors].ID


ORDER BY [Last Name] & ", " & [First Name] & " " & [MI];








Thanks again for the help and time!



TheChazm

Is This A Good Question/Topic? 0
  • +

Replies To: Custom selection in combo box results

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,463
  • Joined: 12-June 08

Re: Custom selection in combo box results

Posted 24 October 2013 - 11:13 AM

If you are hard coding column values there is no 'from <table name>' because.. well.. they are hard coded and not from a table!
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3197
  • View blog
  • Posts: 10,714
  • Joined: 12-December 12

Re: Custom selection in combo box results

Posted 24 October 2013 - 11:13 AM

A UNION query must have the same number of columns from each of the tables. Also, the aliases (AS) are taken from the first table specified, so remove the aliases for the second table.

Get it working first without the ORDER BY clause, then add the ORDER BY clause, trying with the aliases from the first table. In fact, I believe you will need to use ORDER BY 1, 2 to order by the 1st then 2nd column. You won't be able to use the aliases and you would only be able to use the original column names if they all had the same column-name(s).

.. and what modi said ;)

This post has been edited by andrewsw: 24 October 2013 - 11:17 AM

Was This Post Helpful? 1
  • +
  • -

#4 thechazm  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 24-October 13

Re: Custom selection in combo box results

Posted 24 October 2013 - 12:24 PM

@modi123_1:
Thanks for the reply but it does sound like you did not understand what I was attempting to do. That's ok but the comment you left made it sound like there was no way in doing what I was trying to do. I have done this before in the past and even some of the Microsoft standard databases that are templates also use this type of functionality for selecting and applying filters. Either way I was able to figure it out so no need to worry and I appreciate the time you took to write your response.

@andrewsw:
Thank you for your reply as well and for cluing me in on the Union query requirement that I had forgotten about since I had not done this in a while. I was able to fix my problem and move on.

The cause of the entire issue was mainly the [ID]. Since I was trying to start it out with having a -1 value as the selected ID I had forgotten this was going on a continuous form in a combo box. All I had to do was put an iff statement in there evaluating if the [FullName] field was null or not. If it was null I returned a -1 as the active ID if it was not then I returned the actual ID of the person. Now I have my custom line at the top of the combo box when you hit the drop down that after selected fires up an instance of the Add Person form to allow for all of the data entry.

After the person saves that record it closes and refreshes the combo box data and selects that person as the needed asset.

Again thanks for the help.

Have a good one,

Chaz
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1