Query Date Copy and Paste...

[LeizladNeb]

I have developed 3 expressions within queries to allow me to predict dates which jobs can be done by. the system works on a 5 jobs per day basis. It takes the current date, adds on one day, and then adds on another day for every 5 pending jobs. It looks like this:

PendingJobs: [tblJob]![JobStatusID]
This returns records with JobStatusID = 1

DaysToAdd: Round(((Sum([QryPendingJobs]![Pendingjobs])/5)+0.49999),0)
This sums the retuned recods and divides it by 5, finding how many days should be added. it rounds up to the nearest integer.

DateToAddTo: Date()+(1+[DaysToAdd])
This takes the current date, adds one and the value obtained above.

My problem lies in that i want this value to automatically present itself as the value of a field within a table, on a form.
The 'DueDate' field within the 'tblJob' table needs to be given this value effectivly as if it's just been copied and pasted into the form that serves as the data entry point

i think i might have to take the expression and put it into vba, or use vba to take the value accross. im having trouble refering to tables and queries in vba as im somewhat of a newbie.
If anyones understood that poorly explained problem, please let me know what i could do with doin.

thanks in advance

ben dalziel

 

[beetee]

Add the following to the form's BeforeUpdate event procedure:
(NOTE: to do this, view the form's properties [open the form in design view, and select Viewroperties from the main menu]. Click the events tab. Select 'EventProcedure' in the 'BeforeUpdate' edit box. Click the elipses (three dots) build button).

private sub Form_BeforeUpdate
' open the query with the PendingJobs counts
Dim MyRst As Recordset
Set MyRst = CurrentDb.OpenRecordset("QryPendingJobs&quot
' find the days to add
dim DaysToAdd as long
DaysToAdd = Round(((MyRst![Pendingjobs])/5)+0.49999),0)
'store the result
Me.ExpectedCompletionDate = Date()+ DaysToAdd + 1
MyRst.Close
Set MyRst = Nothing
end sub


The BIG assumption here is, that QryPendingJobs is summing the jobs for whatever JobStatusID is currently displayed on your form. One way to handle this is to create a new query just like the existing query, and, in the JobStatusID criteria area, enter something like:

Forms![MyForm]![JobStatusID}

This query will only work properly when your data entry form (MyForm) is open.

HTH

 

[LeizladNeb]

Hi again.

I have inputted the above suggestion into a form as stated.
It will not allow me to have 'Date()' though, and it doesn't display a result in the form.
I have replaced QryPendingJobs with 'QryDueDate', and within this, the 'PendingJobs' expression currently returns the result '2'.
I'm a bit concerned that nothings being displayed, and i can't see why!
THis is what i've got at the moment. Any ideas?

Private Sub JobDue_BeforeUpdate()
'open the query with the Pending jobs Count
Dim MyRst As Recordset
Set MyRst = CurrentDb.OpenRecordset("QryDueDate&quot
'find the days to add
Dim DaysToAdd As Long
DaysToAdd = Round((((MyRst![Pendingjobs]) / 5) + 0.49999), 0)
'store the result
Me.ExpectedCompletionDate = Date + DaysToAdd + 1
MyRst.Close
Set MyRst = Nothing
End Sub

Ben Dalziel

 

[beetee]

A couple of thoughts:

I'm not sure why you can't use the term 'Date()'. I can on my Access 97, and it should be valid. Are you sure that the problem is with Date()?

One way to check: open the 'immediate' window by pressing ^G. This window let's you view expressions.

Inside the immediate window, type
print Date()

you *should* see today's date.

Another possible reason:
the data is not being updated until the form's before update event. Therefore, you may not see the changes until you have moved to a different record.

Another place you can put this code is in the AfterUpdate event in whatever controls affect the date calculation.
Regarding the information not showing up on the form, perhaps you need to do a repaint (me.repaint) on the form after setting the field, although I didn't need to in the test case I built to model your situation.

Is 'ExpectedCompletionDate' the correct name for the control? Is the control hidden?