Cascading ComboBox text disappears

[Thomas]

Hello
I have a form that I have based on the NorthView Traders Orders (Form). I
needed to add a ComboBox to the Orders_Subform that list Categories and then
list only the Products with in that Category to ProductID ComboBox. I have
it working the only problem is that the ProductName (the text) disappears
when you close and reopen the Form. Is there any code for the Form on
Activate or ProductID ComboBox event that will refresh the ProductName (the
text).The Products ID# is save but you can't see it because I have the
ProductID ComboBox column widths set at 0"; 2".
Being a Access newbe is my thinking all wrong for the method to limit the
products.
As I am dealing with over 1000 Products

Any suggestions appreciated.

Thanks in advance

Categories ComboBox :

Private Sub Categories_AfterUpdate()
Me.ProductID.RowSource = "SELECT ProductID,ProductName FROM" & _
" Products WHERE CategoryID = " & Me.Categories & _
" ORDER BY ProductName"
Me.ProductID = Me.ProductID.ItemData(1)
End Sub


ProductsID ComboBox :

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

strFilter = "ProductID = " & Me!ProductID

Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub



Private Sub ProductID_BeforeUpdate(Cancel As Integer)

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsLoaded("Orders Subform") Then
strMsg = "You can't add or edit a Product Name when you open Orders
Subform as a standalone form."
intStyle = vbOKOnly
strTitle = "Can't Add or Change Product Name"
MsgBox strMsg, intStyle, strTitle
Me!ProductID.Undo
Me.Undo
End If

End Sub

The System at the store:
WinXP
Access 2003

 

[GVaught]

If I understand you correctly your combo box will come back empty after you
close and reopen the form as you have not picked a category. What you may
have to do is load all the category choices to the Category combo box using
the OnLoad Event of the form. Then upon a selection fill the product combo
box. The form won't remember your last selection unless you make provisions
to saving that information, which would not be a good idea.

 

[Thomas]

The Form has the Category name in the field saved and all the other info as
well the only field blank is the ProductID ComboBox for the ProductName on
reopening the form on a previous order.

The info from the Orders_SubForm is saved in a Table Orders_Details
OrderID
CategoryID ComboBox
ProductID ComboBox
UnitPrice TextBox
Quantity TextBox
Discount TextBox
ExtendedPrice TextBox

 

[tina]

is your subform Single Form view, Continuous Forms view, or Datasheet view?
if it's single form view, it should show the correct filtered combo box
Product list when you initially open the form, but if it doesn't, try adding
the following line of code to the form's current event procedure, as

Me!MyProductIDComboBoxName.Requery

hth

 

[Thomas]

First I would like to say thank you for your input

The form is Datasheet (Default View)

I added the code to the forms current event procedure
Me!ProductID.Requery
and now when I select category ComboBox the select product ComboBox clears
the text from the previously selected row above it.

 

[tina]

this is normal, not a bug; and if you understand how combo boxes work, it
makes perfect sense. unfortunately it's also unsettling to users, who may
think the data has been deleted, and just generally a pain.

i've never seen a slick workaround offered here for a data entry form
(though there may well have been, i don't read every single post/answer). i
use a fairly clunky workaround, but it only works in Continuous form view,
not Datasheet view.

sorry i wasn't able to help.

 

[Joan Wild]

First I would like to say thank you for your input

The form is Datasheet (Default View)

You need to understand that although it looks like several comboboxes on
screen, it is just one. It's displayed many times but there is still only
one control, so changing its properties changes all of the occurrances of
that control.

You can place a textbox, bound to the CategoryID over the CategoryID
combobox (so that just the dropdown shows). Make the textbox enabled(false)
and locked(true), and also set it's tab stop to false.

This will show the current record's CategoryID, but when the combobox is
dropped down it will cover the textbox, allowing a new value to be
selected.

 

[tina]

that's the "clunky workaround" i use (except i use a DLookup expression in
the text box to show the text, rather than binding to the ID field). but
i've never used it in a datasheet because i can't figure out how to overlay
the textbox column on top of the combo box column. i can set it up that way
in design view, but datasheet view always shows two separate columns. how do
you change that?

 

[Rick Brandt]

that's the "clunky workaround" i use (except i use a DLookup expression in
the text box to show the text, rather than binding to the ID field). but
i've never used it in a datasheet because i can't figure out how to overlay
the textbox column on top of the combo box column. i can set it up that way
in design view, but datasheet view always shows two separate columns. how do
you change that?

You can't. You could build a continuous form that "looks like" a datasheet and
then do it easily. I seldom use actual datasheets for anything but very simple
situations.