Record Navigation with ListBox

[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

 

[Jeefgeorge]

Almost forgot, when debugging, the error is at
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
in the ListBox after update code

 

[Allen Browne]

Suggestions for list box:
1. Check that the list box (StdNoList) is unbound.
2. After a FindFirst, test NoMatch, not EOF.
3. Be explicit about the kind of Recordset you want (rather than just
Object.)
4. Explicitly save any edits before moving to another record.

ListBox:
Private Sub StdNoList_AfterUpdate() 'Go to Bid Data for list selection
Dim rs As DAO.Recordset
If Not IsNull(Me.StdNoList) Then
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
rs.FindFirst "StdNo = """ & Me.StdNoList & """"
If rs.NoMatch Then
MsgBox "Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End Sub

Further suggestions for Form_Current:
1. Don't assign anything to Prefix if it is bound to a field. (This would
dirty the record as soon as you move there.)
2. Handle the case where StdNo is null. (Even if it is primary key, it could
be Null at a new record.)
3. I'm not sure why the list box needs requerying when you move record.

Private Sub Form_Current()
Dim strFirst As String
If Me.NewRecord Or IsNull(Me.StdNo) Then
Me.StdNoList = Null
Me.Prefix = Null
Else
strFirst = Left(Me.StdNo, 1)
Me.Prefix = strFirst
End If
'Me.StdNoList.Requery
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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