Update unbound box on form when opening form

[Lynne]

Hi All

I have a form which contains a date field [schedule date], an unbound text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well. I also
have a refresh macro on update of the [date made] field - this also works
well.

What I am trying to achieve is that when the form is opened first, the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on opening the
form, but it just doesn't work. Can someone please help me?

Thanks in anticipation

 

[Allen Browne]

Call the macro in the Current event of the form, so it is updated each time
a record is loaded into the form.

 

[Lynne]

Have tried that, but not sure if I'm doing it right. I've put...

Private Sub Form_Current()
DoCmd.RunMacro (Refresh)

End Sub

I get an error message then saying that the refresh is not open.

Call the macro in the Current event of the form, so it is updated each time
a record is loaded into the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form which contains a date field [schedule date], an unbound text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well. I also
have a refresh macro on update of the [date made] field - this also works
well.

What I am trying to achieve is that when the form is opened first, the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on opening
the
form, but it just doesn't work. Can someone please help me?

Thanks in anticipation

 

[Allen Browne]

Try:
Private Sub Form_Current()
Me.Recalc
End Sub

(In most cases, the code will not be necessary. Access will refresh the
calculated controls once other things are done.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Have tried that, but not sure if I'm doing it right. I've put...

Private Sub Form_Current()
DoCmd.RunMacro (Refresh)

End Sub

I get an error message then saying that the refresh is not open.

Call the macro in the Current event of the form, so it is updated each
time
a record is loaded into the form.

I have a form which contains a date field [schedule date], an unbound
text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well. I
also
have a refresh macro on update of the [date made] field - this also
works
well.

What I am trying to achieve is that when the form is opened first, the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on
opening
the
form, but it just doesn't work. Can someone please help me?

 

[Lynne]

Sorry Allen, that didn't work either.

Really don't know what I've done wrong here.

Try:
Private Sub Form_Current()
Me.Recalc
End Sub

(In most cases, the code will not be necessary. Access will refresh the
calculated controls once other things are done.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Have tried that, but not sure if I'm doing it right. I've put...

Private Sub Form_Current()
DoCmd.RunMacro (Refresh)

End Sub

I get an error message then saying that the refresh is not open.

Call the macro in the Current event of the form, so it is updated each
time
a record is loaded into the form.


I have a form which contains a date field [schedule date], an unbound
text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well. I
also
have a refresh macro on update of the [date made] field - this also
works
well.

What I am trying to achieve is that when the form is opened first, the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on
opening
the
form, but it just doesn't work. Can someone please help me?

 

[Allen Browne]

We need to clarify what is going on here.

a) What is lblschstatus?
A text box?
I understand its Control Source property contains:
=IIf(Now()<[Schedule Date]," ","OUT OF DATE")
(so it is bound to an expression, not unbound)

b) What is [Schedule Date]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

c) What is [date made]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

d) What is the connection between [Schedule Date] and [date made]?

e) You have a macro named Refresh.
What does this macro contain?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry Allen, that didn't work either.

Really don't know what I've done wrong here.

Try:
Private Sub Form_Current()
Me.Recalc
End Sub

(In most cases, the code will not be necessary. Access will refresh the
calculated controls once other things are done.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Have tried that, but not sure if I'm doing it right. I've put...

Private Sub Form_Current()
DoCmd.RunMacro (Refresh)

End Sub

I get an error message then saying that the refresh is not open.

:

Call the macro in the Current event of the form, so it is updated each
time
a record is loaded into the form.


I have a form which contains a date field [schedule date], an
unbound
text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well. I
also
have a refresh macro on update of the [date made] field - this also
works
well.

What I am trying to achieve is that when the form is opened first,
the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on
opening
the
form, but it just doesn't work. Can someone please help me?

 

[Lynne]

To clarify

lblschstatus is a text box - apologies if I confused you.

[Schedule Date] is a field in the form's RecordSource.

[Date made] is also a field in the form's Record Source.

The connection between schedule date and date made....

user enters in schedule date - lblschstatus checks to see if schedule date
is less than today's date, if it is not, message of out of date is returned.

user enters in date made - refresh macro runs and if there is an entry in
date made, clears the box lblschstatus as no longer out of date.

We need to clarify what is going on here.

a) What is lblschstatus?
A text box?
I understand its Control Source property contains:
=IIf(Now()<[Schedule Date]," ","OUT OF DATE")
(so it is bound to an expression, not unbound)

b) What is [Schedule Date]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

c) What is [date made]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

d) What is the connection between [Schedule Date] and [date made]?

e) You have a macro named Refresh.
What does this macro contain?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry Allen, that didn't work either.

Really don't know what I've done wrong here.

Try:
Private Sub Form_Current()
Me.Recalc
End Sub

