Combo Box value depends on another Combo Box

[tom1147]

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

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.

[jimatqsi]

If changing the first combo box should change the second combo box, put some code into the OnChange event of the first combo box.

if me.dirty then me.dirty = false
me.combobox2.requery

The reference to me.dirty makes sure any changes on the screen are recorded before trying to execute the requery.

Jim

[tom1147]

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.

[jimatqsi]

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

me.combobox2="xyz"

. Or you can loop through the items in the combo box until you find the one you want like this

dim intItem as integer
for intItem = 0 to ComboBox2.ListCount-1
   if ComboBox2.column(intItem)=me.Combobox1 then
me.combobox2(intItem).Selected=true
endif
 
next
 

Note that if your combobox has more than one column, then (intItem) must be changed to (0,intItem) to reference the first column

Jim

[zmbd]

First, So long as you are using Access and/or VBA this is the place to be, and welcome!

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

Third,

SELECT [OtherID] FROM [OtherPersonQuery] WHERE Other = [Combo31];

is malformed.
Change this to either:
if the returned value in [Combo1] is numeric

SELECT [OtherID] FROM [OtherPersonQuery] WHERE "Other = " & me.[Combo1];

if the returned value in [Combo1] is text string

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.

[zmbd]

jimatqsi:
Bound controls are much easier to work with than what you propose.

[tom1147]

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:

sqlStr = "INSERT into Spouse(PersonID, SpouseID, SpouseName) VALUES ([ID], Combo31.Value, " + Combo31.Column(1) + ");" 

as well as:

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.

[jimatqsi]

No need to feel stupid about asking for help. We've all been there. :)