problem with delays on form

[Kamil]

Hi.
I created a form with subdatasheet:
FORM - employee data
SUBDATASHEET - employee's history of employment (multiple records from
another table)
On the subdatasheet (I mean the object in forms section, not the
embedded subdatasheet) I created few fields in the footer section,
which are summarizing the employment period and calculating if an
employee is employed or not.
I wanted to have these values on the main form, so I placed the
unbound text fields and used formulas.
It works nice, but shows me correct values after half a second.

It wouldn't be a problem, but I want to indicate the status with
color: EMPLOYED in green and UNEMPLOYED in red.

Here's the code I used:
Private Sub Form_Current()
if Me!status="EMPLOYED" then
Me!status.forecolor=4227072
else
Me!status.forecolor=255
end if
end sub

the coloring does not work. I suppose that's because of the delay.
what can I do to make it work?
Best regards,
Kamil

 

[BruceM]

Try referencing the subform value or expression rather than the control on
the main form into which you have copied the value:

If Me.SubformControlName.Form.Status = "EMPLOYED" Then
Me.txtStatus.ForeColor = 4227072
Else
Me.txtStatus.ForeColor = 255
End If

I have made a few guesses here. If you cannot decipher it, post the code or
describe the expressions used to place the values into the subform footer.
Also, what are the formulas you used to place the values into the main form?
If you are having problems with code or expressions it is always a good
choice to include the code or expression (an abridged version, perhaps, if
it contains elements that do not apply to the question at hand) when you
post the question.

 

[Kamil]

Try referencing the subform value or expression rather than the control on
the main form into which you have copied the value:

If Me.SubformControlName.Form.Status = "EMPLOYED" Then
    Me.txtStatus.ForeColor = 4227072
    Else
        Me.txtStatus.ForeColor = 255
End If

I have made a few guesses here.  If you cannot decipher it, post the code or
describe the expressions used to place the values into the subform footer..
Also, what are the formulas you used to place the values into the main form?
If you are having problems with code or expressions it is always a good
choice to include the code or expression (an abridged version, perhaps, if
it contains elements that do not apply to the question at hand) when you
post the question.









- Poka¿ cytowany tekst -

I tried:
if Me.SubformControlName.Form!Status = "EMPLOYED" then [...]
but it does not help.
More code:
the SubformControlName field I'm referring to (Status):
=iif(Last([EndDate]<Date();"UNEMPLOYED";"EMPLOYED")
EndDate field indicated the end date of each record in employment
history for each worker.

Is it possible to force the Form_Current code to wait until all
calculations are done?

 

[BruceM]

You need to use the actual name of the subform control. SubformControlName
is a placeholder in my sample code.

The Last function will not do what I think you expect. It just returns the
last record in the recordset, in whatever order it is sorted. If EndDate is
a field on the form you can just look at that:

=IIf([EndDate] < Date(), etc.)

If you need to search for EndDate in a specific record you will need to
specify that record, perhaps with DMax or DLookup. How you do it depends on
the criteria for locating that record. Is it the latest date for an
employee whose name and ID are on the current record? Is there more than
one record containing that field for a particular employee? If so, you may
need to do something like this (assuming EmployeeID is numeric):

Dim datEnd as Date

datEnd = DMax("[EndDate]","[TableName]", _
"[EmployeeID] = " & Me.EmployeeID)

Me.txtEmp = IIf(datEnd < Date(),"Employed","Unemployed")

txtEmp is an unbound text box on the form.

You could do something like this for the color coding:

If Me.SubformControlName.Form.EndDate < Date Then
Me.txtStatus.ForeColor = 422072
Else
Me.txtStatus.Forecolor = 255
End If

There is no need to force the Current event code to wait, but you could add
this line of code:

DoEvents

If there is a need to force the code to wait until all processing is done.

Try referencing the subform value or expression rather than the control on
the main form into which you have copied the value:

If Me.SubformControlName.Form.Status = "EMPLOYED" Then
Me.txtStatus.ForeColor = 4227072
Else
Me.txtStatus.ForeColor = 255
End If

I have made a few guesses here. If you cannot decipher it, post the code
or
describe the expressions used to place the values into the subform footer.
Also, what are the formulas you used to place the values into the main
form?
If you are having problems with code or expressions it is always a good
choice to include the code or expression (an abridged version, perhaps, if
it contains elements that do not apply to the question at hand) when you
post the question.









- Poka¿ cytowany tekst -

I tried:
if Me.SubformControlName.Form!Status = "EMPLOYED" then [...]
but it does not help.
More code:
the SubformControlName field I'm referring to (Status):
=iif(Last([EndDate]<Date();"UNEMPLOYED";"EMPLOYED")
EndDate field indicated the end date of each record in employment
history for each worker.

Is it possible to force the Form_Current code to wait until all
calculations are done?

 

[Kamil]

You need to use the actual name of the subform control.  SubformControlName
is a placeholder in my sample code.

The Last function will not do what I think you expect.  It just returns the
last record in the recordset, in whatever order it is sorted.  If EndDate is
a field on the form you can just look at that:

=IIf([EndDate] < Date(), etc.)

If you need to search for EndDate in a specific record you will need to
specify that record, perhaps with DMax or DLookup.  How you do it depends on
the criteria for locating that record.  Is it the latest date for an
employee whose name and ID are on the current record?  Is there morethan
one record containing that field for a particular employee?  If so, you may
need to do something like this (assuming EmployeeID is numeric):

Dim datEnd as Date

datEnd = DMax("[EndDate]","[TableName]", _
                  "[EmployeeID] = " & Me.EmployeeID)

Me.txtEmp = IIf(datEnd < Date(),"Employed","Unemployed")

txtEmp is an unbound text box on the form.

You could do something like this for the color coding:

If Me.SubformControlName.Form.EndDate < Date Then
    Me.txtStatus.ForeColor = 422072
    Else
        Me.txtStatus.Forecolor = 255
End If

There is no need to force the Current event code to wait, but you could add
this line of code:

DoEvents

If there is a need to force the code to wait until all processing is done..

Try referencing the subform value or expression rather than the controlon
the main form into which you have copied the value:

If Me.SubformControlName.Form.Status = "EMPLOYED" Then
Me.txtStatus.ForeColor = 4227072
Else
Me.txtStatus.ForeColor = 255
End If

I have made a few guesses here. If you cannot decipher it, post the code
or
describe the expressions used to place the values into the subform footer.
Also, what are the formulas you used to place the values into the main
form?
If you are having problems with code or expressions it is always a good
choice to include the code or expression (an abridged version, perhaps,if
it contains elements that do not apply to the question at hand) when you
post the question.

- Poka¿ cytowany tekst -

I tried:
if Me.SubformControlName.Form!Status = "EMPLOYED" then [...]
but it does not help.
More code:
the SubformControlName field I'm referring to (Status):
=iif(Last([EndDate]<Date();"UNEMPLOYED";"EMPLOYED")
EndDate field indicated the end date of each record in employment
history for each worker.

Is it possible to force the Form_Current code to wait until all
calculations are done?- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Hi.
Thanks for the answer.
I'm also using placeholders here instead of my real names (these are
in Polish).
In my case Last is working good - sorting is correct, and I need last
record, not last date).
An employee can have multiple EndDates - that's why I have subform -
to show whole history. From this history I need to pick last record
(newest) and check it's EndDate.
I can't use DMax in the way you did, because EndDate (employee end of
contract) can be specified, but normally it's NULL.

