Cascading combo boxes... 2

[JasonPurdueEE]

hello everybody. got a question about making a form in Access 97 for ya. What I need to do is come up with a series of cascading combo boxes to allow users to pick different combinations from the dropdowns. the contents of the second dropdown need to be based off of the selection of the first combo box and the third dropdown needs to be based off of the selection of the first and second combo boxes, and so on and so forth. anybody know how to do this? Thanks in advance.

JASON

 

[braindead2]

See the FAQ limiting the selection of a combo box based on the selection of another.

Good Luck

 

[JasonPurdueEE]

yeah, I checked that out and tried to get mine to work and it wont let me. maybe somebody could give me a hand with it. heres the underlying info on my setup. VWOP and WorkPackage are the two fields that I use in my combo boxes. both are from the table VarrianceCommentary. Here's what I have so far. I am trying to get the first 2 fields to work properly. the first combo box is pulling the values VWOP from the table VarianceCommentary. the first combo box has these properties:

Control Source: VWOP
Row Source: Select Distinct VWOP From VarianceCommentary
After Update: RefreshNum (this is a macro that requerys Query1)


the second combo box is getting WorkPackage from the query titled Query1. the combo box has these properties:
Control Source: WorkPackage
Row Source: Select Distinct WorkPackage From Query1


Query1 is a select query with the following SQL statement:

SELECT VarianceCommentary.WorkPackage
FROM VarianceCommentary
WHERE (((VarianceCommentary.VWOP)=[Forms]![VarianceCommentaryBestFormEver1]![VWOP].[value]));

can anybody see where I'm going wrong here and maybe nudge me in the right direction? if clarification is needed, please let me know. thank you.

 

[scriverb]

I believe the piece that is missing to make a good assessment of the situation is in your statement:

After Update: RefreshNum (this is a macro that requerys Query1)

You don't requery Query1 you perform the following on the second combobox:

me![ComboBox2].requery
me![ComboBox2].setfocus
me![ComboBox2].dropdown

Put the above code in the afterupdate event procedure of me![ComboBox1]. This should requery the second comboBox refreshing it with just the records selected in Query1 which is based on the value of ComboBox1.

Let me know how this works out.

Bob Scriver

 

[FoxProProgrammer]

In addition to Bob's comments, I am curious about the query that you have stored in the Rowsource of combo box 2.

Row Source: Select Distinct WorkPackage From Query1

Query1 is a select query with the following SQL statement:

SELECT VarianceCommentary.WorkPackage
FROM VarianceCommentary
WHERE (((VarianceCommentary.VWOP)=[Forms]![VarianceCommentaryBestFormEver1]![VWOP].[value]));

Not that it matters much, but couldn't you combine these two into a single query such as:

SELECT DISTINCT VarianceCommentary.WorkPackage
FROM VarianceCommentary
WHERE (((VarianceCommentary.VWOP)=[Forms]![VarianceCommentaryBestFormEver1]![VWOP].[value]));

Then store this query in the Rowsource of combo box 2?

dz

 

[JasonPurdueEE]

thanks a ton for your help guys! I got the first 2 combo boxes to cascade correctly. but I ran into some trouble. heres the scoop: the first two combo boxes hold the data that forms the primary key. it allows me to pull down and select the values from the combo boxes just fine, but I cant do anything to it cause it says it cant edit the primary key. how do I make it so its just selecting (not trying to edit) those values so I stop getting that error?

and one more thing, how do I make this scale to more fields. the user needs to select those 2 fields and based off that I need to pull up other fields in the same table to display only, not change. basically off of the users selection of the two combo boxes (which yields a unique combination that I can trace back to a single record), I need to display the rest of the record. suggestions/hints/tips/etc? thanks.

 

[JasonPurdueEE]

a little update on this: the exact error I get when after I select my values from the combo boxes and try to anything is this,

"The changes you requested to the table were not sucessful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit dupicate entries and try again."

does that help any? need any clarification?

 

[JasonPurdueEE]

a little update on this: the exact error I get when after I select my values from the combo boxes and try to do anything is this,

"The changes you requested to the table were not sucessful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit dupicate entries and try again."

does that help any? need any clarification?

 

[FoxProProgrammer]

Jason,

Is your combo box bound or unbound? It should be unbound. If you are getting that error, I'd suspect that either the combo box is bound or another line of code is causing it. Have you traced the code with the debugger to make sure that the error is occurring when you think it is? Selecting a row of an unbound combo box should not attempt to write data to the table.

Regarding your second question, you will need to do a FindFirst on a recordset. The code will depend on whether your form is bound to a table, and whether your fields are bound or unbound. Let me know and I'll give you an example. Basically, you will need to do a FindFirst on a recordset. If it finds a match, then set form.bookmark = recordsetname.bookmark to synchronize the form to the recordset. This will cause your bound fields to populate. You will have to assign the unbound fields differently.

dz

 

[JasonPurdueEE]

they were bound to 1 whatever that means. I set it to bound to 0. it is then still goofed up. so you're saying my form should be unbound? what other changes do I need to make to get this to run properly. sorry for all the questions, but this is really getting frustrating.

 

[scriverb]

No, the form can be bound but you cannot have a combobox bound to a primary key because when you have a record selected on the form and you pick another value for that combobox that is bound to your primary key field you basicially are trying to change the primary key. ACCESS thinks that you have selected another value for this record. The primary key being unique you cannot do this.

Does this make sense to you? Now what you are doing with these combobox's is important. Are you are selecting a new record to be displayed? If so then they certainly should be unbound and the code behind the ComboBox's that will find the record for you.

