By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Odd Behavior Of Combobox and Textbox

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Non-updateable queries will always return blank if there are no records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"Steve" <ss****@bellsouth.net> wrote in message
news:5E*******************@newsread1.news.atl.eart hlink.net...
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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 9/29/2003
Nov 12 '05 #2

P: n/a
Arvin,

Thank you for responding!

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

Steve
"Arvin Meyer" <a@m.com> wrote in message
news:#N*************@TK2MSFTNGP11.phx.gbl...
Non-updateable queries will always return blank if there are no records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"Steve" <ss****@bellsouth.net> wrote in message
news:5E*******************@newsread1.news.atl.eart hlink.net...
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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 9/29/2003

Nov 12 '05 #3

P: n/a
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?

--
HTH
Dan Artuso, Access MVP
"Steve" <ss****@bellsouth.net> wrote in message news:pG******************@newsread2.news.atl.earth link.net...
Arvin,

Thank you for responding!

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

Steve
"Arvin Meyer" <a@m.com> wrote in message
news:#N*************@TK2MSFTNGP11.phx.gbl...
Non-updateable queries will always return blank if there are no records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"Steve" <ss****@bellsouth.net> wrote in message
news:5E*******************@newsread1.news.atl.eart hlink.net...
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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 9/29/2003


Nov 12 '05 #4

P: n/a
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 Artuso" <da*****@NoSpampagepearls.com> wrote in message
news:eH**************@TK2MSFTNGP10.phx.gbl...
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?

--
HTH
Dan Artuso, Access MVP
"Steve" <ss****@bellsouth.net> wrote in message

news:pG******************@newsread2.news.atl.earth link.net...
Arvin,

Thank you for responding!

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

Steve
"Arvin Meyer" <a@m.com> wrote in message
news:#N*************@TK2MSFTNGP11.phx.gbl...
Non-updateable queries will always return blank if there are no records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"Steve" <ss****@bellsouth.net> wrote in message
news:5E*******************@newsread1.news.atl.eart hlink.net...
> 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
>
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 9/29/2003



Nov 12 '05 #5

P: n/a
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

"Dan Artuso" <da*****@NoSpampagepearls.com> wrote in message
news:eH**************@TK2MSFTNGP10.phx.gbl...
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?

--
HTH
Dan Artuso, Access MVP
"Steve" <ss****@bellsouth.net> wrote in message

news:pG******************@newsread2.news.atl.earth link.net...
Arvin,

Thank you for responding!

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

Steve
"Arvin Meyer" <a@m.com> wrote in message
news:#N*************@TK2MSFTNGP11.phx.gbl...
Non-updateable queries will always return blank if there are no records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

"Steve" <ss****@bellsouth.net> wrote in message
news:5E*******************@newsread1.news.atl.eart hlink.net...
> 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
>
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 9/29/2003



Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.