469,658 Members | 1,862 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Combo Box value depends on another Combo Box

I'm new to this site, so forgive me if this is the wrong forum.

My problem is as follows:
I have a form based on a Person table (usual identifying fields - name, address, etc) with all fields from the table at the top of the form.

At the bottom of the form, I need 2 pieces of info from another record in the Person table - ID (the key) and FullName.
I want the User to be able to select the FullName so I can tie the 2 records together in a Spouse table.
I have tried to use a combo box with all persons' names and a corresponding combo box that derives the key from the previous combo box with a
Expand|Select|Wrap|Line Numbers
  1. SELECT [OtherID] FROM [OtherPersonQuery] WHERE Other = [Combo31]; 
statement in the Row Source for Combo36 (the Other ID field).

This works a little, but leaves me with 2 problems:
- First, the combo box doesn't fill with any value even though the value is available in the drop-down portion (it is unique, so there is only one value).
- The second problem is that I can't make it update corresponding to changes in the Combo31 box (eg if Combo31 changes from Fred to Wilma, the Other ID field should change from 303 to 909.

Is there some way to make the second combo box mime the first one, or am I approaching this the wrong way? Any help would be appreciated.

I hope my explanation is clear.
Jul 29 '13 #1
7 8622
1,260 Expert 1GB
If changing the first combo box should change the second combo box, put some code into the OnChange event of the first combo box.
Expand|Select|Wrap|Line Numbers
  1. if me.dirty then me.dirty = false
  2. me.combobox2.requery
The reference to me.dirty makes sure any changes on the screen are recorded before trying to execute the requery.

Jul 29 '13 #2
I did a requery in the OnChange of the first combo box and that part works fine. Thanks. I still don't know how to force the value into the combo box without actually selecting it from the drop-down. I'd like to do that so I can hide the field from the User since its presence on the form is confusing. Is there some way to do that? I can't find any. I'd be happy with a text box but that doesn't allow a query, as far as I can discern.
Jul 30 '13 #3
1,260 Expert 1GB
You're saying you want to actually choose a value for the second combobox? Not change the list of available values but select a value? In the AfterUpdate event of the first combo box (not OnChange as I mistakenly said previously) you can add
Expand|Select|Wrap|Line Numbers
  1. me.combobox2="xyz"
. Or you can loop through the items in the combo box until you find the one you want like this
Expand|Select|Wrap|Line Numbers
  1. dim intItem as integer
  2. for intItem = 0 to ComboBox2.ListCount-1
  3.    if ComboBox2.column(intItem)=me.Combobox1 then
  4. me.combobox2(intItem).Selected=true
  5. endif
  7. next
Note that if your combobox has more than one column, then (intItem) must be changed to (0,intItem) to reference the first column

Jul 30 '13 #4
5,400 Expert Mod 4TB
First, So long as you are using Access and/or VBA this is the place to be, and welcome!

Second, check which column is bound in the combo boxes. This will tell you which of the following to use:

Expand|Select|Wrap|Line Numbers
  1. SELECT [OtherID] FROM [OtherPersonQuery] WHERE Other = [Combo31];
is malformed.
Change this to either:
if the returned value in [Combo1] is numeric
Expand|Select|Wrap|Line Numbers
  1. SELECT [OtherID] FROM [OtherPersonQuery] WHERE "Other = " & me.[Combo1];
if the returned value in [Combo1] is text string
Expand|Select|Wrap|Line Numbers
  1. SELECT [OtherID] FROM [OtherPersonQuery] WHERE "Other = '" & me.[Combo1] & "'";

fourth, we need the code you are using in the after_update event in the combo boxes that you are using to update the spouses table with.
Jul 30 '13 #5
5,400 Expert Mod 4TB
Bound controls are much easier to work with than what you propose.
Jul 30 '13 #6
First of all, thanks to all the responders for looking at my problem. As it turns out, I was making a much bigger problem out of it than needed to be. I had simply forgotten that you could access multiple columns out of a combo box query and that is what my second field ultimately needed.

When it finally came to me, I had other issues with quoting that masked the problem even further until I finally figured it all out.

If anyone cares to know what I ultimately had to do, it was to put:
Expand|Select|Wrap|Line Numbers
  1. sqlStr = "INSERT into Spouse(PersonID, SpouseID, SpouseName) VALUES ([ID], Combo31.Value, " + Combo31.Column(1) + ");" 
as well as:
Expand|Select|Wrap|Line Numbers
  1. sqlStr = "INSERT into Spouse(PersonID, SpouseID, SpouseName) VALUES (Combo31.Value, [ID], FullName);" 
before executing each sql statement.

The final piece that caused me to wonder what was happening was being able to place the "Combo31.Value" piece inside the quotes and have it execute perfectly, but I had to place the "Combo31.Column(1)" piece outside the quotes or I received a 3805 error telling me the function Combo31.Column() was undefined. I still don't understand why the 2 pieces of sql have different quoting requirements, but at least it works well now.

I feel kind of stupid for soliciting the help when the answer turned out to be so obvious, but it was a couple of different questions and answers on this site (including my own) that caused me to see the solution, so I appreciate the help.
Aug 7 '13 #7
1,260 Expert 1GB
No need to feel stupid about asking for help. We've all been there. :)
Aug 7 '13 #8

Post your reply

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

Similar topics

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