Only retrieving first 50 records?!?

W

winsa

Hi

I hope I have posted this in the right place.

I have a query using three tables (see below). Simply viewing the query is
not an issue, however, when I use it as the recordsource for a form, Access
only pulls up the first 50 records, then the rest after a pause. This is
creating havoc with some code that uses Recordset bookmarks. In a custom
"Record x of y" display, it only displays "Record *whatever* of 50" until I
click on the navigation buttons, and then it displays the correct total
record number. In my custom search function, it cannot find any of the
records above 51.

I have read the article from MS regarding query optimisation, but no luck
there. Does it have something to do with the multi-table query in that a
many-to-many relationship is formed because tbl_DEBTORS and tbl_PACODE are
both lookup tables?!?!

Can anyone offer any suggestions or explanations as to why Access is doing
this?

The query SQL is as follows:

SELECT TOP 100 PERCENT tbl_PAMASTER.PAPOLICY_IDX, tbl_PAMASTER.DEBTOR_IDX,
tbl_DEBTORS.DBTR_NAME, tbl_PAMASTER.PAI_CONGREGATION,
tbl_PAMASTER.PAI_INSUREDFIRST, tbl_PAMASTER.PAI_INSUREDINITIAL,
tbl_PAMASTER.PAI_INSUREDLAST, tbl_PAMASTER.PAI_DOB, (CAST(CONVERT(CHAR(8),
CURRENT_TIMESTAMP, 112) AS INT) - CAST(CONVERT(CHAR(8), tbl_PAMASTER.PAI_DOB,
112) AS INT)) / 10000 AS PAI_AGE, tbl_PAMASTER.PACODE_IDX,
tbl_PACODES.PAI_COVER_DESC, tbl_PACODES.PAI_CAPITAL, tbl_PACODES.PAI_WEEKS,
tbl_PACODES.PAI_ACCIDENT, tbl_PACODES.PAI_ILLNESS, tbl_PACODES.PAI_PRM,
tbl_PAMASTER.FORM_P, tbl_PAMASTER.PAMSTR_NOTES, tbl_DEBTORS.ADD_1,
tbl_DEBTORS.ADD_2, tbl_DEBTORS.TOWN, tbl_DEBTORS.STATE, tbl_DEBTORS.PCODE
FROM tbl_PAMASTER INNER JOIN tbl_PACODES ON tbl_PAMASTER.PACODE_IDX =
tbl_PACODES.PACODE_IDX INNER JOIN
tbl_DEBTORS ON tbl_PAMASTER.DEBTOR_IDX = tbl_DEBTORS.DEBTOR_IDX
ORDER BY tbl_PAMASTER.PAPOLICY_IDX

Thanking you in advance.
 
M

Marshall Barton

winsa said:
I have a query using three tables (see below). Simply viewing the query is
not an issue, however, when I use it as the recordsource for a form, Access
only pulls up the first 50 records, then the rest after a pause. This is
creating havoc with some code that uses Recordset bookmarks. In a custom
"Record x of y" display, it only displays "Record *whatever* of 50" until I
click on the navigation buttons, and then it displays the correct total
record number. In my custom search function, it cannot find any of the
records above 51.

I have read the article from MS regarding query optimisation, but no luck
there. Does it have something to do with the multi-table query in that a
many-to-many relationship is formed because tbl_DEBTORS and tbl_PACODE are
both lookup tables?!?!

Can anyone offer any suggestions or explanations as to why Access is doing
this?

The query SQL is as follows:

