Combo boxes on continuous subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2002 on Windows XP

I have cascading combo boxes on a continuous subform. The first combo box
should filter the results of the second, and the second for the third. This
has always worked on a main form with a table structure using an auto-number
field as the primary key, a text field showing the name of the item, and a
number field as the foreign key to the first table's auto-numbered primary
key. I read somewhere that this setup won't work on a continuous form. The
post said I'd have to make the primary key field the actual text that I want
to show on the combo box, thus eliminating the auto-number field and only
having the text of the item in the table, along with a text field as the
foreign key when necessary.

I was wondering if there is a different way to achieve the same results, as
I don't want to store the text value in my table, and the third combo box has
quite a lot of long-worded items.

Thanks in advance for any help.
Melanie
 
Hi, Melanie.

The problem with cascading combo boxes on a continuous subform is that there
can be only one Row Source for each combo box. So, if the Row Source does
not include values for previously entered records, Access doesn't know how to
display the data, so it seems to "disappear".

The solution is to replace the combo box with a textbox and choose your 2nd
value from a second form. In the following example, after a user has
selected a Steel Type, a form is displayed in dialog mode to select the sizes
associated with that type. Then the focus is returned to the next control.

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

The 2nd CB's Row Source is:

SELECT tblSteelSizes.SteelSizeID, tblSteelSizes.SteelType,
tblSteelSizes.SteelSize, tblSteelSizes.LBPerLF, tblSteelSizes.SFPerLF FROM
tblSteelSizes WHERE
tblSteelSizes.SteelType=Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!cboSteelType ORDER BY tblSteelSizes.SteelSize;

The selection is copied to the original form in the CB's AfterUpdate event:

Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub

This control on the main form is bound to the foreign key, and is not
visible. A visible control displays the text associated with the selection:

=IIf(IsNull([txtSteelSizeID]),"",DLookUp("SteelSize","tblSteelSizes","SteelSizeID=" & [txtSteelSizeID]))

Hope this helps.
Sprinks
 
Thanks for the info. How could this work if I had three combo boxes?

Sprinks said:
Hi, Melanie.

The problem with cascading combo boxes on a continuous subform is that there
can be only one Row Source for each combo box. So, if the Row Source does
not include values for previously entered records, Access doesn't know how to
display the data, so it seems to "disappear".

The solution is to replace the combo box with a textbox and choose your 2nd
value from a second form. In the following example, after a user has
selected a Steel Type, a form is displayed in dialog mode to select the sizes
associated with that type. Then the focus is returned to the next control.

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

The 2nd CB's Row Source is:

SELECT tblSteelSizes.SteelSizeID, tblSteelSizes.SteelType,
tblSteelSizes.SteelSize, tblSteelSizes.LBPerLF, tblSteelSizes.SFPerLF FROM
tblSteelSizes WHERE
tblSteelSizes.SteelType=Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!cboSteelType ORDER BY tblSteelSizes.SteelSize;

The selection is copied to the original form in the CB's AfterUpdate event:

Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub

This control on the main form is bound to the foreign key, and is not
visible. A visible control displays the text associated with the selection:

=IIf(IsNull([txtSteelSizeID]),"",DLookUp("SteelSize","tblSteelSizes","SteelSizeID=" & [txtSteelSizeID]))

Hope this helps.
Sprinks

Melanie O said:
Access 2002 on Windows XP

I have cascading combo boxes on a continuous subform. The first combo box
should filter the results of the second, and the second for the third. This
has always worked on a main form with a table structure using an auto-number
field as the primary key, a text field showing the name of the item, and a
number field as the foreign key to the first table's auto-numbered primary
key. I read somewhere that this setup won't work on a continuous form. The
post said I'd have to make the primary key field the actual text that I want
to show on the combo box, thus eliminating the auto-number field and only
having the text of the item in the table, along with a text field as the
foreign key when necessary.

I was wondering if there is a different way to achieve the same results, as
I don't want to store the text value in my table, and the third combo box has
quite a lot of long-worded items.

Thanks in advance for any help.
Melanie
Click to expand...
Click to expand...
 
Melanie,

This general strategy can work with any number of combo boxes. Create
another form for the 3rd combo box, and set its RowSource based on the value
written to the textbox that contains the selection from the 2nd. Assuming
you’d like to input these three values in succession, you could simply modify
the AfterUpdate event of the initial combo box:

‘ AfterUpdate event of first combo box (on main continuous form)

‘ Get second value
DoCmd.OpenForm _
FormName:="YourFormForTheSecondComboBox", _
View:=acNormal, _
WindowMode:=acDialog

‘ Get third value
DoCmd.OpenForm _
FormName:="YourFormForTheThirdComboBox", _
View:=acNormal, _
WindowMode:=acDialog

‘ Set the focus to the next control
Me!YourNextFormControl.SetFocus

