J
Jeefgeorge
I have a form based on a [StdLineItems] table, whose primary key is [StdNo].
The first character in the [StdNo] identifies which section the item is in.
The section characters are stored in a [StdNoPrefix] table. On my form, I
have a two column ComboBox [Prefix] which shows the section. The first column
(bound and hidden) is the [Prefix] field, the second column is the
[Description]. Also I have a three column ListBox [StdNoList] which shows
the [StdLineItems] assocaited with the section selected in the ComboBox. The
[StdNo] field is the bound column for the ListBox. The selection in the
ListBox then populates [StdNo], [Description], [Unit], and [Spec] TextBoxes
also a subform is linked by the [StdNo] TextBox.
For some clarification on naming, the [StdNo] TextBox shows the same StdNo
as highlighted in the StdNoList - this TextBox is referenced in my code.
My problem is that when I use my custom navigation controls, the ComboBox
and ListBox show the current section and line item correctly. But if I select
a different section and line item the attempts to change the current record
(fails to update, Error 3022 - would create duplicate records in the index).
I would like the form to navigate to the line item selected.
Here is my code:
ListBox:
Private Sub StdNoList_AfterUpdate() 'Go to Bid Data for list selection
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "StdNo = '" & Me.StdNoList & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
ComboBox:
Private Sub Prefix_AfterUpdate()
Me.StdNoList.Requery
End Sub
Form:
Private Sub Form_Current()
Dim First As String
Me.StdNoList = Me.StdNo
First = Left(Me.StdNo, 1)
Me.Prefix = First
Me.StdNoList.Requery
End Sub
The first character in the [StdNo] identifies which section the item is in.
The section characters are stored in a [StdNoPrefix] table. On my form, I
have a two column ComboBox [Prefix] which shows the section. The first column
(bound and hidden) is the [Prefix] field, the second column is the
[Description]. Also I have a three column ListBox [StdNoList] which shows
the [StdLineItems] assocaited with the section selected in the ComboBox. The
[StdNo] field is the bound column for the ListBox. The selection in the
ListBox then populates [StdNo], [Description], [Unit], and [Spec] TextBoxes
also a subform is linked by the [StdNo] TextBox.
For some clarification on naming, the [StdNo] TextBox shows the same StdNo
as highlighted in the StdNoList - this TextBox is referenced in my code.
My problem is that when I use my custom navigation controls, the ComboBox
and ListBox show the current section and line item correctly. But if I select
a different section and line item the attempts to change the current record
(fails to update, Error 3022 - would create duplicate records in the index).
I would like the form to navigate to the line item selected.
Here is my code:
ListBox:
Private Sub StdNoList_AfterUpdate() 'Go to Bid Data for list selection
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "StdNo = '" & Me.StdNoList & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
ComboBox:
Private Sub Prefix_AfterUpdate()
Me.StdNoList.Requery
End Sub
Form:
Private Sub Form_Current()
Dim First As String
Me.StdNoList = Me.StdNo
First = Left(Me.StdNo, 1)
Me.Prefix = First
Me.StdNoList.Requery
End Sub