How to add additional values to a rowsource value

[James Bowyer]

I have several combo boxes in a form as they link to more information (for example customerproject manager, when selected would not only allow you to select the project manager, but would also then drag a whole load of information into the background of the form, such as telephone number, email address, etc, etc).

All is fine so far, however, to add new project managers I originally had a button called admin tools, that would allow you to add new values for all the different combo boxes and again, that worked fine. However, according to the powers that be, thats confusing.

What I have been instructed to do is have a list such as this:

Name1
Name2
Name3
Add New Name

where obviously the first part can be picked up with

CustomerProjectManager.RowSource = "SELECT CustomerPM.CustomerProjectManager FROM CustomerPM WHERE CustomerPM.CustomerName = " & "CustomerName.Value"

however, How do I then add on "Add New Name" to this list?
I want to make it so when you select that it will then open the relevant form to allow the addition of the additional data.

And thats where I'm stuck!!

Help!

Thanks

[gnawoncents]

Add "Add New Name" to your table so it shows up at the bottom/top of your list (use a special character if you need). Then you can set up an if statement in the after update event of the combo box that would then open the form. Something like this:

If [customerprojectmanager] = "Add New Name" Then
    DoCmd.OpenForm "YourFormHere"
    Exit Sub
End If

That way whenever someone selects "Add New Name" it will open the form to well... add a new name. Otherwise it will execute whatever code you need for the manager selected.

[NeoPa]

You need to add in an extra SELECT to your SQL using the UNION ALL clause. Use TOP 1 to ensure it only appears once :

With Me.CustomerProjectManager
    .RowSource = "SELECT [CustomerProjectManager] " & _
                 "FROM   [CustomerPM] " & _
                 "WHERE  [CustomerName]=" & Me.CustomerName & _
                 "UNION ALL " & _
                 "SELECT TOP 1 'Add New Name' " & _
                 "FROM   [CustomerPM]"
End With

[James Bowyer]

I tried the Union All code, and now I am getting an entirely blank combo box. Any ideas why?

[NeoPa]

Not without seeing what you actually tried.

I have to assume that you have made no other changes too of course. Changing properties of your ComboBox control could muck things up, but I only know what you tell me.

[James Bowyer]

The current code is as follows:

Private Sub CustomerName_AfterUpdate()

If [CustomerName] = "Add New" Then
Me.CustomerName = ""
DoCmd.OpenForm "CustomerDetailsInputForm", acNormal, , , acFormAdd, acWindowNormal
Exit Sub
Else
With Me.CustomerProjectManager
    .RowSource = "SELECT [CustomerProjectManager] FROM [CustomerPM] WHERE [CustomerName] = " & Me.CustomerName & "UNION ALL SELECT TOP 1 'Add New' FROM CustomerPM"
End With

End If

End Sub

But unfortunately the combo box is still blank. Any ideas what I am doing wrong?

Thanks

James

[NeoPa]

Ah. I think I see the problem now. You stupidly followed my instructions, which I'm ashamed to say, had at least two newbie errors in them.

Let me see if I can suggest some more reliable code for you :

With Me.CustomerProjectManager
    .RowSource = "SELECT [CustomerProjectManager] " & _
                 "FROM   [CustomerPM] " & _
                 "WHERE  [CustomerName]='" & Me.CustomerName & "' " & _
                 "UNION ALL " & _
                 "SELECT TOP 1 'Add New Name' " & _
                 "FROM   [CustomerPM]"
End With

I hope you find this more reliable :-D

[James Bowyer]

Perfect!! Thanks Very much.

[NeoPa]

No worries. I'm just sorry I gave you such a bum lead in the first place. Such basic errors too.

[James Bowyer]

Just realised one very small issue, the combo boxes are now no longer in alphabetical order when clicked on. How do I go about changing this? (Preferably leaving Add New at the bottom as it currently is with your code).

Ta!

[NeoPa]

Unfortunately, it seems that when UNION is used in any form, it ignores any ordering except that after the whole dataset has been brought together (I even tried sorting the data by including the ORDER BY clause in a subquery - but even that had no effect).

It seems you have two choices then :-

1. Leave it as it is, with the "Add New Name" entry last.
2. Sort it, but have the "Add New Name" entry appear wherever it does alphabetically in the list.

There is a third choice I suppose, which is to add an alphabetically high character to the value so that it appears at the end after sorting. Unfortunately, I only found 'z' to work well in this context. Every other character I tried seemed to be treated as another version of characters lower down the order, and thus the entry was not found at the end.

Anyway, the code for sorting the results would be :

With Me.CustomerProjectManager
    .RowSource = "SELECT   [CustomerProjectManager] " & _
                 "FROM     [CustomerPM] " & _
                 "WHERE    [CustomerName]='" & Me.CustomerName & "' " & _
                 "UNION ALL " & _
                 "SELECT TOP 1 'Add New Name' " & _
                 "FROM     [CustomerPM] " & _
                 "ORDER BY [CustomerProjectManager]"
End With

[NeoPa]

Actually, I suppose there might be a way. It's getting a little cute, but may be something you'd like. It involves putting the resultant data from the UNION query in as a subquery source of an outer query that sorts how we want it to, then displays only the textual result ([CustomerProject Manager]).

It's a bit more involved so I'd do this with an intermediate string variable (strSQL) which would need to be Dimmed first. The Dim line would be put at the start of your event procedure and would look like :

Dim strSQL As String

The rest of the code would be something like :

strSQL = "SELECT   0 AS [Order]" & _
         "       , [CustomerProjectManager] " & _
         "FROM     [CustomerPM] " & _
         "WHERE    [CustomerName]='" & Me.CustomerName & "' " & _
         "UNION ALL " & _
         "SELECT TOP 1 " & _
         "         1" & _
         "       , 'Add New Name' " & _
         "FROM     [CustomerPM]"
With Me.CustomerProjectManager
    .RowSource = "SELECT   [CustomerProjectManager] " & _
                 "FROM     (" & strSQL & ") " & _
                 "ORDER BY [Order]" & _
                 "       , [CustomerProjectManager]"
End With

[James Bowyer]

Crikey, that looks quite involved! I'll try it out and let you know how I get on! Thanks

[NeoPa]

James Bowyer:
Crikey, that looks quite involved!

Indeed James. That's why I felt it would be helpful to split away the string for the inner (sub) query. That string can be tested directly too of course. If you shove the resultant string value in the SQL view of a query you can see the results on screen.

[James Bowyer]

It works! I had to add a few &'s (you did that just to check I was paying attention I'm sure) but other than that, perfect.

Now I just need to stare at it long enough to understand exactly how it works!