Changing the rowsource of a combo box

[Bill Murphy]

I have a simple form where all of the fields are unbound. There is one
combo box which is initially populated by the query qryChaseBAIFilesCombo,
and it is properly populated when the form first opens. The form has two
radio buttons which allow the user to change the rowsource of the combo box
as shown in the code below. However, when the user clicks on the second
radio button the combo box and the related text box txtFileName are blank.
The rowsource did change, since I can then drop down the list and see the
results of qryBatchIDForCombosFW, and I can click on an item in the list and
see that the combo and the text box then populate as expected.

Is there any additional code I should add to avoid the blank combo box
problem?

Bill


Private Sub optFiles_AfterUpdate()

If Forms!frmChasePrintSummaryReport!optFiles = 1 Then
Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryChaseBAIFilesCombo"

Else

Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryBatchIDForCombosFW"

End If

Forms!frmChasePrintSummaryReport!cboBatchID.Requery

Forms!frmChasePrintSummaryReport!cboBatchID.SetFocus

Forms!frmChasePrintSummaryReport!txtFileName =
Forms!frmChasePrintSummaryReport![cboBatchID].Column(2)

End Sub

 

[Guest]

I would think Storing the combo box value in a variable at the top of your
radio button code, and then using that value again afterwards.

 

[Marshall Barton]

I have a simple form where all of the fields are unbound. There is one
combo box which is initially populated by the query qryChaseBAIFilesCombo,
and it is properly populated when the form first opens. The form has two
radio buttons which allow the user to change the rowsource of the combo box
as shown in the code below. However, when the user clicks on the second
radio button the combo box and the related text box txtFileName are blank.
The rowsource did change, since I can then drop down the list and see the
results of qryBatchIDForCombosFW, and I can click on an item in the list and
see that the combo and the text box then populate as expected.

Is there any additional code I should add to avoid the blank combo box
problem?

Bill


Private Sub optFiles_AfterUpdate()

If Forms!frmChasePrintSummaryReport!optFiles = 1 Then
Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryChaseBAIFilesCombo"

Else

Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryBatchIDForCombosFW"

End If

Forms!frmChasePrintSummaryReport!cboBatchID.Requery

Forms!frmChasePrintSummaryReport!cboBatchID.SetFocus

Forms!frmChasePrintSummaryReport!txtFileName =
Forms!frmChasePrintSummaryReport![cboBatchID].Column(2)

End Sub

A Requery after setting the RowSource is redundant. Access
automatically requeries when you set the row source.

If the combo box's value is not in the row source, then
there will not be a selected row. Which item in the new row
source do you want to use?

 

[Bill Murphy]

Marsh,

I removed the requery as you suggested.

I would like to display the second column of the query in the combo box, and
this value is in the row source. Here are the three columns in the query:

Column 1 NumericID An autonumber which is not displayed in the
dropdown
Column 2 BatchID The value needed in the combo
Column 3 txtFileName This is displayed in the related text box on
the form

The combo box is bound to column 1 (zero based, so this is actually column 2
above). I would like to get the first row in the query, and I have the
default value of the combo box set to [cboBatchID].[ItemData](0).

Bill

I have a simple form where all of the fields are unbound. There is one
combo box which is initially populated by the query qryChaseBAIFilesCombo,
and it is properly populated when the form first opens. The form has two
radio buttons which allow the user to change the rowsource of the combo
box
as shown in the code below. However, when the user clicks on the second
radio button the combo box and the related text box txtFileName are blank.
The rowsource did change, since I can then drop down the list and see the
results of qryBatchIDForCombosFW, and I can click on an item in the list
and
see that the combo and the text box then populate as expected.

Is there any additional code I should add to avoid the blank combo box
problem?

Bill


Private Sub optFiles_AfterUpdate()

If Forms!frmChasePrintSummaryReport!optFiles = 1 Then
Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryChaseBAIFilesCombo"

Else

Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryBatchIDForCombosFW"

End If

Forms!frmChasePrintSummaryReport!cboBatchID.Requery

Forms!frmChasePrintSummaryReport!cboBatchID.SetFocus

Forms!frmChasePrintSummaryReport!txtFileName =
Forms!frmChasePrintSummaryReport![cboBatchID].Column(2)

End Sub

A Requery after setting the RowSource is redundant. Access
automatically requeries when you set the row source.

