469,366 Members | 2,243 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

How to add additional values to a rowsource value

I have several combo boxes in a form as they link to more information (for example customerprojectmanager, 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

Expand|Select|Wrap|Line Numbers
  1. 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
Nov 2 '10 #1
15 6784
gnawoncents
214 100+
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:

Expand|Select|Wrap|Line Numbers
  1. If [customerprojectmanager] = "Add New Name" Then
  2.     DoCmd.OpenForm "YourFormHere"
  3.     Exit Sub
  4. 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.
Nov 2 '10 #2
NeoPa
32,185 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. With Me.CustomerProjectManager
  2.     .RowSource = "SELECT [CustomerProjectManager] " & _
  3.                  "FROM   [CustomerPM] " & _
  4.                  "WHERE  [CustomerName]=" & Me.CustomerName & _
  5.                  "UNION ALL " & _
  6.                  "SELECT TOP 1 'Add New Name' " & _
  7.                  "FROM   [CustomerPM]"
  8. End With
Nov 2 '10 #3
I tried the Union All code, and now I am getting an entirely blank combo box. Any ideas why?
Nov 3 '10 #4
NeoPa
32,185 Expert Mod 16PB
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.
Nov 4 '10 #5
The current code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CustomerName_AfterUpdate()
  2.  
  3. If [CustomerName] = "Add New" Then
  4. Me.CustomerName = ""
  5. DoCmd.OpenForm "CustomerDetailsInputForm", acNormal, , , acFormAdd, acWindowNormal
  6. Exit Sub
  7. Else
  8. With Me.CustomerProjectManager
  9.     .RowSource = "SELECT [CustomerProjectManager] FROM [CustomerPM] WHERE [CustomerName] = " & Me.CustomerName & "UNION ALL SELECT TOP 1 'Add New' FROM CustomerPM"
  10. End With
  11.  
  12. End If
  13.  
  14. End Sub
  15.  
But unfortunately the combo box is still blank. Any ideas what I am doing wrong?

Thanks

James
Nov 10 '10 #6
NeoPa
32,185 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. With Me.CustomerProjectManager
  2.     .RowSource = "SELECT [CustomerProjectManager] " & _
  3.                  "FROM   [CustomerPM] " & _
  4.                  "WHERE  [CustomerName]='" & Me.CustomerName & "' " & _
  5.                  "UNION ALL " & _
  6.                  "SELECT TOP 1 'Add New Name' " & _
  7.                  "FROM   [CustomerPM]"
  8. End With
I hope you find this more reliable :-D
Nov 10 '10 #7
Perfect!! Thanks Very much.
Nov 10 '10 #8
NeoPa
32,185 Expert Mod 16PB
No worries. I'm just sorry I gave you such a bum lead in the first place. Such basic errors too.
Nov 10 '10 #9
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!
Nov 11 '10 #10
NeoPa
32,185 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. With Me.CustomerProjectManager
  2.     .RowSource = "SELECT   [CustomerProjectManager] " & _
  3.                  "FROM     [CustomerPM] " & _
  4.                  "WHERE    [CustomerName]='" & Me.CustomerName & "' " & _
  5.                  "UNION ALL " & _
  6.                  "SELECT TOP 1 'Add New Name' " & _
  7.                  "FROM     [CustomerPM] " & _
  8.                  "ORDER BY [CustomerProjectManager]"
  9. End With
Nov 11 '10 #11
NeoPa
32,185 Expert Mod 16PB
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 ([CustomerProjectManager]).

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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
The rest of the code would be something like :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT   0 AS [Order]" & _
  2.          "       , [CustomerProjectManager] " & _
  3.          "FROM     [CustomerPM] " & _
  4.          "WHERE    [CustomerName]='" & Me.CustomerName & "' " & _
  5.          "UNION ALL " & _
  6.          "SELECT TOP 1 " & _
  7.          "         1" & _
  8.          "       , 'Add New Name' " & _
  9.          "FROM     [CustomerPM]"
  10. With Me.CustomerProjectManager
  11.     .RowSource = "SELECT   [CustomerProjectManager] " & _
  12.                  "FROM     (" & strSQL & ") " & _
  13.                  "ORDER BY [Order]" & _
  14.                  "       , [CustomerProjectManager]"
  15. End With
Nov 11 '10 #12
Crikey, that looks quite involved! I'll try it out and let you know how I get on! Thanks
Nov 11 '10 #13
NeoPa
32,185 Expert Mod 16PB
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.
Nov 12 '10 #14
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!
Nov 15 '10 #15
NeoPa
32,185 Expert Mod 16PB
I seem to be making a bunch of sloppy errors in this thread James. You caught them though, so that's good.

I've updated my post (#12 - with an edit comment) to show what it should have looked like ;-D
Nov 15 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Frank | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.