Give us more information so that we can be more specific.

Bob Scriver

 

[FoxProProgrammer]

Hi Jason,

Access surely can be frustrating! The good news is it sounds like you are almost there. As Bob said, the form can be bound, but for this to work properly, the combo box must be unbound. To determine if the combo box is bound or unbound, look in the control source property of the combo box. It shouldn't have anything in it. Your SQL Query should be in the rowsource property of the combo box. The word "Unbound" should appear in the control in design view. If the combo box is bound, the field name will appear in the combo box in design view instead of "Unbound". If your control is bound to a field, simply remove the field name from the control source property. If the Query in the rowsource is correct, it should work. If it doesn't, post back and we'll try to assist you further.

dz

 

[FoxProProgrammer]

Sorry, one more thing about the bound column of a combo box... The bound column of a combo box determines what value the control has when a row is selected. It does not mean that the control is bound to the table. This is confusing because they use the word "bound" to mean two different things. Let's say that a combo box has three columns: column one contains the customerID (primary key), column two contains the customer's name, and column three contains the customer's address. If the combo box is bound to column 0, it means that the value of the control will be the customerID when a row is selected. If the combo box is bound to column 1, the value of the control will be the customer's name when a row is selected. If the combo box is bound to column 2, the value of the control will be the customer's address when a row is selected. It is easier to do what you want if the control is bound to column 0 (the primary key), but you could make it bound to column 1 and use the column property to refer to the value in column 0 in the Where clause of the query in the second and subsequent combo boxes. I'm not sure if this makes any sense. If not, please write back.

Thanks,

dz

 

[JasonPurdueEE]

ok, thanks for explaining the bound/unbounded thing everybody. I changed them all to unbound but now my query in the rowsource for my second combo box isnt working. it just pulls up a blank drop down. I looked at the SQL statement behind it and it looks ok. here it is. anybody see some glaring error? VWOP is the first combo box field and WorkPackage is the second combo box's field. VariancCommentary is the table this all comes from. VarianceCommentaryBestFormEver1 is the name of this form (lol, what can I say, I ran out of ideas when naming it).


SELECT DISTINCT VarianceCommentary.WorkPackage
FROM VarianceCommentary
WHERE (((VarianceCommentary.VWOP)=[Forms]![VarianceCommentaryBestFormEver1]![VWOP].[value]));


if this looks good to you guys too, any ideas of what to troubleshoot next? thanks a ton, everybody has been very helpful. I love tek-tips. this has got to be the most friendly and knowledgeable forum on the net!

 

[scriverb]

As FoxProProgrammer said, make sure that the Bound Column of the first ComboBox is 1 so that the Name is the value of the ComboBox. This needs to be, so that the second combobox will use that value to requery and display the correct records. It sounds like the value of the bound column from the first combobox is either the wrong column or you have removed the value.

Check that out and get back to us.

Bob Scriver

 

[JasonPurdueEE]

ahh, I get it now. I changed that now I get the drop down with the choices. but when I click on one it closes the dropdown and nothing is selected. what setting did I goof up now?

also, as the next elements of my form I want the rest of the record selected by the first two combo boxes. I was thinking of using text boxes to add the additional fields. what property settings will be of importance to me in the text box properties?

 

[FoxProProgrammer]

Hi Jason,

Great, you are making progress!

If nothing is displayed in the combo box after you click an item, the only thing that I can think of is code in the OnChange procedure is changing the value or selecting a different row. Can you please post all your code in the OnChange event of the combo box?

Thanks,

dz

 

[JasonPurdueEE]

ok, I figured out the prob with it displaying nothing in the second combo box. I dont know exactly what it was, I just started playing with the properties and instead of making one change at a time and testing it like I should have, I made about 3 changes and it worked. its still too early to be systematic this morning. anyway, got that figured out, but my second question still stands. what do I need to do to the properties of the text boxes to get the appropriate field info to display? I assume this is the easy part and all the hard stuff is behind me. *crosses fingers* again, with the selection of combo box 1 and 2 there will only be one record that matches the criteria (the two fields used in combo 1 and 2 respectively form the primary key). I need to diplay the rest of the record in text boxes (or is there a better way?). what property settings are important to me in this case?

 

[FoxProProgrammer]

If the text boxes are bound to the table, all you have to do is find the record and synchronize the recordset to the form. This code assumes that your Form is bound to a table. If it isn't, let us know.

RecordsetClone.FindFirst <expression> ' Find the record

If RecordsetClone.NoMatch = True Then
MsgBox &quot;Record not found.&quot; ' We shouldn't get here.
Else
Bookmark = RecordsetClone.Bookmark ' Sync the form to recordset
End If

RecordsetClone.Close

In this example, expression would contain a string that identifies the record to search for. It is like a Where clause in a query. You would build the string based on the values selected in your two combo boxes. If you are certain that VWOP and WorkPackage are unique, the expression would look like one of these.

If the combo boxes contain numbers:

&quot;VarianceCommentary.VWOP = &quot; & Trim(Str(<cbo1name.Value>)) & &quot; And VarianceCommentary.WorkPackage = &quot; & Trim(Str(<cbo2name.Value>))

If the combo boxes contains text:

&quot;VarianceCommentary.VWOP = &quot; & Chr(34) & <cbo1name.Value> & Chr(34) & &quot; And VarianceCommentary.WorkPackage = &quot; & Chr(34) & <cbo2name.Value> & Chr(34)


dz

 

[JasonPurdueEE]

<dumb question>
how can I tell if the form is bound to the table?
</dumb question>

and where might this code be placed in my text boxes property settings? the Control Source?