VBA Code not being made first priority

[firebug88]

So let me start off by stating that I cannot post any of the code.

So I have a large databse (65K rows) with names, genders, and locations (Not the real data but you get the point). I have a Form that has 3 list which i use to apply a filter to a query. This query uses 3 dummy tables to only include the DISTINCT names, genders, and locations seaperatly.

I have the list populated using the properties sheet and the query being populated using VBA code. However, my issue is that access (bottom right) says its running query as soon as i open the Form. What is bothering me is that my VBA code has a msgbox to pop-up as soon as the sub is accessed but it doesnt come up until i wait a few minutes for whatever is running before hand. VBA code is set to Form_Open FYI.

My question, has anyone experience something similar or is there a good way to debug exactly what is happening and when?

 

[Micron]

as soon as the sub is accessed
Don't understand much of your terminology in your post. You could mean this is code on a form or subform. Or maybe a procedure (Sub something () ). It could be working exactly as designed, but there's not enough info to be sure, such as what accesses it? Which code runs first depends on if there are subforms on forms, and where you place the code (e.g. Open event or Load event) and if the form(s) or it's controls are bound to tables or queries. Put a break on the first line of code that executes the form opening. If there is no such thing, then on the form open event, and step through the code to see the order of execution.

 

[firebug88]

Sorry for not being very clear as this is the first time i have been using VBA code. The code im talking about is placed in the Open Event procedure. The three list on the form are bound to a query, and the query is bound to three temp tables. When i open the form, ideally the code is supposed to re-populate the temp tables by deleting the current content and adding distinct values from the main table. Then the form should open with the three list in the form being popultated from the three temp tables. This part of the code is working as planed; however, the query which combines the three temp tables (to generate a report with the filtered settings) is being run first for some reason. Then, after my code runs, the query re-runs again. I know this because i made a form which pops up at the start of my code:

Private Sub Form_Open ()
DoCmd.OpenForm "Temp_Form"

The temp form will not pop-up until after that query is run. I have tried putting a break at the very beggining of the sub but it still runs the query first. Even though i know it shoundt be.
It is very frustrating. Hopefully i helped to clear this up a little.

 

[Micron]

The code im talking about is placed in the Open Event

OK, but you don't say what the code does. Some code belongs in the open event, sometimes the Load event. You really should check out https://support.office.com/en-ca/ar...-objects-e76fbbfe-6180-4a52-8787-ce86553682f9 to see if you have it in the right place. It will really help you to understand the sequence of code processing for forms, especially if they have bound controls or sub forms.

the code is supposed to re-populate the temp tables

If this is in the open event, it's in the wrong place, hence my recommendation to read up on events. The open event is for controlling how a form and it's objects are rendered, including setting properties of the form and controls such as visible or what the objects record sources are. The Load event is where you run queries from. Controls bound to queries get their info automatically, but during the load event.
Keep in mind that code excecution passes from Access to Jet or vice versa. Jet handles queries, and when they are run/opened, processing is suspended temporarily in Access. Sometimes, a pause is needed, say when refreshing, repainting or requerying a form, before you allow code to transition from Access to Jet. I doubt that is your problem though. I really think it's where you're putting the code for your events.

 

[firebug88]

Thanks for the link. I have moved the sub for Form Load for now but honestly it does not make a difference in speed. Maybe there is a better way of doing this than the following:

Open Form
Run 3 append queries to populate 3 tmp tables (VBA)
Use a Join query on those 3 tmp tables
populate the 3 list boxes on the form using the previous query
finish opening the form
use form to filter out rows in the query (VBA)
use final filtered query to run report in a readable layout (VBA)

The append queries take no time at all, however the join query takes forever. Which is what lead me to my previous question.

 

[Micron]

I thought your issue was the order of events, not speed of process.

However, my issue is that access (bottom right) says its running query as soon as i open the Form. What is bothering me is that my <acronym title="visual basic for applications">VBA</acronym> code has a msgbox to pop-up as soon as the sub is accessed but it doesnt come up until i wait a few minutes for whatever is running before hand.

and

The temp form will not pop-up until after that query is run. I have tried putting a break at the very beggining of the sub but it still runs the query first. Even though i know it shoundt be.

Maybe there is a better way of doing this than the following:

Open Form
Run 3 append queries to populate 3 tmp tables (<acronym title="visual basic for applications">VBA</acronym>)
Use a Join query on those 3 tmp tables
populate the 3 list boxes on the form using the previous query
finish opening the form
use form to filter out rows in the query (<acronym title="visual basic for applications">VBA</acronym>)
use final filtered query to run report in a readable layout (<acronym title="visual basic for applications">VBA</acronym>)