The code I used gives me proper data, but the delay is unacceptable
(or the sequence of events: the code is first coloring, and then
calculating values. I want to color text, which is changed be these
calculations).

 

[BruceM]

You do not say where you are using the expression:
=iif(Last([EndDate]<Date();"UNEMPLOYED";"EMPLOYED")

If it is the Control Source of an unbound text box, try putting this into
another unbound text box to be sure the Last function is returning what you
expect:
=Last([EndDate])

You say you can't use DMax because EndDate is ordinarily null. That is
incorrect. You can allow for null, for instance by using the Nz function.

With a better understanding of your database's structure I may be able to
suggest an alternative to the Last function, which I can only repeat is
unlikely to do what you think it will. Even if it works in a test, that may
be more about luck than design. If you are determined to use the Last
function the only thing I can suggest is that you be sure EndDate is indexed
in the table.

You need to use the actual name of the subform control. SubformControlName
is a placeholder in my sample code.

The Last function will not do what I think you expect. It just returns the
last record in the recordset, in whatever order it is sorted. If EndDate
is
a field on the form you can just look at that:

=IIf([EndDate] < Date(), etc.)

If you need to search for EndDate in a specific record you will need to
specify that record, perhaps with DMax or DLookup. How you do it depends
on
the criteria for locating that record. Is it the latest date for an
employee whose name and ID are on the current record? Is there more than
one record containing that field for a particular employee? If so, you may
need to do something like this (assuming EmployeeID is numeric):

Dim datEnd as Date

datEnd = DMax("[EndDate]","[TableName]", _
"[EmployeeID] = " & Me.EmployeeID)

Me.txtEmp = IIf(datEnd < Date(),"Employed","Unemployed")

txtEmp is an unbound text box on the form.

You could do something like this for the color coding:

If Me.SubformControlName.Form.EndDate < Date Then
Me.txtStatus.ForeColor = 422072
Else
Me.txtStatus.Forecolor = 255
End If

There is no need to force the Current event code to wait, but you could
add
this line of code:

DoEvents

If there is a need to force the code to wait until all processing is done.

Try referencing the subform value or expression rather than the control
on
the main form into which you have copied the value:

If Me.SubformControlName.Form.Status = "EMPLOYED" Then
Me.txtStatus.ForeColor = 4227072
Else
Me.txtStatus.ForeColor = 255
End If

I have made a few guesses here. If you cannot decipher it, post the code
or
describe the expressions used to place the values into the subform
footer.
Also, what are the formulas you used to place the values into the main
form?
If you are having problems with code or expressions it is always a good
choice to include the code or expression (an abridged version, perhaps,
if
it contains elements that do not apply to the question at hand) when you
post the question.

- Poka¿ cytowany tekst -

I tried:
if Me.SubformControlName.Form!Status = "EMPLOYED" then [...]
but it does not help.
More code:
the SubformControlName field I'm referring to (Status):
=iif(Last([EndDate]<Date();"UNEMPLOYED";"EMPLOYED")
EndDate field indicated the end date of each record in employment
history for each worker.

Is it possible to force the Form_Current code to wait until all
calculations are done?- Ukryj cytowany tekst -

- Pokaz cytowany tekst -

Hi.
Thanks for the answer.
I'm also using placeholders here instead of my real names (these are
in Polish).
In my case Last is working good - sorting is correct, and I need last
record, not last date).
An employee can have multiple EndDates - that's why I have subform -
to show whole history. From this history I need to pick last record
(newest) and check it's EndDate.
I can't use DMax in the way you did, because EndDate (employee end of
contract) can be specified, but normally it's NULL.

The code I used gives me proper data, but the delay is unacceptable
(or the sequence of events: the code is first coloring, and then
calculating values. I want to color text, which is changed be these
calculations).