SELECT TOP 100 PERCENT tbl_PAMASTER.PAPOLICY_IDX, tbl_PAMASTER.DEBTOR_IDX,
tbl_DEBTORS.DBTR_NAME, tbl_PAMASTER.PAI_CONGREGATION,
tbl_PAMASTER.PAI_INSUREDFIRST, tbl_PAMASTER.PAI_INSUREDINITIAL,
tbl_PAMASTER.PAI_INSUREDLAST, tbl_PAMASTER.PAI_DOB, (CAST(CONVERT(CHAR(8),
CURRENT_TIMESTAMP, 112) AS INT) - CAST(CONVERT(CHAR(8), tbl_PAMASTER.PAI_DOB,
112) AS INT)) / 10000 AS PAI_AGE, tbl_PAMASTER.PACODE_IDX,
tbl_PACODES.PAI_COVER_DESC, tbl_PACODES.PAI_CAPITAL, tbl_PACODES.PAI_WEEKS,
tbl_PACODES.PAI_ACCIDENT, tbl_PACODES.PAI_ILLNESS, tbl_PACODES.PAI_PRM,
tbl_PAMASTER.FORM_P, tbl_PAMASTER.PAMSTR_NOTES, tbl_DEBTORS.ADD_1,
tbl_DEBTORS.ADD_2, tbl_DEBTORS.TOWN, tbl_DEBTORS.STATE, tbl_DEBTORS.PCODE
FROM tbl_PAMASTER INNER JOIN tbl_PACODES ON tbl_PAMASTER.PACODE_IDX =
tbl_PACODES.PACODE_IDX INNER JOIN
tbl_DEBTORS ON tbl_PAMASTER.DEBTOR_IDX = tbl_DEBTORS.DEBTOR_IDX
ORDER BY tbl_PAMASTER.PAPOLICY_IDX
Click to expand...


That happens because loading a form's data (beyond an
initial number of records) is a background task so the form
can be used by users without waiting fo the entire dataset
to load.

You can force all records to be loaded by using:
Me.RecordsetClone.MoveLast
in the form's Load event or at any time before you refer to
the RecordCount property. (RecordCount only returns the
number of records that have been accessed and MoveLast
accesses the last record).
 
W

winsa

OK, thanks for your help.

--
Regards
Winsa



Marshall Barton said:
Well, no, I don't think an explanation exists. Access is
doing the job while trying to make it appear as quickly as
it can. The other records are not really needed until a
user navigates beyond what's already loaded and by then it
has hopefully had enough time to load a bunch(?) more.

If you are referring to the records programmatically, Help
is pretty clear that you need to use MoveLast to get an
accurate RecordCount. Don't be deluded by what you see in
some test case, because some other circumstance may
initially only load a single record.
Click to expand...
 
W

winsa

Sorry Marshall.

I put Me.RecordsetClone.MoveLast in the Form Load event, and it works, but
only the first time I open the form. If I close the form, and reopen it, it
doesn't work. I tried putting the code in Form Open, but that didn't seem to
do anything. I've also got it in Form Current (where the RecordCount code
is), could that be causing a conflict?
 
M

Marshall Barton

winsa said:
I put Me.RecordsetClone.MoveLast in the Form Load event, and it works, but
only the first time I open the form. If I close the form, and reopen it, it
doesn't work. I tried putting the code in Form Open, but that didn't seem to
do anything. I've also got it in Form Current (where the RecordCount code
is), could that be causing a conflict?
Click to expand...


The load event should be adequate, but putting it right
before you refer to the RecordCount property should also be
fine.

I don't understand why it isn't working, especially if you
use the navigation buttons to move through the form's
records. Do you have any more clues about what is
happening?
 
W

winsa

Hi Marshall

I've inserted some of the code that is behind my form, can you see anything
that would cause a conflict?? Thanks for your help.

Private Sub cmd_CancelChanges_Click()

Dim rs As Recordset
Dim strMsg As String

Set rs = Me.RecordsetClone
strMsg = "Do you really want to cancel?" & vbNewLine & "Any changes you made
will be lost."

If Me.Dirty Then
If MsgBox(strMsg, vbYesNo + vbExclamation, "Cancel Changes?") = 6 Then
Me.Undo
Call Controls_Off
Me!cmd_SaveChanges.Visible = False
Me!cmd_CursorStop.SetFocus
Me!cmd_CancelChanges.Visible = False
Call Buttons_On
If Me.NewRecord = True Then
rs.MoveFirst
rs.Bookmark = Me.Bookmark
End If
Else
Me!cbo_DBTR_IDX.SetFocus
Exit Sub
End If
Else
Call Controls_Off
Me!cmd_CursorStop.SetFocus
Me!cmd_SaveChanges.Visible = False
Me!cmd_CancelChanges.Visible = False
Call Buttons_On
If Me.NewRecord = True Then
rs.MoveFirst
rs.Bookmark = Me.Bookmark
End If
End If

End Sub



Private Sub FORM_CURRENT()

cmd_CursorStop.SetFocus

Dim rs As Recordset

Set rs = Me.RecordsetClone

On Error Resume Next

rs.MoveLast
rs.MoveFirst
rs.Bookmark = Me.Bookmark

