Odd Behavior Of Combobox and Textbox

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have an unbound combobox in the form header of a continuous form. The
selection in the combobox sets the where clause in a querydef which determines
QryPFrmInventoryManagement. The following code is in the afterupdate event of
the combobox:

Me.RecordSource = "QryPFrmInventoryManagement"

Me!ItemCount = Me.RecordsetClone.RecordCount & " Items"

MsgBox Me.RecordsetClone.RecordCount & " Items"

Everything works fine as long as the query returns one or more records. When the
query does not return any records, the selection disappears in the combobox.
Also, the textbox, ItemCount, does not change from its previous value although
the MsgBox returns 0. Can anyone explain the odd behavior of the combobox and
textbox when the query does not return any records?

Thanks for all help!

Steve
 
Arvin,

Thank you for responding!

The query has Inventory and Reorder Point fields and they can be edited so the
query is updateable.

Steve
 
Hi,
Is that all of the code in the after update?
What is the row source for your combo?
Is the Item Count control in the header as well?
 
Dan,

Thank you for responding!

The three lines in my post are the last three lines of the code in the after
update. Prior to that are these lines of code:
Set QDF = DB.QueryDefs("QryPFrmInventoryManagement")
QDF.SQL = SQLStr
For Each Prm In QDF.Parameters
Prm.Value = Eval(Prm.Name)
Next Prm
and prior to this is code to build up SQLStr.

RowSource Of Combobox:

QryInventoryStatusCriteria InvStatCriteriaID InventoryCriteria
InventoryReorderPoint SortOrder
2 <0 Less Than 2
3 =0 = To 3
4 Between 1 And 3 1 - 3 Items More Than 4
5 Between 4 And 6 4 - 6 Items More Than 5


Yes, ItemCount is also in the form header.

Steve
 
Dan,

Found the answer to both problems!!

1. The value of the combobox is not available until the form opens. Therefore,
the reference to the combobox in the criteria of the query which is the
recordsource of the form has a null value which causes the query to not return
any records when the form opens.

2. The purpose of the form is to display the inventory and reorder point of
products in the database. Therefore, I had turned of Allow Additions. When Allow
Additions is turned off and a selection is made in the combobox which returns no
records, Allow Additions being turned off causes the selection not to be
displayed in the combobox. The work around is to change the code in the
AfterUpdate of the combobox to:
Me.AllowAdditions = True
Me.Requery
Me.AllowAdditions = False

By changing this code, all selections whether they return records or not are
displayed in the combobox.

Steve
 
Back
Top