(In most cases, the code will not be necessary. Access will refresh the
calculated controls once other things are done.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Have tried that, but not sure if I'm doing it right. I've put...

Private Sub Form_Current()
DoCmd.RunMacro (Refresh)

End Sub

I get an error message then saying that the refresh is not open.

:

Call the macro in the Current event of the form, so it is updated each
time
a record is loaded into the form.


I have a form which contains a date field [schedule date], an
unbound
text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well. I
also
have a refresh macro on update of the [date made] field - this also
works
well.

What I am trying to achieve is that when the form is opened first,
the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on
opening
the
form, but it just doesn't work. Can someone please help me?

 

[Allen Browne]

Okay, lblschstatus is a text box bound to an expression that depends only on
[Schedule Date] and Now().

[Schedule Date] is a field in the form's RecordSource, and possibly a
control as well (or the control bound to [Schedule Date] might have a
different name.)

[Date made] is supposed to clear the message in lblschstatus? But you cannot
assign a value to a control that is bound to an expression, so I'm not sure
what the Refresh macro is actually doing.

Would this kind of thing work without the macro:
=IIf(([Date made] Is Null) AND ([Schedule Date] < Date()), "OUT OF
DATE", Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

To clarify

lblschstatus is a text box - apologies if I confused you.

[Schedule Date] is a field in the form's RecordSource.

[Date made] is also a field in the form's Record Source.

The connection between schedule date and date made....

user enters in schedule date - lblschstatus checks to see if schedule date
is less than today's date, if it is not, message of out of date is
returned.

user enters in date made - refresh macro runs and if there is an entry in
date made, clears the box lblschstatus as no longer out of date.

We need to clarify what is going on here.

a) What is lblschstatus?
A text box?
I understand its Control Source property contains:
=IIf(Now()<[Schedule Date]," ","OUT OF DATE")
(so it is bound to an expression, not unbound)

b) What is [Schedule Date]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

c) What is [date made]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

d) What is the connection between [Schedule Date] and [date made]?

e) You have a macro named Refresh.
What does this macro contain?

Sorry Allen, that didn't work either.

Really don't know what I've done wrong here.

:

Try:
Private Sub Form_Current()
Me.Recalc
End Sub

(In most cases, the code will not be necessary. Access will refresh
the
calculated controls once other things are done.)

Have tried that, but not sure if I'm doing it right. I've put...

Private Sub Form_Current()
DoCmd.RunMacro (Refresh)

End Sub

I get an error message then saying that the refresh is not open.

:

Call the macro in the Current event of the form, so it is updated
each
time
a record is loaded into the form.


I have a form which contains a date field [schedule date], an
unbound
text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well.
I
also
have a refresh macro on update of the [date made] field - this
also
works
well.

What I am trying to achieve is that when the form is opened
first,
the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on
opening
the
form, but it just doesn't work. Can someone please help me?

 

[Lynne]

Hi Allen

Have just tried what you suggest. Yes it shows OUT OF DATE in
[lblschstatus] text box when it should, but if I enter a date into the date
made field, it does not clear the [lblschstatus] "OUT OF DATE" without the
Macro.

Okay, lblschstatus is a text box bound to an expression that depends only on
[Schedule Date] and Now().

[Schedule Date] is a field in the form's RecordSource, and possibly a
control as well (or the control bound to [Schedule Date] might have a
different name.)

[Date made] is supposed to clear the message in lblschstatus? But you cannot
assign a value to a control that is bound to an expression, so I'm not sure
what the Refresh macro is actually doing.

Would this kind of thing work without the macro:
=IIf(([Date made] Is Null) AND ([Schedule Date] < Date()), "OUT OF
DATE", Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

To clarify

lblschstatus is a text box - apologies if I confused you.

[Schedule Date] is a field in the form's RecordSource.

[Date made] is also a field in the form's Record Source.

The connection between schedule date and date made....

user enters in schedule date - lblschstatus checks to see if schedule date
is less than today's date, if it is not, message of out of date is
returned.

user enters in date made - refresh macro runs and if there is an entry in
date made, clears the box lblschstatus as no longer out of date.

We need to clarify what is going on here.

a) What is lblschstatus?
A text box?
I understand its Control Source property contains:
=IIf(Now()<[Schedule Date]," ","OUT OF DATE")
(so it is bound to an expression, not unbound)

b) What is [Schedule Date]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

c) What is [date made]?
Is this a text box bound to an expression?
Or a field in the form's RecordSource?
An unbound control?
Other?

d) What is the connection between [Schedule Date] and [date made]?

e) You have a macro named Refresh.
What does this macro contain?

Sorry Allen, that didn't work either.

Really don't know what I've done wrong here.

:

Try:
Private Sub Form_Current()
Me.Recalc
End Sub

(In most cases, the code will not be necessary. Access will refresh
the
calculated controls once other things are done.)

Have tried that, but not sure if I'm doing it right. I've put...

Private Sub Form_Current()
DoCmd.RunMacro (Refresh)

End Sub

I get an error message then saying that the refresh is not open.

:

Call the macro in the Current event of the form, so it is updated
each
time
a record is loaded into the form.


I have a form which contains a date field [schedule date], an
unbound
text
field [lblschstatus] and also a date made field [date made]

My unbound field [lblschstatus] contains the following:

=IIf(Now()<[Schedule Date]," ","OUT OF DATE"), which works well.
I
also
have a refresh macro on update of the [date made] field - this
also
works
well.

What I am trying to achieve is that when the form is opened
first,
the
unbound field [lblschstatus] is automatically updated.

I've tried to run the refresh macro on load of the form, also on
opening
the
form, but it just doesn't work. Can someone please help me?