If Err Then
Me!lblRecNo = "New Record"
Else
Me!lblRecNo = "Certificate " & rs.AbsolutePosition & " of " &
rs.RecordCount
End If

'Set rs = Nothing

End Sub



Private Sub cmd_Search_Click()
' Finds the record that matches the control.

Dim rs As Recordset
Dim strSearch As String

Set rs = Me.RecordsetClone

If Not IsNull(txtSearch) Then strSearch = Me.txtSearch

If IsNull(txtSearch) Then
MsgBox "Please enter a Certificate to search for!", vbOKOnly +
vbExclamation, "Invalid Search Criteria!"
Me.txtSearch.SetFocus
Else
rs.Find "[PAPOLICY_IDX] = '" & strSearch & "'"
If rs.EOF Then
MsgBox "Cert " & strSearch & " Not Found - Please Try Again.", ,
"Invalid Search Criteria!"
Me.txtSearch.SetFocus
Me.txtSearch = ""
Else
MsgBox "Cert " & strSearch & " Found", , "Congratulations!"
Me!cmd_CursorStop.SetFocus
Me!txtSearch = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End If
End If

End Sub



Private Sub Form_Load()

Dim rs As Recordset

Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
rs.Bookmark = Me.Bookmark
Set rs = Nothing

End Sub
 
M

Marshall Barton

Your use of the Find method implies that you are using ADO,
which I hadn't realized before. Since I am not familiar
with the details of that library, everything I have said in
this thread is suspect.

You should start a new thread and rephrase your question
with the Version of Access and the library identified right
up front.
--
Marsh
MVP [MS Access]

I've inserted some of the code that is behind my form, can you see anything
that would cause a conflict?? Thanks for your help.

Private Sub cmd_CancelChanges_Click()

Dim rs As Recordset
Dim strMsg As String

Set rs = Me.RecordsetClone
strMsg = "Do you really want to cancel?" & vbNewLine & "Any changes you made
will be lost."

If Me.Dirty Then
If MsgBox(strMsg, vbYesNo + vbExclamation, "Cancel Changes?") = 6 Then
Me.Undo
Call Controls_Off
Me!cmd_SaveChanges.Visible = False
Me!cmd_CursorStop.SetFocus
Me!cmd_CancelChanges.Visible = False
Call Buttons_On
If Me.NewRecord = True Then
rs.MoveFirst
rs.Bookmark = Me.Bookmark
End If
Else
Me!cbo_DBTR_IDX.SetFocus
Exit Sub
End If
Else
Call Controls_Off
Me!cmd_CursorStop.SetFocus
Me!cmd_SaveChanges.Visible = False
Me!cmd_CancelChanges.Visible = False
Call Buttons_On
If Me.NewRecord = True Then
rs.MoveFirst
rs.Bookmark = Me.Bookmark
End If
End If

End Sub


Private Sub FORM_CURRENT()

cmd_CursorStop.SetFocus

Dim rs As Recordset

Set rs = Me.RecordsetClone

On Error Resume Next

rs.MoveLast
rs.MoveFirst
rs.Bookmark = Me.Bookmark

If Err Then
Me!lblRecNo = "New Record"
Else
Me!lblRecNo = "Certificate " & rs.AbsolutePosition & " of " &
rs.RecordCount
End If

'Set rs = Nothing

End Sub



Private Sub cmd_Search_Click()
' Finds the record that matches the control.

Dim rs As Recordset
Dim strSearch As String

Set rs = Me.RecordsetClone

If Not IsNull(txtSearch) Then strSearch = Me.txtSearch

If IsNull(txtSearch) Then
MsgBox "Please enter a Certificate to search for!", vbOKOnly +
vbExclamation, "Invalid Search Criteria!"
Me.txtSearch.SetFocus
Else
rs.Find "[PAPOLICY_IDX] = '" & strSearch & "'"
If rs.EOF Then
MsgBox "Cert " & strSearch & " Not Found - Please Try Again.", ,
"Invalid Search Criteria!"
Me.txtSearch.SetFocus
Me.txtSearch = ""
Else
MsgBox "Cert " & strSearch & " Found", , "Congratulations!"
Me!cmd_CursorStop.SetFocus
Me!txtSearch = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End If
End If

End Sub



Private Sub Form_Load()

Dim rs As Recordset

Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
rs.Bookmark = Me.Bookmark
Set rs = Nothing

