Getting all the records via DAO

[dave b via AccessMonster.com]

I've taken over a project that uses DAO programming to obtain records from
various tables. I've added a comments table, where multiple records can
exist for each Product/Lot. This works fine. I was able to create a form,
add navigation buttons, and the user can scroll through the existing records
and add new records.

I'm trying to implement the same changes to the Downtime form. This form was
originally created to individually add the appropriate information. Several
records exist for the same Product/Lot. However, when I try to retrieve all
records for each Product/Lot, the recordcount shows only 1 record, even when
there are multiple records that meet the criteria.

The code generally looks like this:
----------------------------------------------------------------------
Function Open()

Dim dbLocal as Database
Dim snpData as DAO.Recordset

Set dBLocal = CurrentDb
Set snpData = dBLocal.OpenRecordset(SQLString)
..
..
..
Exit Function

End Function

Function SQLString() As String

On Error GoTo Error_Load

SQLString = "SELECT *" & vbCrLf
SQLString = SQLString & " FROM tblDowntime" & vbCrLf
SQLString = SQLString & " WHERE (tblDowntime.ProductID &
tblDowntime.LotNo ='" & [Forms]![frmMnMenu]![cboProductID] & [Forms]!
[frmMnMenu]![cboLotNumber] & "')"

Exit Function

Error_Load:
Beep
MsgBox "The following Error has Occurred: " & vbCrLf & _
Err.Description, vbCritical, "Error!"
Exit Function
End Function

 

[Andrew Tapp]

I think you'll find that if you iterate through the recordset that all the
records are there, it's just the recordcount that indicates 1.

In order to make sure the recordcount reads the correct value you should
move to the bottom of your recordset and then back to the top e.g.

If Not snpData.EOF Then
snpData.MoveLast
EndIf

If Not snpData.BOF Then
snpData.MoveFirst
EndIf

Hope this helps.

 

[dave b via AccessMonster.com]

Thank you. That's exactly what I found out.

I think you'll find that if you iterate through the recordset that all the
records are there, it's just the recordcount that indicates 1.

In order to make sure the recordcount reads the correct value you should
move to the bottom of your recordset and then back to the top e.g.

If Not snpData.EOF Then
snpData.MoveLast
EndIf

If Not snpData.BOF Then
snpData.MoveFirst
EndIf

Hope this helps.

I've taken over a project that uses DAO programming to obtain records from
various tables. I've added a comments table, where multiple records can

[quoted text clipped - 46 lines]

Even when I replace SQLString with "tblDowntime" in the Set snpData command,
tyring to retrieve all records, I still get a recordcount of 1.