The AfterUpdate event of the dialog form’s combo box should do 2 things:
1) Write the foreign key (FK) to a non-visible control on the continuous form
2) Close itself

In each case, a visible control then displays the text corresponding to the
foreign key with a DLookup function call on your lookup table (LUT):

=IIf(IsNull([YourFK]),"",DLookup("NameofYourTextDescriptionField","YourLUT","YourLUTKeyField=" & [YourFK]))

See VBA Help on DLookup for additional information on the syntax.

Hope that helps.
Sprinks

Melanie O said:
Thanks for the info. How could this work if I had three combo boxes?

Sprinks said:
Hi, Melanie.

The problem with cascading combo boxes on a continuous subform is that there
can be only one Row Source for each combo box. So, if the Row Source does
not include values for previously entered records, Access doesn't know how to
display the data, so it seems to "disappear".

The solution is to replace the combo box with a textbox and choose your 2nd
value from a second form. In the following example, after a user has
selected a Steel Type, a form is displayed in dialog mode to select the sizes
associated with that type. Then the focus is returned to the next control.

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

The 2nd CB's Row Source is:

SELECT tblSteelSizes.SteelSizeID, tblSteelSizes.SteelType,
tblSteelSizes.SteelSize, tblSteelSizes.LBPerLF, tblSteelSizes.SFPerLF FROM
tblSteelSizes WHERE
tblSteelSizes.SteelType=Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!cboSteelType ORDER BY tblSteelSizes.SteelSize;

The selection is copied to the original form in the CB's AfterUpdate event:

Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub

This control on the main form is bound to the foreign key, and is not
visible. A visible control displays the text associated with the selection:

=IIf(IsNull([txtSteelSizeID]),"",DLookUp("SteelSize","tblSteelSizes","SteelSizeID=" & [txtSteelSizeID]))

Hope this helps.
Sprinks

Melanie O said:
Access 2002 on Windows XP

I have cascading combo boxes on a continuous subform. The first combo box
should filter the results of the second, and the second for the third. This
has always worked on a main form with a table structure using an auto-number
field as the primary key, a text field showing the name of the item, and a
number field as the foreign key to the first table's auto-numbered primary
key. I read somewhere that this setup won't work on a continuous form. The
post said I'd have to make the primary key field the actual text that I want
to show on the combo box, thus eliminating the auto-number field and only
having the text of the item in the table, along with a text field as the
foreign key when necessary.

I was wondering if there is a different way to achieve the same results, as
I don't want to store the text value in my table, and the third combo box has
quite a lot of long-worded items.

Thanks in advance for any help.
Melanie
Click to expand...
Click to expand...
Click to expand...
 
Sprinks,

Thanks so much for the help. I finally got it to work!

Melanie

Sprinks said:
Melanie,

This general strategy can work with any number of combo boxes. Create
another form for the 3rd combo box, and set its RowSource based on the value
written to the textbox that contains the selection from the 2nd. Assuming
you’d like to input these three values in succession, you could simply modify
the AfterUpdate event of the initial combo box:

‘ AfterUpdate event of first combo box (on main continuous form)

‘ Get second value
DoCmd.OpenForm _
FormName:="YourFormForTheSecondComboBox", _
View:=acNormal, _
WindowMode:=acDialog

‘ Get third value
DoCmd.OpenForm _
FormName:="YourFormForTheThirdComboBox", _
View:=acNormal, _
WindowMode:=acDialog

‘ Set the focus to the next control
Me!YourNextFormControl.SetFocus

The AfterUpdate event of the dialog form’s combo box should do 2 things:
1) Write the foreign key (FK) to a non-visible control on the continuous form
2) Close itself

In each case, a visible control then displays the text corresponding to the
foreign key with a DLookup function call on your lookup table (LUT):

=IIf(IsNull([YourFK]),"",DLookup("NameofYourTextDescriptionField","YourLUT","YourLUTKeyField=" & [YourFK]))

See VBA Help on DLookup for additional information on the syntax.

Hope that helps.
Sprinks

Melanie O said:
Thanks for the info. How could this work if I had three combo boxes?

Sprinks said:
Hi, Melanie.

The problem with cascading combo boxes on a continuous subform is that there
can be only one Row Source for each combo box. So, if the Row Source does
not include values for previously entered records, Access doesn't know how to
display the data, so it seems to "disappear".

The solution is to replace the combo box with a textbox and choose your 2nd
value from a second form. In the following example, after a user has
selected a Steel Type, a form is displayed in dialog mode to select the sizes
associated with that type. Then the focus is returned to the next control.

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

The 2nd CB's Row Source is:

SELECT tblSteelSizes.SteelSizeID, tblSteelSizes.SteelType,
tblSteelSizes.SteelSize, tblSteelSizes.LBPerLF, tblSteelSizes.SFPerLF FROM
tblSteelSizes WHERE
tblSteelSizes.SteelType=Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!cboSteelType ORDER BY tblSteelSizes.SteelSize;

