473,842 Members | 1,598 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Combo Box value depends on another Combo Box

3 New Member
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 9608
jimatqsi
1,278 Recognized Expert Top Contributor
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.

Jim
Jul 29 '13 #2
tom1147
3 New Member
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
jimatqsi
1,278 Recognized Expert Top Contributor
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
  6.  
  7. next
  8.  
Note that if your combobox has more than one column, then (intItem) must be changed to (0,intItem) to reference the first column

Jim
Jul 30 '13 #4
zmbd
5,501 Recognized Expert Moderator Expert
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,
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
zmbd
5,501 Recognized Expert Moderator Expert
jimatqsi:
Bound controls are much easier to work with than what you propose.
Jul 30 '13 #6
tom1147
3 New Member
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.Va lue" 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
jimatqsi
1,278 Recognized Expert Top Contributor
No need to feel stupid about asking for help. We've all been there. :)
Aug 7 '13 #8

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

Similar topics

2
2352
by: D | last post by:
Book inventory. Access 2K. I'm trying to let a user choose a book title from one combo box, then a book number for that book title from another combo box. I can filter the book number combo with an unbound book title combo, but I'd like the user to see both the title and number when returning to the record. I've seen it done - just can't remember where. Anyone guide me to a solution? Thanks in advance.
1
14382
by: damatoer | last post by:
I am having problems with attempting to filter a form based on criteria selected in a combo box from another form. I am attempting to have a user select a order number from a combo box that is populated with unique order numbers from the order table. Then I want them to click on a command button that will use the combo box value (selected order number) as the filter for the form that I have set up to edit the order. The code that I...
1
11846
by: meganrobertson22 | last post by:
Hi Everyone- I am trying to use a simple macro to set the value of a combo box on a form, and I can't get it to work. I have a macro with 2 actions: OpenForm and SetValue. I can open my form, but I can't get the macro to set the value of a combo box on the form that opens. I don't need the macro to look up any values, I just want it to set the value of a combo box.
7
12286
by: sara | last post by:
I have a form where the user selects an item from a list box, and then works on that item. The user chooses an AD, then opens a form to assign departments to the ad. The top of the Depts form has a combo box, to select an ad from the drop down list. I would like the Depts form to open with the Ad selected on the Main form displaying in the combo box, AND any information already added presented to the user. (I am thinking this latter...
0
1552
by: CalMyPal | last post by:
Basing one combo box on another January 31, 2005 By Sal Ricciardi Making choices is what a combo box is for, but sometimes one choice depends on another. In this installment, you'll learn how to set up one combo box so that its list is based on the selection you make in another. We provide an example in a sample database that you can download. When I add the close statement to his code it keeps asking for the combo box parameters. ...
2
2064
by: SM | last post by:
Hello, I have an HTML file that contains a form with 2 selection list (combo box). The first combobox 'onchange' function, addColor(),adds a new color to the second combobox. I have half of the function build, but im missing the how to reference one combobox from another combobox. I've tried using the getElementById with no success. This is how the form with the 2 combobox looks like:
6
18426
by: troy_lee | last post by:
I have a continuous form that has one combo box (cbo1) with the selectable values of "Removal" and "Installation". I would like to change another combo box (cbo2) value list based on the selection in cbo1. For example, when the user selects "Removal" from cbo1 I would like the value list of cbo2 to be one set of values and when "Installation" is selected in cbo1 I would like the value list of cbo2 to be a different set of values.
1
2737
by: sejal17 | last post by:
Hello Please can anyone help me on my this question?I want to load country combobox and after selecting one country i want to load state combo of that country.I have done it successfully but problem is that if i put those combos on top then everything is working properly but if i put it in middle then the above textbox is automatically been blank.I think it is postback problem.How can i solve this problem? thanks.................
3
1347
by: lrw0831 | last post by:
I have a table named AssignedTo. In this table are the fields: AssignedTo_ID, Department, Employee. On my form, I need to create 2 combo boxes. The first one will select the department. Based one what department is selected, the second combo box will be populated with employees in that department. Can someone help me on how to do this?
1
3249
by: ncsthbell | last post by:
Hello, I hope I can do a good enough job trying to explain this situation! I have inherited the maintenance for an access database and I have been requested to make a change to a form to add 2 combo boxes. I am encountering problems with getting the data value from one of the combo boxes (for divisions and groups) to be updated on the record. THE FORM: frmHardware form property record source: HWInventory table (has columns for the...
0
10612
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10672
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10314
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9453
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7858
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7038
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5696
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5885
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4089
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.