Doesn't look quite right to me, but that's based on certain assumptions. I don't see why you need the temp tables unless some of the source tables are local and some are not (ODBC with a million records,for example) or why a query would populate 3 controls with the same data. Maybe your form should open with the controls populated with the variables you need (perhaps a query using the DISTINCT or DISTINCT ROW predicate) and after the user makes selections, use a query that gets the data from the same source as your append but uses the form controls as criteria. You don't actually open this query, you allow the report to call it. Maybe that doesn't make sense, but I don't know what else to suggest. If your current query is slow, you should try to determine why (65,000 records is not that many, believe it or not) so the slowness could be
- not enough joins (thus limiting the rows to be processed),
- the type of joins used (inner vs outer)
- lack of indexes on table fields
- running it more than once as you seem to be.
I don't expect network traffic is an issue since your append works satisfactorily. "Takes forever" is a subjective measure and doesn't help me much. Perhaps for you that is 1 minute. To put this in perspective, I routinely select data from local tables joined on ODBC tables containing 25 - 30 columns and a million records in each linked table, expecting no more than 2 minutes, and this includes doing calcualtions such as Min or Max on a date field. However, they do not always start out that way. If I go beyond 5 minutes, I start looking for performance improvements such as the ones already mentioned. You could also try the database optimizer to evaluate your query, but it might not help. In order to optimize the query based on your temp tables, you may need additional fields that you don't have now using the append.<acronym title="visual basic for applications"></acronym>

 

[firebug88]

Micron, Thanks for helping me with this issue. So part 1 of my problem was solved by indexing my fields in the tables that i am using to run the innor join query (hopefully i said that right). The join query is now taking no more than 5-8 sec to complete (Down from 10-15 min). However, it still takes ~30seconds for my form to fully load, which leads me to my next question.

In my form, I have three list boxes that have the following properties:

RowSource: SELECT DISTINCT Query1.field1 FROM Query1 'list 1
RowSource: SELECT DISTINCT Query1.field2 FROM Query1 'list 2
RowSource: SELECT DISTINCT Query1.field3 FROM Query1 'list 3

Is this running query1 a total of three times? If so is there a faster way to populate the list boxes? I am using the selected values from listbox 1 to "update" list 2 and 3 via Query1 if that makes since.

 

[Micron]

Pretty sure the answer is yes, and because no criteria is supplied, it runs against the entire source each time. Your form should open faster if you run one query using all 3 fields, create a recordset from that and set each listbox rowsource to one of those fields. The code below should do that.

If you already have these variables set in your module, don't re-declare them, and ensure your project has a reference set to DAO Object Library. You should test the sql first - it may not be exactly what you need (not sure if you understand when to use DISTINCT versus DISTINCT ROW or even if you need either one). If you are not happy with the speed of the query, try to tweak it for faster processing such as trying it without DISTICT clauses. The code should go in your form load event. If the sql returns no records, the IF block will be skipped and execution should jump to whatever you have next. Change my references in red to whatever you need.

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = Currentdb
Set rs = db.OpenRecordset ("SELECT DISTINCT [COLOR=#ff0000]field1[/COLOR], [COLOR=#ff0000]field2[/COLOR],[COLOR=#ff0000] field3[/COLOR] from [COLOR=#ff0000]Query1[/COLOR]")

If rs.RecordCount <> 0 Then 
   rs.Movefirst
   Do While Not rs.EOF
      [COLOR=#ff0000]listbox1[/COLOR].Items.Add(rs.Fields(0).Value)
      [COLOR=#ff0000]listbox2[/COLOR].Items.Add(rs.Fields(1).value)
      [COLOR=#ff0000]listbox3[/COLOR].Items.Add(rs.Fields(2).value)
      rs.MoveNext()
   Loop
End IF

 

[firebug88]

I tried to implement that code but for some reason it got cought up at listbox1.Items.Add (coundnt find the cmd "Items"). Also, i figured out that changing the Subdatasheetname to None speed things up considerably. It now only takes 5 sec for my form to load completly. I would still like to add the code in the previous post though if i could cause i have the listboxes being set using the properties tab and not in a sub.

FYI i am using Access 2007 with the 2002-2003 file format (compatability)

 

[Micron]

You DID change the red parts to the names of your controls & fields and ensure you have the set the required references? I don't know what "coundnt find the cmd "Items" means either. Post the error message you are getting IF you have taken care of the first two items of concern.
Not sure I understand this either
"cause i have the listboxes being set using the properties tab and not in a sub"
If you're setting properties (I take it you mean listbox record list) in property sheet, why would you want any code to do this?