The selection is copied to the original form in the CB's AfterUpdate event:

Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub

This control on the main form is bound to the foreign key, and is not
visible. A visible control displays the text associated with the selection:

=IIf(IsNull([txtSteelSizeID]),"",DLookUp("SteelSize","tblSteelSizes","SteelSizeID=" & [txtSteelSizeID]))

Hope this helps.
Sprinks

:

Access 2002 on Windows XP

I have cascading combo boxes on a continuous subform. The first combo box
should filter the results of the second, and the second for the third. This
has always worked on a main form with a table structure using an auto-number
field as the primary key, a text field showing the name of the item, and a
number field as the foreign key to the first table's auto-numbered primary
key. I read somewhere that this setup won't work on a continuous form. The
post said I'd have to make the primary key field the actual text that I want
to show on the combo box, thus eliminating the auto-number field and only
having the text of the item in the table, along with a text field as the
foreign key when necessary.

I was wondering if there is a different way to achieve the same results, as
I don't want to store the text value in my table, and the third combo box has
quite a lot of long-worded items.

Thanks in advance for any help.
Melanie
Click to expand...
Click to expand...
Click to expand...
 
I need some more help. I got the combo boxes to work using just a main form,
but when I change the form to a continuous subform and put it on a main form,
the references to the subform textboxes don't work. What is the correct way
to reference subform controls from a different form?

Thanks,
Melanie

Sprinks said:
Melanie,

This general strategy can work with any number of combo boxes. Create
another form for the 3rd combo box, and set its RowSource based on the value
written to the textbox that contains the selection from the 2nd. Assuming
you’d like to input these three values in succession, you could simply modify
the AfterUpdate event of the initial combo box:

‘ AfterUpdate event of first combo box (on main continuous form)

‘ Get second value
DoCmd.OpenForm _
FormName:="YourFormForTheSecondComboBox", _
View:=acNormal, _
WindowMode:=acDialog

‘ Get third value
DoCmd.OpenForm _
FormName:="YourFormForTheThirdComboBox", _
View:=acNormal, _
WindowMode:=acDialog

‘ Set the focus to the next control
Me!YourNextFormControl.SetFocus

The AfterUpdate event of the dialog form’s combo box should do 2 things:
1) Write the foreign key (FK) to a non-visible control on the continuous form
2) Close itself

In each case, a visible control then displays the text corresponding to the
foreign key with a DLookup function call on your lookup table (LUT):

=IIf(IsNull([YourFK]),"",DLookup("NameofYourTextDescriptionField","YourLUT","YourLUTKeyField=" & [YourFK]))

See VBA Help on DLookup for additional information on the syntax.

Hope that helps.
Sprinks

Melanie O said:
Thanks for the info. How could this work if I had three combo boxes?

Sprinks said:
Hi, Melanie.

The problem with cascading combo boxes on a continuous subform is that there
can be only one Row Source for each combo box. So, if the Row Source does
not include values for previously entered records, Access doesn't know how to
display the data, so it seems to "disappear".

The solution is to replace the combo box with a textbox and choose your 2nd
value from a second form. In the following example, after a user has
selected a Steel Type, a form is displayed in dialog mode to select the sizes
associated with that type. Then the focus is returned to the next control.

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

The 2nd CB's Row Source is:

SELECT tblSteelSizes.SteelSizeID, tblSteelSizes.SteelType,
tblSteelSizes.SteelSize, tblSteelSizes.LBPerLF, tblSteelSizes.SFPerLF FROM
tblSteelSizes WHERE
tblSteelSizes.SteelType=Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!cboSteelType ORDER BY tblSteelSizes.SteelSize;

The selection is copied to the original form in the CB's AfterUpdate event:

Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub

This control on the main form is bound to the foreign key, and is not
visible. A visible control displays the text associated with the selection:

=IIf(IsNull([txtSteelSizeID]),"",DLookUp("SteelSize","tblSteelSizes","SteelSizeID=" & [txtSteelSizeID]))

Hope this helps.
Sprinks

:

Access 2002 on Windows XP

I have cascading combo boxes on a continuous subform. The first combo box
should filter the results of the second, and the second for the third. This
has always worked on a main form with a table structure using an auto-number
field as the primary key, a text field showing the name of the item, and a
number field as the foreign key to the first table's auto-numbered primary
key. I read somewhere that this setup won't work on a continuous form. The
post said I'd have to make the primary key field the actual text that I want
to show on the combo box, thus eliminating the auto-number field and only
having the text of the item in the table, along with a text field as the
foreign key when necessary.

I was wondering if there is a different way to achieve the same results, as
I don't want to store the text value in my table, and the third combo box has
quite a lot of long-worded items.

Thanks in advance for any help.
Melanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top