Query not selecting all data 1

[hoose]

Hi all,

I have the following code contained in a command button's onclick event

***********************************
Dim FinishTime As Date

FinishTime = Now

ENDTIME = FinishTime
INTTIME = DateDiff("n", STTIME, ENDTIME)

DoCmd.OpenQuery ("Complete&quot

DoCmd.TransferSpreadsheet acExport, acSpreadsheetType97, "tblcomplete", NAME, True
************************************

The Complete query selects a bunch of data, which is then exported as an excel file (including the variable INTTIME). However, the value of INTTIME is never exported. Is this because the value of INTTIME isn't updated before the query is run?

Can anyone suggust a way to fix this problem?

Thanks in andvance.

Dan

 

[jrbarnett]

Dan,

Your code just sets a variable called EndTime to the value of the date/time at time of execution (the now function),
retrieves the difference between start and end time, storing the result in the intTime variable, opens the "Complete" query then exports your spreadsheet.

The date/time calculation results are not used by anything else and could be removed entirely without affecting the output of this code.
If you want to store the start and end time in the exported results, you have to ensure that they are in the table "tblComplete" before the export is run.
In order to do this, instead of opening the query, you would need to execute an SQL INSERT or UPDATE query to put the data into the table prior to the export.


As it is, your code could be simplified to:

DoCmd.OpenQuery ("Complete&quot

DoCmd.TransferSpreadsheet acExport, acSpreadsheetType97, "tblcomplete", NAME, True

with no difference to the output from your point of view.

John

 

[hoose]

thanks for the reply.

i just copied and pasted my code over, i understand some of it does not apply to my question.

I'll use an update query.

A more correct question would be:

Why doesn't the value of INTTIME be updated when it is assigned a new variable? (Earlier on I assigned STTIME to a value in a similar fashion and it is included in the table)

Thanks, dan

 

[hoose]

Sorry, made a little mistake in the last post.

Why doesn't the value of INTTIME be updated when it is assigned a new value? When does the update occur if it is not stated through an update query? (Earlier on I assigned STTIME to a value in a similar fashion and it is included in the table)

thanks

dan

 

[jrbarnett]

Dan,

I presume given your comments that there is an intTime value in the query "complete".
The variable within your VBA code gets updated when the following line is executed.

INTTIME = DateDiff("n", STTIME, ENDTIME)

This line of code is not in any way connected to any intTime value within the complete query.
If you want to update this value, you need to update the data in the underlying table. Something like:

DoCmd.RunSQL "Update tablename Set fintTime = " & intTime & " Where primarykey = value"

Replace the following items:
tablename with the name of the table within the query (not the query itself.
fintTime is the name of the field within the table
primarykey is the fieldname of the primary key within the table "tablename"
value is the value of the primarykey field that you want to update.
Everything from the word "Where" onwards could be omitted, but this would result in every intTime value in the table being updated, and the chances are this is not what you want.

Please get back to me if you need anything clarified.

John

 

[hoose]

hi john,

This question is pretty basic, or more likely won't make any sense at all.

Say i have a table (tblstart) with an autonumber primary key and variable 'temp'

I have a form linked to use tblstart

If I have a command button on the form, and in the command button's onclick event i have the code:

temp = 1

If i open the form up and start clicking on the command button, then open up the table, i'll see a bunch of records with temp = 1...

So the code is putting a value into the table.

When does the code actually update the table? Is it when a new record is started? Or when the form is closed?

I'm a little confused.

Thanks, Dan

 

[jrbarnett]

Dan,

The code will update the value in the form as it executes. The form will update the table when the record is saved (the equivalent of pressing Shift Enter, clicking the pencil icon to the left of the form, or selecting the Save Record command from the Records menu). The record will also save automatically when the focus changes to another record (ie you click in a different record), or the form is closed or the view changes.

Referring to your earlier posts, I now know where I think you are coming from (ie a little about your situation and database setup) and think that the following will sort your problem:

Dim FinishTime As Date

FinishTime = Now

ENDTIME = FinishTime
INTTIME = DateDiff("n", STTIME, ENDTIME)

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenQuery ("Complete")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetType97, "tblcomplete", NAME

Please get back to me with regards to its success.

John

 

[hoose]

thanks for the explanation

everything works the way i want it too.

thanks a lot for your help

dan