CODEWITHSUNDEEP
vbams-access
Pangu
Pangu

Reputation: 3819

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

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.

Upvotes: 0

Views: 1252

Answers (3)

marg
marg

Reputation: 1

I had same problem, After hours of verification - In my case - the problem was that the MainForm , And SubForm (table) - was binded to the same recordset. I just Un-Binded data RS from MainForm.

Upvotes: 0

Cahaba Data
Cahaba Data

Reputation: 622

Your form should be bound to a record set (table or query).

Upvotes: 0

wazz
wazz

Reputation: 5068

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.

Upvotes: 2

Related Questions