USING A PRE QUERY ANYWHERE ON THE FORM...

[franksirvent]

Hi
I am trying to load a number of records via a Query (as per below) on the FORM_ONLOAD() so it is loaded in memory and can be used at anytime while the form is open (so it doesn't have to constantly load the data over and over again so it is faster...)

The form calls another SUB which goes through each record on the 'sqlline2' (query) below and then quickly finds the data it needs to do a number of calculations.

This is the sub it runs to go through the records...
------------------------------------------------------
private sub gothroughtherecords()

rst2.MoveFirst
Do Until rst2.EOF
If firstpart = rst![Direction] then [txt20] = rst2![20]
rst2.MoveNext
Loop
end sub
------------------------------------------------------

But it doesn't work.
Obviously I am doing something wrong...
Anyone can help me?

thanks

THIS IS HOW I LOAD THE DATA ON THE FORM_ONLOAD
------------------------------------------------------
Public Sub loadbafdata()

sqlline2 = "SELECT g.*, g.ChargeDescription "
sqlline2 = sqlline2 & " FROM [BAF Rates1] as g "
sqlline2 = sqlline2 & " WHERE ((([ChargeDescription])='BAF'));"

Set dbs = CurrentDb()
Set rst2 = dbs.OpenRecordset(sqlline2)
rst2.MoveLast
nor = rst2.RecordCount

End Sub
------------------------------------------------------

 

[BNPMike]

What error messages to you get. Have you run it in debug at all?

Apart from all that, you seem to be getting a set of records and then picking one by going through the set with VBA. Databases are quite good at this themselves. Quite possibly you don't need any VBA at all. Have you had a problem, or were you just anticipating one?

 

[franksirvent]

thanks for replying

the error is :

424 'Needs an object' (I think that's the translation from Spanish to English)

This happens when the code jumps to

"private sub gothroughtherecords()

rst2.MoveFirst <<<<< THIS IS WHERE THE ERROR HAPPENS"

Is like the query (which is run when the form loads) is not longer in memory, but I don't want to run it over and over again for every calculation.

I want to keep the information in memory to avoid constantly querying the database.

There are over 25 people using this form constantly so I want the query to keep it in memory when they open the form (usually there is only around 50-100 records selected by the query) so it runs faster (in each user's PC instead of querying the back end/main server database over and over and over again for the same information.

The query will always return the same records, so I want to avoid unnecesary additional network process...

thanks

 

[BNPMike]

It looks like a scope thing, doesn't it? It looks like your procedure can't see rst2.

I still wonder whether you need to do this at all. If you opened a form with the correct bound query, Access will let you move backwards and forwards through the recordset using various built in features. It's a good idea not to do your own version of what software does. It does it a lot better than you and leaves you time to go down the pub.

 

[franksirvent]

ok

I agree it is a scope thing...do you know how to fix it ?
About the bound query, note that the form doesn't show an records.
It is a calculation form.
Users fill in a number of fields, and the form goes and queries for information based on the values on the fields.

I cannot put the query on the form itself (bound) because it is not related to any field info and it is not supposed to do that...

I basically want a LOOP which mantains the info in memory all the time, like DIMMED ARRAYS (eg field1(10,10), etc etc)

 

[BNPMike]

OK I'm not an expert on this. Hopefully someone else will join in. It looks like DAO which I've never used.

First thing I notice - don't know if it is a problem - is you don't appear to have declared rs2. Is that code you have missed out just for posting? I'd expect a DIM statement.

 

[PHV]

First, add an Option Explicit on top of the Declarations section of your module.
Then, as you use rst2 in more than one procedure, declare it in ths Declarations section.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244