Need to run DoCmd.OpenQuery in background 3

[randysmid]

Hi,
I have a form that includes a nested subform. Based on a date entered in the main form, I need to create a table using DoCmd.OpenQuery. The problem is that I don't want the user to see the resulting table, which is happening now.
1) Does anyone know of a technique to get around this situation?
2) Should the code used to create the table be placed in the main form, or the subform? Which event is best to keep the user from seeing the result set?

TIA, Randy Smith

HTH,
Randy Smith, MCP
California Teachers Association

 

[judgehopkins]

Randy, there was a discussion of this very question at

http://www.experts-exchange.com/Developer/Databases/MS_Access/Q_20375640.html#7341289

and also at

http://www.utteraccess.com/forums/access/access4907.html

and also at

faq181-120 (i.e., in Tek-Tips)

Let me know if any of these help, okay?

"A great many people now reading and writing would be better employed keeping rabbits." --Dame Edith Sitwell

 

[randysmid]

Hi Judge,
Thank you for the response. I have read the other postings, and the key item appears to be the statement: DoCmd.SetWarnings False

However, I already have these in my code, and the results of the query still display on the screen. The ironic part is that this was not happening before.

So, my ultimate goal is to attempt to put this query into the background.

HTH,
Randy Smith, MCP
California Teachers Association

 

[BillPower]

Hi randysmid,

Can you post the code for the whole sub or function that runs these queries. And the SQL for the Queries.

Thanks

Bill

 

[randysmid]

Hi,
Here is my code inside the Enter event for the subform:

DoCmd.SetWarnings False

DoCmd.OpenQuery "qryBatch_Date_Allowable_Personnel1", , acReadOnly
DoCmd.OpenQuery "qryBatch_Date_Allowable_Personnel2", , acReadOnly

DoCmd.SetWarnings True

HTH,
Randy Smith, MCP
California Teachers Association

 

[CosmoKramer]

Randy,

Why don't you run an append query instead, deleting the records first through a delete query (if necessary). I do this with temp tables a lot and it works quite well....

 

[BillPower]

Hi, can you post the SQL for the queries too. Trying to reproduce your problem, have never encountered it before.

Bill

 

[randysmid]

Hi,
Here is the first query being run:
SELECT [Last_name]+", "+[First_name] AS Full_name, Pers_assign.SSN, Pers_assign.Bti_flag, WEEKEND.Weekend_date, Pers_assign.Start_date, Pers_assign.End_date
FROM WEEKEND, Personnel INNER JOIN Pers_assign ON Personnel.SSN = Pers_assign.SSN
WHERE (((Pers_assign.Bti_flag)="Y&quot AND ((WEEKEND.Weekend_date)=[Forms]![BatchTimeInput]![Batch_date]))
ORDER BY [Last_name]+", "+[First_name];

Then, the second query is used to make a work table called Batch_Allowable_Personnel:
SELECT DISTINCT qryBatch_Date_Allowable_Personnel1.SSN, qryBatch_Date_Allowable_Personnel1.Full_name, qryBatch_Date_Allowable_Personnel1.Start_date, qryBatch_Date_Allowable_Personnel1.End_date, qryBatch_Date_Allowable_Personnel1.Weekend_date INTO Batch_Allowable_Personnel
FROM qryBatch_Date_Allowable_Personnel1
WHERE (((qryBatch_Date_Allowable_Personnel1.Start_date)<=[Weekend_date]) AND ((qryBatch_Date_Allowable_Personnel1.End_date)>=[Weekend_date]))
ORDER BY qryBatch_Date_Allowable_Personnel1.SSN;

The queries work perfectly, but the problem is that the results of the query are now being displayed on the users machine, rather than being performed in the background.

HTH,
Randy Smith, MCP
California Teachers Association

 

[randysmid]

Hello everyone,
Here is an update: I changed the list box recordsource on the subform to use the 2nd query (after changing it to a standard select type), and it still displays the results of the query. In the past, this all happened in the background.

HTH,
Randy Smith, MCP
California Teachers Association

 

[BillPower]

Hi again,

Delete this line of code, it's a select query and rightly being displayed on screen:

DoCmd.OpenQuery &quot;qryBatch_Date_Allowable_Personnel1&quot;, , acReadOnly

qryBatch_Date_Allowable_Personnel1 doesn't need to be open for qryBatch_Date_Allowable_Personnel2 to read it. qryBatch_Date_Allowable_Personnel2 will treat qryBatch_Date_Allowable_Personnel1 as if it were a table. You don't open Tables to query them.

qryBatch_Date_Allowable_Personnel2 won't show on screen as it is an Action query.

Fingers crossed.

Bill

 

[randysmid]

Bill,
Which event do you think is best for this? I just tried the main forms LostFocus event, but it doesn't seem to fire when jumping to the subform.

HTH,
Randy Smith, MCP
California Teachers Association

 

[randysmid]

Hello everyone,
I tried BillPower's advice and placed the code in the main form's Enter event for the subform control. IT NOW WORKS PERFECTLY.
Many thanks to all, and stars for all your assistance!!!!

HTH,
Randy Smith, MCP
California Teachers Association

 

[BillPower]

I would say in the AfterUpdate event of Batch_date in the Form called BatchTimeInput.

I'm assuming that the table Batch_Allowable_Personnel is the recordsource of your Sub Form. This is why I wanted to see your code, but I'll try and make a guess at what you should do. In the AfterUpdate event of Batch_date :

On Error Resume Next
Docmd.Echo False
Me!YourSubformName.Form.RecordSource = &quot;&quot;
DoCmd.SetWarnings False
DoCmd.OpenQuery &quot;qryBatch_Date_Allowable_Personnel2
DoCmd.SetWarnings True
Me!YourSubformName.Form.RecordSource = &quot;Batch_Allowable_Personnel&quot;
Docmd.Echo True


I've used Echo so that the user should see very little of the process taking place. RecordSource = &quot;&quot; , the Make Table query would fail otherwise.

Obviously add to the code above anything that you need that I can't see.

Bill