MS Access VBA - First Record In List is Overwritten on Form Close?

Question

I just learning how to create forms in Microsoft Access 2013 and using VBA programming to control but I'm having an issue I don't quite understand.

I have created a form with a List Box where the source originates from a query that contains the following fields the Query Builder in order from left to right:

   |ParentNumber|ParentName|ChildNumber|ChildName|
   |------------|----------|-----------|---------|
   |------------|----------|-----------|---------|
   |------------|----------|-----------|---------|

Some fields from the Query are hidden with a Column Width of 0".

I also have 4 Text Box below the List Box corresponding to the appropriate ParentNumber, ParentName, ChildNumber, and ChildName values.

When I select a record in the List Box, it populates the data to the appropriate Text Box.

When the form first loads, the first row in the List Box is selected:

Private Sub Form_Load()

On Error Resume Next
    DoCmd.GoToRecord , , acFirst
    Me.List = Me.List.ItemData(0)
End Sub

The issue is that if I select a different row, then close the form, and reopen the form, the first row in the List Box is overwritten with the last selected row before the form is closed.

Even if I start out with any other row selected initially when the form opens, the first row is always overwritten by the last selected row when the form is closed.

The following subroutine handles the update of the Text Box data:

Private Sub List_AfterUpdate()
    Dim rowIndex As Integer
    rowIndex = Me.List.ListIndex

    Me.textBox_ParentNumber = Me.List.Column(3, rowIndex)
    Me.textBox_ParentName = Me.List.Column(4, rowIndex)
    Me.textBox_ChildNumber = Me.List.Column(6, rowIndex)
    Me.textBox_ChildName = Me.List.Column(7, rowIndex)
End Sub

I found a somewhat similar problem to mine, but I tried the suggested solution, which didn't seem to work: MS Access - First record in table is overwritten on form close

I'm completely baffled as to what could cause this based on the code above.

What's my issue?

Thanks.

I'd set a break point in your code on the rowIndex line and check the value. — aduguid
@aduguid Me.List.ListIndex return the Integer value pertaining to the row I selected from the List Box, i.e. 0, 1, 2, 3, ... so I'm perplex what is causing this issue — Pangu
Does the form itself have a recordsource and are the textboxes bound to any columns? — aduguid
@aduguid yes the form has a recordsource to a table and the textboxes are bounded to a control source of the appropriate field from the table....sorry for asking as I'm new to this but is this a problem? — Pangu

wazz wazz · Accepted Answer · 2018-02-15T06:35:10

Few things

Private Sub Form_Load()

    'remove this so you can see errors.
    'On Error Resume Next

    ' this goes to the first record of the *form*, not the list.
    ' you might want this, or not. 
    DoCmd.GoToRecord , , acFirst

    ' ItemData returns the data in the *bound column* of the list, 
    ' the data from one specific column. the list is set to that 
    ' data every time the form loads. not what you want; remove it.
    'Me.List = Me.List.ItemData(0)
End Sub

This should get rid of the problem. What you want to do next is another question.

MS Access VBA - First Record In List is Overwritten on Form Close?

3 Answers