Problem with accessing all records

[mkirros]

I have an Access 2002 project linked into a SQL Server 2000 back end, with about 300,000 records in the main table. I have a "lookup" form in which users enter the ID number (the primary key in the main table). The procedure behind the form then accesses the table, finds the record (if it exists), and opens a new form with the selected record as current. The problem is that it only works with about the first 2,000 records - if the record is deeper in the table than that, the first record is returned. I've tried resetting the MaxRecords property everywhere it can be reset, with no change. I'm new to working with Access projects linked to SQL Server back ends, so I may be missing something obvious. Here's the code that gets the record -

Dim tb1 As ADODB.Recordset
Dim cn As ADODB.Connection
Dim ConnectStr As String
Set cn = CurrentProject.Connection

Set tb1 = New ADODB.Recordset
tb1.MaxRecords = 0
tb1.Open "Select ID,LastName1,FirstName1,Phone1 from " _ tblOrganizerInfo", cn
tb1.Find "ID = '" & txtID & "'"

 

[danvlas]

tb1.MoveLast
tb1.MoveFirst
tb1.Find

might work. MoveLast would wait for all the records to be fetched...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cmmrfrds]

Put this in your Select statement as a Where clause.

"ID = '" & txtID & "'"

The "FIND" is extremely inefficient and not necessary in this case.

 

[mkirros]

I tried both of the suggestions. No luck.

 

[cmmrfrds]

Can you show the SQL statement you sent?

 

[mkirros]

tb1.Open "Select ID,LastName1,FirstName1,Phone1 from tblOrganizerInfo where ID = '" & txtID & "'", cn

 

[cmmrfrds]

What do you mean by " No luck" that leaves a lot of guessing as what may be happening.

instead of putting the statement in the Open, put it in a variable.

Dim mySQL as String
mySQL = "Select ID,LastName1,FirstName1,Phone1 from tblOrganizerInfo "ID = '" & txtID & "'"

Then do
Debug.Print mySQL
Then do
Control G to look at the String and make sure it is built as expected.

tb1.Open mySQL, cn, 3, 3

If not (tb1.BOF and tb1.EOF) then
Debug.Print "first field = " tb1(0)
else
Debug.Print "no recordset"
End If

 

[ronspree]

Take the txtID out of the single quotes. Your where clause would otherwise be evaluated as "ID = 'txtID'". What you want is "ID = <value of txtID>"

tb1.Open "Select ID,LastName1,FirstName1,Phone1 from tblOrganizerInfo where ID = " & txtID, cn