subform cbo changing all records

[lmv]

HELP PLEASE!
Using the following code... for sorting my 2nd cbo is resetting ALL records
on purchaseordersubform instead of only the one I am working on. Can someone
tell me what code I need to add to make it only the current record I am in?

2nd question I also want to sort this ascending and I don't know the code to
put within the SQL.

Thanks!

Private Sub cboCategoryID_AfterUpdate()
Dim ProductName As String

ProductName = "SELECT [ProductsTTL].[ProductID],
[ProductsTTL].[CategoryID], [ProductsTTL].[ProductName] " & _
"FROM ProductsTTL " & _
"WHERE [CategoryID] = " & Me.cboCategoryID.Value

Me.cboProductName.RowSource = ProductName
Me.cboProductName.Requery

End Sub

 

[Sandra Daigle]

First - to include a sort just add an Order By clause to the SQL:

ProductName = "SELECT [ProductsTTL].[ProductID], _
[ProductsTTL].[CategoryID], [ProductsTTL].[ProductName] " & _
"FROM ProductsTTL " & _
"WHERE [CategoryID] = " & Me.cboCategoryID.Value & _
" Order By [ProductsTTL].[ProductName]"

Assuming that the subform is a continuous form the problem you are seeing is
due to the fact that there is really only one combo control - it is just
displayed multiple times. So whatever you have for current rowsource will
apply to all rows. This makes it difficult to use synchronized combos on
continuous forms. However, there is a fairly easy workaround. To create the
effect of a synchronized combo in a continuous form create another textbox
control, include the display column of the combo in the Recordsource query
of the form (join the foreign table and drag in the column). Position and
size the new textbox so that you can place it directly over the combo box
allowing only the down-arrow portion of the combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not be fit the criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem
ACC2000: Combo Box in Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm

 

[lmv]

Thanks for the explanation of my problem that was VERY helpful. At least now
I can stop trying to make something work that can't.

I had tried what you said before about the sort order but nothing appears in
my field for some reason when I add the "ORDER BY" to the VBA SQL code after
the WHERE. It doesn't error it just doesn't do anything...??

As far as the links... I had looked at your examples awhile ago and I LOVE
the idea of them but I am not saavy enough to impliment them though I would
love to and I did TRY (for hours)! But, again I tried even the txt box...
product Name over the ProductID which is the second combo box and I just
can't get it to work properly.

I will keep trying since I can't really logically figure why this isn't
working.

Thanks again!!
lmv

First - to include a sort just add an Order By clause to the SQL:

ProductName = "SELECT [ProductsTTL].[ProductID], _
[ProductsTTL].[CategoryID], [ProductsTTL].[ProductName] " & _
"FROM ProductsTTL " & _
"WHERE [CategoryID] = " & Me.cboCategoryID.Value & _
" Order By [ProductsTTL].[ProductName]"

Assuming that the subform is a continuous form the problem you are seeing is
due to the fact that there is really only one combo control - it is just
displayed multiple times. So whatever you have for current rowsource will
apply to all rows. This makes it difficult to use synchronized combos on
continuous forms. However, there is a fairly easy workaround. To create the
effect of a synchronized combo in a continuous form create another textbox
control, include the display column of the combo in the Recordsource query
of the form (join the foreign table and drag in the column). Position and
size the new textbox so that you can place it directly over the combo box
allowing only the down-arrow portion of the combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not be fit the criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem
ACC2000: Combo Box in Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

HELP PLEASE!
Using the following code... for sorting my 2nd cbo is resetting ALL
records on purchaseordersubform instead of only the one I am working
on. Can someone tell me what code I need to add to make it only the
current record I am in?

2nd question I also want to sort this ascending and I don't know the
code to put within the SQL.

Thanks!

Private Sub cboCategoryID_AfterUpdate()
Dim ProductName As String

ProductName = "SELECT [ProductsTTL].[ProductID],
[ProductsTTL].[CategoryID], [ProductsTTL].[ProductName] " & _
"FROM ProductsTTL " & _
"WHERE [CategoryID] = " & Me.cboCategoryID.Value

Me.cboProductName.RowSource = ProductName
Me.cboProductName.Requery

End Sub