Default Value for Cascading Combo Boxes

[goaksmith]

I have a few cascading combo boxes on my form. They follow the order below. Everything works exactly as it should. However, once I get to the Items (cboItem1, cboItem2) there is only one value in the combo box. So I want to make the first and only value in the list be the default value instead of having to chose from the drop down menu. But since it is a cascading combo box what this value could be changes.

cboFund

cboOrder

cboItem1
cboItem2...

Is there any way to do this. I have tried to search other posts but nothing seems to be exactly what I am looking for. Any help would be greatly appreciated.

 

[dcx693]

Put some code in the AfterUpdate event of the cboOrder box like this:

Me.cboItem1 = Me.cboItem1.ItemData(0)

That will set the value of the combo box to the first (and in this case only) value.

 

[goaksmith]

I did this and it worked fine except for one thing. Some of the Orders have 5 items and some have 15 items. When there is no item it comes up with the following error message. (ie. If there are 5 items on Item 6) Is there any way to get around this?

Run-time error '3315'

Field 'OrderData.ItemNo1' can't be a zero-length string

 

[goaksmith]

I am not sure if this is the best way to solve this problem. Does anyone have any other suggestions on how to do this another way?

 

[dcx693]

I'm sorry, I don't quite understand what's going wrong. Are the combo boxes in question bound fields or are they unbound?

 

[goaksmith]

I have just come back to this problem after a month of working on other portions of the database. Let me see if I can explain better what is going on. I have 2 main cascading combo boxes.

[CBO1] is a list of fund groups.
[CBO2] is a list of packets that you can order within a Fund Group

Then there are 4 other combo cascading combo boxes that are all based on [CBO2]. In these are the individual items that make up what is in the packet. 4 is the maximum amount of items that are available however some funds have 1 or fewer.

For example.

[CBO1] = Gretchen Funds
[CBO2] = Gretchen Funds IRA Packet
[CBO3] = IRA Application
[CBO4] = Prospectus
[CBO5] = IRA Transfer Form
[CBO6] = Supplement prospectus

But then there might be another which is

[CBO1] = Shannon Funds
[CBO2] = Shannon Funds Regular Packet
[CBO3] = Regular Application
[CBO4] = Prospectus
[CBO5] =
[CBO6] =

Now I added the code below to the after update event of combo boxes [CBO3-6].

Me.CBO3 = Me.CBO3.ItemData(0)

The problem is that the error message below is returned if the field is blank.

Run-time error '3315'

Field '[CBO6].ItemNo1' can't be a zero-length string

So in the Gretchen funds there would be no error, but in Shannon funds [CBO5-6] would trigger the error. Is there a simple way to fix this problem?

 

[dcx693]

What is the rowsource of CBO6?

 

[goaksmith]

The row source is

SELECT DISTINCT [tblOrderType/CoverLetter].[Item Included 4] FROM [tblOrderType/CoverLetter] WHERE ((([tblOrderType/CoverLetter].[Order Type])=[Forms]![frmEditOrderItem]![cbOrder])) ORDER BY [tblOrderType/CoverLetter].[Item Included 4];

 

[goaksmith]

Where [Item Included 4] = [CBO6]

 

[goaksmith]

To answer another question below they are bound fields. This is the last thing I have to do on my database and I can find no way to make this work. Any help would be appreciated.

 

[goaksmith]

Is there a way to write some code to have it first look to see if there is a value in the combo box and if there is then have the first value in the combo box be set in the field and if not then place a null value in the field?