End Sub


Marshall Barton said:
The load event should be adequate, but putting it right
before you refer to the RecordCount property should also be
fine.

I don't understand why it isn't working, especially if you
use the navigation buttons to move through the form's
records. Do you have any more clues about what is
happening?
Click to expand...
Click to expand...
 
W

winsa

Hi Marshall

Yes, sorry, I did forget to say that I was using ADO.

I can't seem to get the DAO connection to work, even though I've got the
library connected. I keep getting errors, but that's a whole new kettle of
fish!

I'll try reposting, or else I'll try something else.

Thanks for all your help anyway!

--
Regards
Winsa



Marshall Barton said:
Your use of the Find method implies that you are using ADO,
which I hadn't realized before. Since I am not familiar
with the details of that library, everything I have said in
this thread is suspect.

You should start a new thread and rephrase your question
with the Version of Access and the library identified right
up front.
--
Marsh
MVP [MS Access]

I've inserted some of the code that is behind my form, can you see anything
that would cause a conflict?? Thanks for your help.

Private Sub cmd_CancelChanges_Click()

Dim rs As Recordset
Dim strMsg As String

Set rs = Me.RecordsetClone
strMsg = "Do you really want to cancel?" & vbNewLine & "Any changes you made
will be lost."

If Me.Dirty Then
If MsgBox(strMsg, vbYesNo + vbExclamation, "Cancel Changes?") = 6 Then
Me.Undo
Call Controls_Off
Me!cmd_SaveChanges.Visible = False
Me!cmd_CursorStop.SetFocus
Me!cmd_CancelChanges.Visible = False
Call Buttons_On
If Me.NewRecord = True Then
rs.MoveFirst
rs.Bookmark = Me.Bookmark
End If
Else
Me!cbo_DBTR_IDX.SetFocus
Exit Sub
End If
Else
Call Controls_Off
Me!cmd_CursorStop.SetFocus
Me!cmd_SaveChanges.Visible = False
Me!cmd_CancelChanges.Visible = False
Call Buttons_On
If Me.NewRecord = True Then
rs.MoveFirst
rs.Bookmark = Me.Bookmark
End If
End If

End Sub


Private Sub FORM_CURRENT()

cmd_CursorStop.SetFocus

Dim rs As Recordset

Set rs = Me.RecordsetClone

On Error Resume Next

rs.MoveLast
rs.MoveFirst
rs.Bookmark = Me.Bookmark

If Err Then
Me!lblRecNo = "New Record"
Else
Me!lblRecNo = "Certificate " & rs.AbsolutePosition & " of " &
rs.RecordCount
End If

'Set rs = Nothing

End Sub



Private Sub cmd_Search_Click()
' Finds the record that matches the control.

Dim rs As Recordset
Dim strSearch As String

Set rs = Me.RecordsetClone

If Not IsNull(txtSearch) Then strSearch = Me.txtSearch

If IsNull(txtSearch) Then
MsgBox "Please enter a Certificate to search for!", vbOKOnly +
vbExclamation, "Invalid Search Criteria!"
Me.txtSearch.SetFocus
Else
rs.Find "[PAPOLICY_IDX] = '" & strSearch & "'"
If rs.EOF Then
MsgBox "Cert " & strSearch & " Not Found - Please Try Again.", ,
"Invalid Search Criteria!"
Me.txtSearch.SetFocus
Me.txtSearch = ""
Else
MsgBox "Cert " & strSearch & " Found", , "Congratulations!"
Me!cmd_CursorStop.SetFocus
Me!txtSearch = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End If
End If

End Sub



Private Sub Form_Load()

Dim rs As Recordset

Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
rs.Bookmark = Me.Bookmark
Set rs = Nothing

End Sub


winsa wrote:
I put Me.RecordsetClone.MoveLast in the Form Load event, and it works, but
only the first time I open the form. If I close the form, and reopen it, it
doesn't work. I tried putting the code in Form Open, but that didn't seem to
do anything. I've also got it in Form Current (where the RecordCount code
is), could that be causing a conflict?
Click to expand...
Marshall Barton said:
The load event should be adequate, but putting it right
before you refer to the RecordCount property should also be
fine.

I don't understand why it isn't working, especially if you
use the navigation buttons to move through the form's
records. Do you have any more clues about what is
happening?
Click to expand...
Click to expand...
Click to expand...
 
Top