Problem adding data (1 Viewer)

kirkm

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2008
Messages
1,257
My Forms record source is a query and I'm trying to populate a field from an array called Tracks. Code is
Code:
Set dc = CurrentDb.OpenRecordset(Form_frmHubAddress.RecordSource)
        If dc.RecordCount > 0 Then dc.MoveLast
        If dc.RecordCount = UBound(Tracks) Then
            dc.MoveFirst
            For c = 1 To UBound(Tracks)
                dc.Edit
                dc(3) = Tracks(c)
                dc.MoveNext
            Next
        End If

Seems to be editing the recordset but changes are not reflected in either Form_frmHubAddress or it's underlying table.
Where am I going wrong?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:53
Joined
Feb 28, 2001
Messages
26,996
First, a question: What is Form_frmHubAddress.RecordSource - i.e. what does that contain?

Second, a comment: I believe that before you actually navigate with a recordset, its .RecordCount is 0. You normally have to do an unconditional rs.MoveLast followed by an rs.MoveFirst to populate .RecordCount - though there are other ways to do that navigation. Taken from the MS documentation online,...


Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset–, snapshot–, or forward–only–type Recordset object until all records have been accessed.

If you have not yet accessed ANYTHING then the count will probably be 0. If so, your loop never gets executed.

Even if the .RecordCount issue is the problem, it ALSO matters about the .RecordSource since there IS such a thing as a non-editable query that could nonetheless drive a form in Read-Only mode.
 

kirkm

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2008
Messages
1,257
Interesting points, I had thought an unconditional rs.MoveLast would error if the recordset is empty.
Debugging shows its not empty and the loop is executing. Should it populate the table as it's written?
The recordsource is "Select Performer, Title, Track, Comp from Table9 where Cat= " & Thiscat() & " Order by Track;"
 

MarkK

bit cruncher
Local time
Today, 01:53
Joined
Mar 17, 2004
Messages
8,178
You need to run the .Update method of the Recordset object to save the changes.

Rich (BB code):
With CurrentDb.OpenRecordset(SomeRecordSource)
    Do While Not .EOF
        .Edit
        .Fields(2) = .Fields(0) * .Fields(1)   ' change a field
        .Update                                ' run the Update method
        .MoveNext
    Loop
    .Close
End With
 

kirkm

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2008
Messages
1,257
Thanks very much Mark, that's fixed it. And I should have known!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:53
Joined
Feb 28, 2001
Messages
26,996
To address the "empty recordset" issue, an empty recordset that is freshly opened will have both .BOF and .EOF set to TRUE.

One more question: For this query:

"Select Performer, Title, Track, Comp from Table9 where Cat= " & Thiscat() & " Order by Track;"

What is the data type of ThisCat() and of field Cat? You might need some single-quote characters inside the quoted parts because if that is text mode, what you wrote wouldn't work so well.
 

kirkm

Registered User.
Local time
Today, 21:53
Joined
Oct 30, 2008
Messages
1,257
Suggesting Do while Not .EOF would mean the record count check is obsolete? ThisCat() is a function that returns the numeric field Cat.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:53
Joined
Feb 28, 2001
Messages
26,996
By default, you start at the .BOF point of a recordset. Yes, I would concur that your suggested Do/While construct would obviate the need for knowing the count.
 

Users who are viewing this thread

Top Bottom