Large Recordset, Large Search Criteria

[StevenAFC]

Hi All,

Ive got a pretty difficult task in terms of size. I have a table which has 629326 records in it. And I have to extract * where tlGLCode = 841 criteria.

Obviously the idea method would be to SQL statement it, but SQL cant handle that many conditions.

So I thought, I'd knock up a quick form, with 2 text boxes, one to show the results delimited, and another I could copy delimited condition codes into. So I could do it maybe 20 at a time.

However I was just getting round to building it when ...

Private Sub Command6_Click()
mainText = ""
Dim rs As Recordset
Dim i As Integer
Set rs = CurrentDb.OpenRecordset("SELECT [Consol Final].*, [Consol Final].tlJobCode FROM [Consol Final] WHERE ((([Consol Final].tlJobCode)='VD1A00002' Or ([Consol Final].tlJobCode)='UKVPN13846'))")
rs.MoveFirst
For i = 0 To rs.RecordCount
    
    mainText = rs("Entity") & ", " & rs("Consol Workings 1_idxAcCode") & ", " & rs("idxOurRef") & ", " & rs("tlDescr")
    rs.MoveNext
Next i
    
End Sub

This code only produces :

"TPBERM, , SIN001681 "

As an output, so it is missing tlDescr, and doesnt bring up the rest of the results?

I need help! Or a suggestion of how else I should do this!

 

[namliam]

First things first, disambiguate!
Dim rs As Recordset => Dim rs As DAO.Recordset

FOr ... Next
Why not use
Do while not RS.eof
...
rs.movenext
Loop
Faster and easier.

About the missing field, try dong NZ(yourField) to make sure it isnt NULL

 

[StevenAFC]

Hi thanks for the reply.

The field is not null, and what im finding if I click my button which runs the code (which I have now modified as you have suggested) theres a slight pause, the first two items come up as ive shown above, then if I click to another window and then click back I get :

TPBERM, , SIN002022 <a funny square character missing char>, MPLS VPN LL

So it pops up in the end, but no other records do, and it should really be retrieving about 29 records.

 

[namliam]

You may have a linefeed/return thing in there...

Try using replace to replace them by "" to prevent it.

maintext in this seems to be a variable, but you are talking about seeing it on a form? How does it end up on the form? This is not in the code you posted here.

 

[StevenAFC]

mainText is the name of the text box im using

 

[namliam]

If you are refering to anything on a form always make sure that is clear.
Me.maintext in this case.

You are deleting the textbox first, then looping the recordset overwritting for each record...

You will only see the last record that is returned from your SQL.

 

[StevenAFC]

Geez thanks for pointing that out!

Well ive fixed that, and when I am just retreiving one column I get, what I would expect, but when I call more than one column I just get the lonely first two parts of the record like before; and it appears to stop the search. Its so weird. Ive mixed up what I want it to recall as well.

Me.mainText = Me.mainText & vbNewLine & rs("Entity") & ", " & rs("idxOurRef") & ", " & rs("tlDescr")

I wonder if saving them in strings before hand would help? Dont see why it would!

 

[namliam]

It could just be a matter of it "disappearing" of the visible line???
Alternatively you could try adding it to a listbox instead of a textbox??

Why are you trying to do this???