If the combo box's value is not in the row source, then
there will not be a selected row. Which item in the new row
source do you want to use?

 

[Marshall Barton]

Using the DefaultValue property on an unbound control is not
particularly useful. The DefaultValue is really only useful
for a bound control on a new record.

Right after you set the row source, set the value of the
combo box:
Me.cboBatchID = Me.cboBatchID.ItemData(0)
--
Marsh
MVP [MS Access]

I would like to display the second column of the query in the combo box, and
this value is in the row source. Here are the three columns in the query:

Column 1 NumericID An autonumber which is not displayed in the
dropdown
Column 2 BatchID The value needed in the combo
Column 3 txtFileName This is displayed in the related text box on
the form

The combo box is bound to column 1 (zero based, so this is actually column 2
above). I would like to get the first row in the query, and I have the
default value of the combo box set to [cboBatchID].[ItemData](0).

I have a simple form where all of the fields are unbound. There is one
combo box which is initially populated by the query qryChaseBAIFilesCombo,
and it is properly populated when the form first opens. The form has two
radio buttons which allow the user to change the rowsource of the combo
box
as shown in the code below. However, when the user clicks on the second
radio button the combo box and the related text box txtFileName are blank.
The rowsource did change, since I can then drop down the list and see the
results of qryBatchIDForCombosFW, and I can click on an item in the list
and
see that the combo and the text box then populate as expected.

Is there any additional code I should add to avoid the blank combo box
problem?


Private Sub optFiles_AfterUpdate()

If Forms!frmChasePrintSummaryReport!optFiles = 1 Then
Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryChaseBAIFilesCombo"

Else

Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryBatchIDForCombosFW"

End If

Forms!frmChasePrintSummaryReport!cboBatchID.Requery

Forms!frmChasePrintSummaryReport!cboBatchID.SetFocus

Forms!frmChasePrintSummaryReport!txtFileName =
Forms!frmChasePrintSummaryReport![cboBatchID].Column(2)

End Sub

A Requery after setting the RowSource is redundant. Access
automatically requeries when you set the row source.

If the combo box's value is not in the row source, then
there will not be a selected row. Which item in the new row
source do you want to use?

 

[Bill Murphy]

Marsh,

Thanks. That worked great.

Bill

Using the DefaultValue property on an unbound control is not
particularly useful. The DefaultValue is really only useful
for a bound control on a new record.

Right after you set the row source, set the value of the
combo box:
Me.cboBatchID = Me.cboBatchID.ItemData(0)
--
Marsh
MVP [MS Access]

I would like to display the second column of the query in the combo box,
and
this value is in the row source. Here are the three columns in the query:

Column 1 NumericID An autonumber which is not displayed in the
dropdown
Column 2 BatchID The value needed in the combo
Column 3 txtFileName This is displayed in the related text box on
the form

The combo box is bound to column 1 (zero based, so this is actually column
2
above). I would like to get the first row in the query, and I have the
default value of the combo box set to [cboBatchID].[ItemData](0).

Bill Murphy wrote:

I have a simple form where all of the fields are unbound. There is one
combo box which is initially populated by the query
qryChaseBAIFilesCombo,
and it is properly populated when the form first opens. The form has
two
radio buttons which allow the user to change the rowsource of the combo
box
as shown in the code below. However, when the user clicks on the second
radio button the combo box and the related text box txtFileName are
blank.
The rowsource did change, since I can then drop down the list and see
the
results of qryBatchIDForCombosFW, and I can click on an item in the list
and
see that the combo and the text box then populate as expected.

Is there any additional code I should add to avoid the blank combo box
problem?


Private Sub optFiles_AfterUpdate()

If Forms!frmChasePrintSummaryReport!optFiles = 1 Then
Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryChaseBAIFilesCombo"

Else

Forms!frmChasePrintSummaryReport!cboBatchID.RowSource =
"qryBatchIDForCombosFW"

End If

Forms!frmChasePrintSummaryReport!cboBatchID.Requery

Forms!frmChasePrintSummaryReport!cboBatchID.SetFocus

Forms!frmChasePrintSummaryReport!txtFileName =
Forms!frmChasePrintSummaryReport![cboBatchID].Column(2)

End Sub


A Requery after setting the RowSource is redundant. Access
automatically requeries when you set the row source.

If the combo box's value is not in the row source, then
there will not be a selected row. Which item in the new row
source do you want to use?