Cascading ComboBoxes to a TextBox - Code Tweak Needed

[Walt in Decatur]

I have implemented successfully a single-cascade ComboBox combination on a form in my database (using an AfterUpdate event code). I have also successfully implemented a ComboBox + TextBox combination, whereby the TextBox shows contents of another column from the query for the ComboBox.

It's when I try to link these two up together when the problem comes up. I've been trying to find a solution to this coding problem in a practice unbound form.

ComboBox1 is called Field1, it drives...
ComboBox2, which is called Field2. This has 2 Columns, the second one hidden
TextBox1, which is called Field3

Here is the code I'm using:

Private Sub Field1_AfterUpdate()
Me.Field2.RowSource = "SELECT Field_2 FROM" & _
" tbl_test WHERE Field_1 = '" & Me.Field1.Text & _
"' ORDER BY Field_2"
Me.Field2.Value = ""
Me.Field1.Requery
End Sub

When I first switch from Design to Form view, the TextBox and ComboBox2 properly display their contents. However, as soon as I actually use ComboBox1, the ComboBox2 filtering works, but the second column (containing what should be displayed in the TextBox goes blank, and so does the TextBox

Any suggestions for fixing the code?

[Stewart Ross]

Hi. A far simpler approach is to refer to the column of your combo box directly as the control source of your textbox - no coding required.

If you wanted to refer to the third column of your combo, set the control source of the textbox to

=[your combo name].column(2)

(Columns are numbered from 0.)

In the rowsource query of the combo you can add additional columns that are not displayed to the user when the combo is selected (by setting the column width for those columns to 0). You can then use these in other textboxes or in code on the after update event for instance as quick lookups. I use this type of lookup a lot to display related data for users without using the Dlookup function.

-Stewart

[Walt in Decatur]

Stewart:

Thank you for replying. Indeed, that's the way the TextBox is set up. And it works fine, until the VBA for ComboBox1 is run. Then the fields for column which I want to appear in the TextBox go "blank". Since the TextBox is unbound (I just need that info for performing some calculations on the form), I don't know whether the correct info is actually being sent to it.

In short, there is something missing in the VBA that will not make that column info disappear.

Walter

Hi. A far simpler approach is to refer to the column of your combo box directly as the control source of your textbox - no coding required.

If you wanted to refer to the third column of your combo, set the control source of the textbox to

=[your combo name].column(2)

(Columns are numbered from 0.)

t

[missinglinq]

Are you. perchance, running version 2003 with the SP3 service pack installed? If so, try installing the hotfix at this Microsoft link:

http://support.microsoft.com/kb/945674

Welcome to Bytes!

Linq ;0)>

[Walt in Decatur]

Thanks for the tip. The problem sounded like what I have, but...

I'm actually working in Access 2007 and the problem persists after I've saved the database in the new 2007 format. So I'm still stumped.

Are you. perchance, running version 2003 with the SP3 service pack installed? If so, try installing the hotfix at this Microsoft link:

http://support.microsoft.com/kb/945674

Welcome to Bytes!

Linq ;0)>

[Stewart Ross]

Hi again Walt. From your first post your row source code, run on after-update of your other combo, selects but one field for your field 2 combo (reproduced again below - see SELECT in line 2). When you run this code the second column is bound to be blank as there is data only for column 0, the default column, and no other.

Code from post 1

Private Sub Field1_AfterUpdate()
Me.Field2.RowSource = "SELECT Field_2 FROM" & _ '<< this selects just one field
" tbl_test WHERE Field_1 = '" & Me.Field1.Text & _
"' ORDER BY Field_2"
Me.Field2.Value = ""
Me.Field1.Requery
End Sub

If your combo has two columns, one hidden, you surely have to select two fields for your revised rowsource??

-Stewart

[Walt in Decatur]

Stewart:

Yes this was a problem. Since I don't have any formal training in SQL or Access, even simple things like this can stymie me. Thank goodness for all of you patient helpers!

Walter

Hi again Walt. From your first post your row source code, run on after-update of your other combo, selects but one field for your field 2 combo (reproduced again below - see SELECT in line 2). When you run this code the second column is bound to be blank as there is data only for column 0, the default column, and no other.


-Stewart