Hi,
I am wondering if there is a limit to how many records a query on a linked table will search?
I have a form that when a user clicks on a button it will look up the information based on the account number and populate portions of the form with the information from the linked table. The below code works. I've looked up several accounts and it populates, however some it will not find, when i know they exist. I can manually find them in the linked table and query them directly in the table.
Its almost as it it's not searching the entire table (which is rather large) (2 500 000 records) - it seems to search the first half of the table. When I query records at the beginning of the table it finds them and works as expected, but if i pick a record further down the table it doesn't find it.
Is there a limit? If there is anyway around it? Or is there something else I should be doing?
Any suggestions, help is appreciated
Code:
Private Sub bttnLoadData_Click()
Dim rs As DAO.Recordset
Dim sSQL As String
If Me.txtNumber <> "" Then
sSQL = "SELECT * FROM linkedTable WHERE STR(linkedTable.Number) = '" & Me.txtNumber & "'"
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
MsgBox "Unable to locate the account ", vbExclamation + vbOKOnly
Else
Me.txtCloseDate = rs.Fields("DATE_CLOS").Value
Me.txtLN1 = rs.Fields("LN1").Value
rs.Close
Set rs = Nothing
End If
Else
MsgBox "Please enter account number", vbExclamation + vbOKOnly
End If
End Sub