Continuance form & combo boxes

[michaelmcmanamon]

Help!!! this is has made me a complete basket case I have a form that I am
trying to Use for Bids & Proposals. It is very basic similar to Orders form
only, My subform has a Category combo that limits my Product choices which
works fine except when I go to the next line or record that line changes the
selection on the first line and so on. If I delete the category field the
whole entire form works great except we have over 4000 products and need a
good way to limit my product choices.
WHAT am I doing Wrong? this is the code I am using:
Option Compare Database
Private Sub Categories_AfterUpdate()
Me.ProductName.Requery
End Sub
Private Sub ProductID_AfterUpdate()
Me.UnitPrice = Me.ProductName.Column(7)
On Error GoTo Error_Routine
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.MoveLast
If StrComp(Me.Bookmark, rs.Bookmark, 0) = 0 Then
Forms![frmMoveToMainForm]![cboCategory].SetFocus
Forms![frmMoveToMainForm]![subMovetoMainForm].Requery
End If
Exit Sub

Error_Routine:
MsgBox "You must be on a record with data"
Exit Sub
End Sub

 

[Armen Stein]

Help!!! this is has made me a complete basket case I have a form that I am
trying to Use for Bids & Proposals. It is very basic similar to Orders form
only, My subform has a Category combo that limits my Product choices which
works fine except when I go to the next line or record that line changes the
selection on the first line and so on. If I delete the category field the
whole entire form works great except we have over 4000 products and need a
good way to limit my product choices.
WHAT am I doing Wrong? this is the code I am using:
Option Compare Database
Private Sub Categories_AfterUpdate()
Me.ProductName.Requery
End Sub
Private Sub ProductID_AfterUpdate()
Me.UnitPrice = Me.ProductName.Column(7)
On Error GoTo Error_Routine
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.MoveLast
If StrComp(Me.Bookmark, rs.Bookmark, 0) = 0 Then
Forms![frmMoveToMainForm]![cboCategory].SetFocus
Forms![frmMoveToMainForm]![subMovetoMainForm].Requery
End If
Exit Sub

Error_Routine:
MsgBox "You must be on a record with data"
Exit Sub
End Sub

Hi Michael,

When you requery a combobox with a different RowSource on one record in
a continuous form, you are changing it for ALL of them.

In your situation, you may need to lock the values on the continuous
form, then drill-down to a detail form where you can limit your combobox
rowsource and edit the record.

It's a hassle, I know. Access just doesn't support having different
combobox rowsources row-by-row in a continuous form.