Help with pending requests

[Aria]

I need to make changes to our recently completed db (thanks to all who helped
me get this far). What seems perfectly fine with limited data becomes a
different story once numerous records are entered. It’s hard to identify what
should be readily evident.

I have a form for requests. I need to see which requests are still pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color = red

It returns what I *asked* for but this is not quite what I want. I would
like it to say “pending†*until* I input a received date. What is happening
is that I can’t input anything (I hear a beep). Maybe the IIF statement isn’t
really what I need (it either puts a received date or “pendingâ€). I took out
the statement and left the conditional formatting but again, this is not
quite what I would like to see. This is probably easily resolved but I don’t
know how to get to where I want to go and could use some help. Thanks.

 

[Jeff Boyce]

Aria

It looks like your "Pending" field is trying to display either a date or a
text message -- do you have a data-type or format type applied to that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form bound to the
underlying data? If so, again, what is the datatype of the underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Aria]

Hi Jeff,
I have limited forays outside of the New User section so I’m sorry for the
incomplete post.

"can't input anything" ... ?where

When I say I can’t input anything, I meant I can’t input a date into the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form bound to the
underlying data? If so, again, what is the datatype of the underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason, tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it’s short date. I
hope I have answered your questions fully. Please let me know if you need to
know something I failed to provide. Thanks for your assistance.

--
Aria W.

Aria

It looks like your "Pending" field is trying to display either a date or a
text message -- do you have a data-type or format type applied to that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form bound to the
underlying data? If so, again, what is the datatype of the underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to all who
helped
me get this far). What seems perfectly fine with limited data becomes a
different story once numerous records are entered. It's hard to identify
what
should be readily evident.

I have a form for requests. I need to see which requests are still
pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color = red

It returns what I *asked* for but this is not quite what I want. I would
like it to say "pending" *until* I input a received date. What is
happening
is that I can't input anything (I hear a beep). Maybe the IIF statement
isn't
really what I need (it either puts a received date or "pending"). I took
out
the statement and left the conditional formatting but again, this is not
quite what I would like to see. This is probably easily resolved but I don't
know how to get to where I want to go and could use some help. Thanks.

 

[Jeff Boyce]

Aria

Since we're not there, we don't necessarily know where your [RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
I have limited forays outside of the New User section so I'm sorry for the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date into the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form bound to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason, tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short date. I
hope I have answered your questions fully. Please let me know if you need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.

Aria

It looks like your "Pending" field is trying to display either a date or
a
text message -- do you have a data-type or format type applied to that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form bound to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to all who
helped
me get this far). What seems perfectly fine with limited data becomes a
different story once numerous records are entered. It's hard to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are still
pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color = red

It returns what I *asked* for but this is not quite what I want. I
would
like it to say "pending" *until* I input a received date. What is
happening
is that I can't input anything (I hear a beep). Maybe the IIF statement
isn't
really what I need (it either puts a received date or "pending"). I
took
out
the statement and left the conditional formatting but again, this is
not
quite what I would like to see. This is probably easily resolved but I
don't
know how to get to where I want to go and could use some help. Thanks.

 

[Aria]

The record source is qryKeyRequests with the previously posted SQL. The
textbox [RcvdDate] is located on frmKeyRequests.

The short date format is in the form in txtRcvdDate.

--
Aria W.

Aria

Since we're not there, we don't necessarily know where your [RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
I have limited forays outside of the New User section so I'm sorry for the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date into the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form bound to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason, tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short date. I
hope I have answered your questions fully. Please let me know if you need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.

Aria

It looks like your "Pending" field is trying to display either a date or
a
text message -- do you have a data-type or format type applied to that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form bound to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to all who
helped
me get this far). What seems perfectly fine with limited data becomes a
different story once numerous records are entered. It's hard to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are still
pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color = red

It returns what I *asked* for but this is not quite what I want. I
would
like it to say "pending" *until* I input a received date. What is
happening
is that I can't input anything (I hear a beep). Maybe the IIF statement
isn't
really what I need (it either puts a received date or "pending"). I
took
out
the statement and left the conditional formatting but again, this is
not
quite what I would like to see. This is probably easily resolved but I
don't
know how to get to where I want to go and could use some help. Thanks.

 

[Jeff Boyce]

So if you have a form with txtRcvdDate as a control, is that control "bound"
to a data field from your query, or "unbound"?

And if you tell Access that the txtRcvdDate control is formatted for short
date, how does it handle when you try to put "Pending" in there?

If the form isn't letting you enter a date in that control, are you getting
an error message?

(still) More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The record source is qryKeyRequests with the previously posted SQL. The
textbox [RcvdDate] is located on frmKeyRequests.

The short date format is in the form in txtRcvdDate.

--
Aria W.

Aria

Since we're not there, we don't necessarily know where your [RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
I have limited forays outside of the New User section so I'm sorry for
the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date into
the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form bound
to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS
Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short date.
I
hope I have answered your questions fully. Please let me know if you
need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.


:

Aria

It looks like your "Pending" field is trying to display either a date
or
a
text message -- do you have a data-type or format type applied to that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form bound
to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to all
who
helped
me get this far). What seems perfectly fine with limited data
becomes a
different story once numerous records are entered. It's hard to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are still
pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color = red

It returns what I *asked* for but this is not quite what I want. I
would
like it to say "pending" *until* I input a received date. What is
happening
is that I can't input anything (I hear a beep). Maybe the IIF
statement
isn't
really what I need (it either puts a received date or "pending"). I
took
out
the statement and left the conditional formatting but again, this is
not
quite what I would like to see. This is probably easily resolved but
I
don't
know how to get to where I want to go and could use some help.
Thanks.

 

[Aria]

Answers to your additional questions:

I believe txtRcvdDate is a bound control. The control source was [RcvdDate],
before I put in the IIF statement. I then changed the control source to
Pending, now part of the field list.

I didn’t have to add “pending†to anything. Once I modified the query and
saved it, I flipped to form view and saw that the records that were null now
said, “Pendingâ€. The other records already had a received date. When I tried
to test a previously null record by entering a date in txtRcvdDate I hear a
beep and can’t enter anything in that control.

I don’t receive any error message. I only hear a beep and can’t enter a
date.

--
Aria W.

So if you have a form with txtRcvdDate as a control, is that control "bound"
to a data field from your query, or "unbound"?

And if you tell Access that the txtRcvdDate control is formatted for short
date, how does it handle when you try to put "Pending" in there?

If the form isn't letting you enter a date in that control, are you getting
an error message?

(still) More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The record source is qryKeyRequests with the previously posted SQL. The
textbox [RcvdDate] is located on frmKeyRequests.

The short date format is in the form in txtRcvdDate.

--
Aria W.

Aria

Since we're not there, we don't necessarily know where your [RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,
I have limited forays outside of the New User section so I'm sorry for
the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date into
the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form bound
to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS
Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short date.
I
hope I have answered your questions fully. Please let me know if you
need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.


:

Aria

It looks like your "Pending" field is trying to display either a date
or
a
text message -- do you have a data-type or format type applied to that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form bound
to
the
underlying data? If so, again, what is the datatype of the underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to all
who
helped
me get this far). What seems perfectly fine with limited data
becomes a
different story once numerous records are entered. It's hard to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are still
pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color = red

It returns what I *asked* for but this is not quite what I want. I
would
like it to say "pending" *until* I input a received date. What is
happening
is that I can't input anything (I hear a beep). Maybe the IIF
statement
isn't
really what I need (it either puts a received date or "pending"). I
took
out
the statement and left the conditional formatting but again, this is
not
quite what I would like to see. This is probably easily resolved but
I
don't
know how to get to where I want to go and could use some help.
Thanks.

 

[Jeff Boyce]

Aria

If this were mine I'd return to the underlying table and confirm the
properties/settings on that field in the table itself.

The value that a bound control holds wouldn't be changing unless your query
"assigned" the new value (pending) to the field -- bound is bound. Please
also open the form in design view and check the record source for that
control.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Answers to your additional questions:

I believe txtRcvdDate is a bound control. The control source was
[RcvdDate],
before I put in the IIF statement. I then changed the control source to
Pending, now part of the field list.

I didn't have to add "pending" to anything. Once I modified the query and
saved it, I flipped to form view and saw that the records that were null
now
said, "Pending". The other records already had a received date. When I
tried
to test a previously null record by entering a date in txtRcvdDate I hear
a
beep and can't enter anything in that control.

I don't receive any error message. I only hear a beep and can't enter a
date.

--
Aria W.

So if you have a form with txtRcvdDate as a control, is that control
"bound"
to a data field from your query, or "unbound"?

And if you tell Access that the txtRcvdDate control is formatted for
short
date, how does it handle when you try to put "Pending" in there?

If the form isn't letting you enter a date in that control, are you
getting
an error message?

(still) More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The record source is qryKeyRequests with the previously posted SQL. The
textbox [RcvdDate] is located on frmKeyRequests.

The short date format is in the form in txtRcvdDate.

--
Aria W.


:

Aria

Since we're not there, we don't necessarily know where your [RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,
I have limited forays outside of the New User section so I'm sorry
for
the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date into
the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS
Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short
date.
I
hope I have answered your questions fully. Please let me know if you
need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.


:

Aria

It looks like your "Pending" field is trying to display either a
date
or
a
text message -- do you have a data-type or format type applied to
that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to all
who
helped
me get this far). What seems perfectly fine with limited data
becomes a
different story once numerous records are entered. It's hard to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are
still
pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color =
red

It returns what I *asked* for but this is not quite what I want.
I
would
like it to say "pending" *until* I input a received date. What is
happening
is that I can't input anything (I hear a beep). Maybe the IIF
statement
isn't
really what I need (it either puts a received date or "pending").
I
took
out
the statement and left the conditional formatting but again, this
is
not
quite what I would like to see. This is probably easily resolved
but
I
don't
know how to get to where I want to go and could use some help.
Thanks.

 

[Aria]

Jeff,
Per your instructions, I returned to tblKeyRequests to verify the data types
and properties for [RcvdDate]. I’ve confirmed that the data type is Date/Time
and the format is short date. I then opened the form in design view and see
that the control source for this textbox (txtRcvdDate) on form
(frmKeyRequests) is Pending (Pending:
IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]).

The value that a bound control holds wouldn't be changing unless your >query "assigned" the new value (pending) to the field -- bound is bound.

I'm a little confused and have a couple of questions. If I create an IIF
statement, is that not assigning a new value to the field? Is the IIF
statement the problem? Am I mistaken in thinking that there are only 2
outcomes with that statement; either it will put “pending†for any
[RecvdDate] field with a null value or it will put the actual received date?
As written, isn't it one or the other? Should I be able to input a date as it
is currently written?

--
Aria W.

Aria

If this were mine I'd return to the underlying table and confirm the
properties/settings on that field in the table itself.

The value that a bound control holds wouldn't be changing unless your query
"assigned" the new value (pending) to the field -- bound is bound. Please
also open the form in design view and check the record source for that
control.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Answers to your additional questions:

I believe txtRcvdDate is a bound control. The control source was
[RcvdDate],
before I put in the IIF statement. I then changed the control source to
Pending, now part of the field list.

I didn't have to add "pending" to anything. Once I modified the query and
saved it, I flipped to form view and saw that the records that were null
now
said, "Pending". The other records already had a received date. When I
tried
to test a previously null record by entering a date in txtRcvdDate I hear
a
beep and can't enter anything in that control.

I don't receive any error message. I only hear a beep and can't enter a
date.

--
Aria W.

So if you have a form with txtRcvdDate as a control, is that control
"bound"
to a data field from your query, or "unbound"?

And if you tell Access that the txtRcvdDate control is formatted for
short
date, how does it handle when you try to put "Pending" in there?

If the form isn't letting you enter a date in that control, are you
getting
an error message?

(still) More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The record source is qryKeyRequests with the previously posted SQL. The
textbox [RcvdDate] is located on frmKeyRequests.

The short date format is in the form in txtRcvdDate.

--
Aria W.


:

Aria

Since we're not there, we don't necessarily know where your [RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,
I have limited forays outside of the New User section so I'm sorry
for
the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date into
the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS
Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short
date.
I
hope I have answered your questions fully. Please let me know if you
need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.


:

Aria

It looks like your "Pending" field is trying to display either a
date
or
a
text message -- do you have a data-type or format type applied to
that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to all
who
helped
me get this far). What seems perfectly fine with limited data
becomes a
different story once numerous records are entered. It's hard to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are
still
pending.
I thought conditional formatting would take care of the problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color =
red

It returns what I *asked* for but this is not quite what I want.
I
would
like it to say "pending" *until* I input a received date. What is
happening
is that I can't input anything (I hear a beep). Maybe the IIF
statement
isn't
really what I need (it either puts a received date or "pending").
I
took
out
the statement and left the conditional formatting but again, this
is
not
quite what I would like to see. This is probably easily resolved
but
I
don't
know how to get to where I want to go and could use some help.
Thanks.

 

[Jeff Boyce]

Aria

I don't believe you are assigning a new value using that IIF() statement.
I suspect it is only display the results of that IIF().

If you need to (permanently) change values in a field, use an update query.

I strongly suspect that the reason why the control on the form doesn't allow
updating is because you've told it that it MUST be (i.e., MUST show) the
results of the IIF() statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Per your instructions, I returned to tblKeyRequests to verify the data
types
and properties for [RcvdDate]. I've confirmed that the data type is
Date/Time
and the format is short date. I then opened the form in design view and
see
that the control source for this textbox (txtRcvdDate) on form
(frmKeyRequests) is Pending (Pending:
IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]).

The value that a bound control holds wouldn't be changing unless your

I'm a little confused and have a couple of questions. If I create an IIF
statement, is that not assigning a new value to the field? Is the IIF
statement the problem? Am I mistaken in thinking that there are only 2
outcomes with that statement; either it will put "pending" for any
[RecvdDate] field with a null value or it will put the actual received
date?
As written, isn't it one or the other? Should I be able to input a date as
it
is currently written?

--
Aria W.

Aria

If this were mine I'd return to the underlying table and confirm the
properties/settings on that field in the table itself.

The value that a bound control holds wouldn't be changing unless your
query
"assigned" the new value (pending) to the field -- bound is bound.
Please
also open the form in design view and check the record source for that
control.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Answers to your additional questions:

I believe txtRcvdDate is a bound control. The control source was
[RcvdDate],
before I put in the IIF statement. I then changed the control source to
Pending, now part of the field list.

I didn't have to add "pending" to anything. Once I modified the query
and
saved it, I flipped to form view and saw that the records that were
null
now
said, "Pending". The other records already had a received date. When I
tried
to test a previously null record by entering a date in txtRcvdDate I
hear
a
beep and can't enter anything in that control.

I don't receive any error message. I only hear a beep and can't enter a
date.

--
Aria W.


:

So if you have a form with txtRcvdDate as a control, is that control
"bound"
to a data field from your query, or "unbound"?

And if you tell Access that the txtRcvdDate control is formatted for
short
date, how does it handle when you try to put "Pending" in there?

If the form isn't letting you enter a date in that control, are you
getting
an error message?

(still) More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The record source is qryKeyRequests with the previously posted SQL.
The
textbox [RcvdDate] is located on frmKeyRequests.

The short date format is in the form in txtRcvdDate.

--
Aria W.


:

Aria

Since we're not there, we don't necessarily know where your
[RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,
I have limited forays outside of the New User section so I'm
sorry
for
the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date
into
the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS
Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short
date.
I
hope I have answered your questions fully. Please let me know if
you
need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.


:

Aria

It looks like your "Pending" field is trying to display either a
date
or
a
text message -- do you have a data-type or format type applied
to
that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to
all
who
helped
me get this far). What seems perfectly fine with limited data
becomes a
different story once numerous records are entered. It's hard
to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are
still
pending.
I thought conditional formatting would take care of the
problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color
=
red

It returns what I *asked* for but this is not quite what I
want.
I
would
like it to say "pending" *until* I input a received date. What
is
happening
is that I can't input anything (I hear a beep). Maybe the IIF
statement
isn't
really what I need (it either puts a received date or
"pending").
I
took
out
the statement and left the conditional formatting but again,
this
is
not
quite what I would like to see. This is probably easily
resolved
but
I
don't
know how to get to where I want to go and could use some help.
Thanks.

 

[Aria]

Jeff,
So I would have to use an update query (which I have never done before) in
order to turn a null field blue with the words "pending" and once the request
is filled, input a date?
--
Aria W.

Aria

I don't believe you are assigning a new value using that IIF() statement.
I suspect it is only display the results of that IIF().

If you need to (permanently) change values in a field, use an update query.

I strongly suspect that the reason why the control on the form doesn't allow
updating is because you've told it that it MUST be (i.e., MUST show) the
results of the IIF() statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Per your instructions, I returned to tblKeyRequests to verify the data
types
and properties for [RcvdDate]. I've confirmed that the data type is
Date/Time
and the format is short date. I then opened the form in design view and
see
that the control source for this textbox (txtRcvdDate) on form
(frmKeyRequests) is Pending (Pending:
IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]).

The value that a bound control holds wouldn't be changing unless your
query "assigned" the new value (pending) to the field -- bound is bound.

I'm a little confused and have a couple of questions. If I create an IIF
statement, is that not assigning a new value to the field? Is the IIF
statement the problem? Am I mistaken in thinking that there are only 2
outcomes with that statement; either it will put "pending" for any
[RecvdDate] field with a null value or it will put the actual received
date?
As written, isn't it one or the other? Should I be able to input a date as
it
is currently written?

--
Aria W.

Aria

If this were mine I'd return to the underlying table and confirm the
properties/settings on that field in the table itself.

The value that a bound control holds wouldn't be changing unless your
query
"assigned" the new value (pending) to the field -- bound is bound.
Please
also open the form in design view and check the record source for that
control.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Answers to your additional questions:

I believe txtRcvdDate is a bound control. The control source was
[RcvdDate],
before I put in the IIF statement. I then changed the control source to
Pending, now part of the field list.

I didn't have to add "pending" to anything. Once I modified the query
and
saved it, I flipped to form view and saw that the records that were
null
now
said, "Pending". The other records already had a received date. When I
tried
to test a previously null record by entering a date in txtRcvdDate I
hear
a
beep and can't enter anything in that control.

I don't receive any error message. I only hear a beep and can't enter a
date.

--
Aria W.


:

So if you have a form with txtRcvdDate as a control, is that control
"bound"
to a data field from your query, or "unbound"?

And if you tell Access that the txtRcvdDate control is formatted for
short
date, how does it handle when you try to put "Pending" in there?

If the form isn't letting you enter a date in that control, are you
getting
an error message?

(still) More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The record source is qryKeyRequests with the previously posted SQL.
The
textbox [RcvdDate] is located on frmKeyRequests.

The short date format is in the form in txtRcvdDate.

--
Aria W.


:

Aria

Since we're not there, we don't necessarily know where your
[RcvdDate]
textbox is located. Is that in a form?

Is the short date format in your table or your form?

What is the record source for your form, if you're using one?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,
I have limited forays outside of the New User section so I'm
sorry
for
the
incomplete post.

"can't input anything" ... ?where

When I say I can't input anything, I meant I can't input a date
into
the
[RcvdDate] text box.

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Yes, I am using the IIF statement in a query.

Here is the SQL in case you need to see it:

SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason,
tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",[RcvdDate]) AS
Pending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

I have a format type applied to the [RcvdDate] field, it's short
date.
I
hope I have answered your questions fully. Please let me know if
you
need
to
know something I failed to provide. Thanks for your assistance.

--
Aria W.


:

Aria

It looks like your "Pending" field is trying to display either a
date
or
a
text message -- do you have a data-type or format type applied
to
that
field? Text isn't Dates.

"can't input anything" ... ?where

Are you using that IIF() statement in a query to "feed" a form
bound
to
the
underlying data? If so, again, what is the datatype of the
underlying
field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to make changes to our recently completed db (thanks to
all
who
helped
me get this far). What seems perfectly fine with limited data
becomes a
different story once numerous records are entered. It's hard
to
identify
what
should be readily evident.

I have a form for requests. I need to see which requests are
still
pending.
I thought conditional formatting would take care of the
problem.

I created the following IIF statement:

Pending: IIf(IsNull([RcvdDate]),"Pending",[RcvdDate])

Conditional Formatting:
Expression Is IsNull([RcvdDate]) Back color = blue Fore color
=
red

It returns what I *asked* for but this is not quite what I
want.
I
would
like it to say "pending" *until* I input a received date. What
is
happening
is that I can't input anything (I hear a beep). Maybe the IIF
statement
isn't
really what I need (it either puts a received date or
"pending").
I
took
out
the statement and left the conditional formatting but again,
this
is
not
quite what I would like to see. This is probably easily
resolved
but
I
don't
know how to get to where I want to go and could use some help.
Thanks.

 

[John W. Vinson]

I'm a little confused and have a couple of questions. If I create an IIF
statement, is that not assigning a new value to the field?

No. It's taking whatever is in the field and creating a NEW dynamic value, not
stored in any table.

Is the IIF statement the problem?
Yes.

Am I mistaken in thinking that there are only 2
outcomes with that statement; either it will put “pending” for any
[RecvdDate] field with a null value or it will put the actual received date?

That's correct as far as it goes - the function will return one or other of
those values for display.

As written, isn't it one or the other? Should I be able to input a date as it
is currently written?

No, because what's on the form IS NOT THE TABLE FIELD. It's the result of a
calculation - an expression - the IIF() function. You can update the table
field but you cannot update IIF(), any more than you could put

=[HoursWorked] * [HourlyRate]

as the control source of a textbox and have it updateable.

 

[Aria]

Ahhh…I see. Thank you so much for explaining. I’ve never tried an update
query before so I’m checking the Help section. I have a question though. When
do I run this? I see that there is an "update to" field row. I won’t have a
date until whenever the request is filled (who knows when that will be).
Also, this is an MDE. How would I run this? Would it be possible to use an
update command button on my form? Do I keep the IIF statement and if so how
would I get a date in the textbox if it beeps now when I try to do anything?
I'm sorry. I guess I’m full of questions.

--
Aria W.

I'm a little confused and have a couple of questions. If I create an IIF
statement, is that not assigning a new value to the field?

No. It's taking whatever is in the field and creating a NEW dynamic value, not
stored in any table.

Is the IIF statement the problem?
Yes.

Am I mistaken in thinking that there are only 2
outcomes with that statement; either it will put “pending†for any
[RecvdDate] field with a null value or it will put the actual received date?

That's correct as far as it goes - the function will return one or other of
those values for display.

As written, isn't it one or the other? Should I be able to input a date as it
is currently written?

No, because what's on the form IS NOT THE TABLE FIELD. It's the result of a
calculation - an expression - the IIF() function. You can update the table
field but you cannot update IIF(), any more than you could put

=[HoursWorked] * [HourlyRate]

as the control source of a textbox and have it updateable.

 

[John W. Vinson]

Ahhh…I see. Thank you so much for explaining. I’ve never tried an update
query before so I’m checking the Help section. I have a question though. When
do I run this? I see that there is an "update to" field row. I won’t have a
date until whenever the request is filled (who knows when that will be).
Also, this is an MDE. How would I run this? Would it be possible to use an
update command button on my form? Do I keep the IIF statement and if so how
would I get a date in the textbox if it beeps now when I try to do anything?
I'm sorry. I guess I’m full of questions.

I really really REALLY don't think you should use an update query.

For one thing you cannot update a Date/Time field to "Pending". That's a text
string, and you cannot store text strings in a date/time field - only
date/time values!

I would suggest instead having a *SECOND* textbox on the form, perhaps
superimposed over the date field textbox. Set its Control Source to

=IIF(IsNull([RcvdDate]), "Pending", Null)

Set its Enabled property to No, Locked to Yes, TabStop to No so the user can't
mess with it; if it's carefully sized and positioned just atop the RcvdDate
textbox, then the user can click or tab into the RcvdDate box and type (or
edit) a date. You may need to requery this new textbox in the RcvdDate
textbox's AfterUpdate event.

The essence is that your table should contain either a date, or nothing. The
"Pending" text need not and should not be stored ANYWHERE - it's just a
display, and can be calculated on the fly.

 

[Aria]

For one thing you cannot update a Date/Time field to "Pending". That's a
text

string, and you cannot store text strings in a date/time field - only
date/time values!
OK.

I would suggest instead having a *SECOND* textbox on the form, perhaps
superimposed over the date field textbox. Set its Control Source to

=IIF(IsNull([RcvdDate]), "Pending", Null)

Set its Enabled property to No, Locked to Yes, TabStop to No so the user can't
mess with it; if it's carefully sized and positioned just atop the RcvdDate
textbox, then the user can click or tab into the RcvdDate box and type (or
edit) a date. You may need to requery this new textbox in the RcvdDate
textbox's AfterUpdate event.

I'm having a bit of a problem with this part. I have created the 2nd textbox
and set the properties as you directed. I used the AfterUpdate event of the
[RcvdDate] textbox to requery the new textbox [RcvdDate2]. I see "Pending"
but the records that did have a date are now null.

I set the control source to:

=IIf(IsNull([RcvdDate]),"Pending",Null)

The other problem is that I can't enter a date into the textbox when it says
"pending". I now receive the following error message,"Control can't be
edited; it's bound the the expression 'IIf(IsNull([RcvdDate])," "Pending",
Null'. Can you see where I made a mistake?

--
Aria W.

Ahhh…I see. Thank you so much for explaining. I’ve never tried an update
query before so I’m checking the Help section. I have a question though. When
do I run this? I see that there is an "update to" field row. I won’t have a
date until whenever the request is filled (who knows when that will be).
Also, this is an MDE. How would I run this? Would it be possible to use an
update command button on my form? Do I keep the IIF statement and if so how
would I get a date in the textbox if it beeps now when I try to do anything?
I'm sorry. I guess I’m full of questions.

I really really REALLY don't think you should use an update query.

For one thing you cannot update a Date/Time field to "Pending". That's a text
string, and you cannot store text strings in a date/time field - only
date/time values!

I would suggest instead having a *SECOND* textbox on the form, perhaps
superimposed over the date field textbox. Set its Control Source to

=IIF(IsNull([RcvdDate]), "Pending", Null)

Set its Enabled property to No, Locked to Yes, TabStop to No so the user can't
mess with it; if it's carefully sized and positioned just atop the RcvdDate
textbox, then the user can click or tab into the RcvdDate box and type (or
edit) a date. You may need to requery this new textbox in the RcvdDate
textbox's AfterUpdate event.

The essence is that your table should contain either a date, or nothing. The
"Pending" text need not and should not be stored ANYWHERE - it's just a
display, and can be calculated on the fly.

 

[John W. Vinson]

For one thing you cannot update a Date/Time field to "Pending". That's a text
string, and you cannot store text strings in a date/time field - only
date/time values!
OK.

I would suggest instead having a *SECOND* textbox on the form, perhaps
superimposed over the date field textbox. Set its Control Source to

=IIF(IsNull([RcvdDate]), "Pending", Null)

Set its Enabled property to No, Locked to Yes, TabStop to No so the user can't
mess with it; if it's carefully sized and positioned just atop the RcvdDate
textbox, then the user can click or tab into the RcvdDate box and type (or
edit) a date. You may need to requery this new textbox in the RcvdDate
textbox's AfterUpdate event.

I'm having a bit of a problem with this part. I have created the 2nd textbox
and set the properties as you directed. I used the AfterUpdate event of the
[RcvdDate] textbox to requery the new textbox [RcvdDate2]. I see "Pending"
but the records that did have a date are now null.

I set the control source to:

=IIf(IsNull([RcvdDate]),"Pending",Null)

The other problem is that I can't enter a date into the textbox when it says
"pending". I now receive the following error message,"Control can't be
edited; it's bound the the expression 'IIf(IsNull([RcvdDate])," "Pending",
Null'. Can you see where I made a mistake?

You tried to edit the Pending textbox.

IT IS NOT EDITABLE. It makes no sense to edit it. It's *just for display*. If
you want to edit something, edit RcvdDate. The textbox properties I described
should make it impossible to set focus to the Pending textbox or to type in it
at all (so you wouldn't get this message).

Since you're apparently using a continuous form (a point I missed, sorry!),
try a different technique. Base the form on a Query of your table. Include
RcvdDate in the query, and put in a calculated field by typing

IsPending: IIF(IsNull([RcvdDate]), "Pending", Null)

in a vacant Field cell in the query grid. Bind one textbox to RcvdDate for
editing, and a second textbox (Enabled = No, Tab Stop = No, Locked = Yes) to
IsPending. You can - again - carefully move the IsPending textbox so that it
is superimposed *OVER* the RcvdDate textbox; when you click into the (two
superimposed) textboxes, or tab into it, the editable RcvdDate textbox will
become active, but you'll still see the word Pending if appropriate.

 

[Aria]

This doesn’t seem to be as easily resolved as I originally thought. I should
probably just let this go but I don’t want to give up if there is a chance I
can get it going. I don’t seem to be able to get the results I would like to
see. I don’t know if it was clear in my last posting, the steps I took.
Except for the change to the query, the steps are the same with your first
instructions as with your latest. I’m going to post each and every step I
took in the hope that you can point out any missteps.

1. Opened the form in design view and changed the query to include the
modified IIF statement, IsPending: IIF(IsNull([RcvdDate]), “Pendingâ€, Null)
which I added to a vacant field in the query grid.

2. Saved the query.

3. Confirmed that txtRcvdDate is bound, control source is [RcvdDate]
4. Changed the control source of txtRcvdDate2 to IsPending, now part of the
field list.

5. Verified that I had the property settings correct for txtRcvdDate2 per
your instructions; enabled = No, tab stop = No, Locked = Yes) I changed the
settings with your last posting but confirmed that they are as you instructed.

6. Repositioned txtRcvdDate2 *OVER* txtRcvdDate, as before, using the left,
top, and height measurements that I copied from txtRcvdDate.

Here is the SQL from the modified query:
SELECT tblKeysRequests.RequestID, tblKeysRequests.KeyID,
tblKeysRequests.EmpID, tblKeysRequests.RqstDate,
tblKeysRequests.QtyRequested, tblKeysRequests.RcvdDate,
tblKeysRequests.QtyRecd, tblKeysRequests.Reason, tblKeysRequests.Complete,
tblKeys.KeyCode, IIf(IsNull([RcvdDate]),"Pending",Null) AS IsPending
FROM tblKeys INNER JOIN tblKeysRequests ON tblKeys.KeyID =
tblKeysRequests.KeyID
ORDER BY tblKeys.KeyCode;

7. Flipped the form to form view.

8. I see the words “Pending†for those records that used to be blank. This
is good.

9. I see a blank textbox for those records that previously had a received
date. This is not good. The received dates previously entered should not
change. Those records are complete.

10. I cannot enter any information into the Rcvd textbox.

I understand when you say the Pending textbox is not editable. I thought
when I tabbed into the textbox that says, “Pendingâ€, I was actually editing
the RcvdDate textbox which should now be active. If I understand correctly,
this will allow me to input a received date as pending requests are filled. I
have followed your instructions line by line (I don’t think I missed
anything) and re-read your explanations. I understand what you’re saying. I
just don’t understand why this isn’t working.

--
Aria W.

For one thing you cannot update a Date/Time field to "Pending". That's a text
string, and you cannot store text strings in a date/time field - only
date/time values!
OK.

I would suggest instead having a *SECOND* textbox on the form, perhaps
superimposed over the date field textbox. Set its Control Source to

=IIF(IsNull([RcvdDate]), "Pending", Null)

Set its Enabled property to No, Locked to Yes, TabStop to No so the user can't
mess with it; if it's carefully sized and positioned just atop the RcvdDate
textbox, then the user can click or tab into the RcvdDate box and type (or
edit) a date. You may need to requery this new textbox in the RcvdDate
textbox's AfterUpdate event.

I'm having a bit of a problem with this part. I have created the 2nd textbox
and set the properties as you directed. I used the AfterUpdate event of the
[RcvdDate] textbox to requery the new textbox [RcvdDate2]. I see "Pending"
but the records that did have a date are now null.

I set the control source to:

=IIf(IsNull([RcvdDate]),"Pending",Null)

The other problem is that I can't enter a date into the textbox when it says
"pending". I now receive the following error message,"Control can't be
edited; it's bound the the expression 'IIf(IsNull([RcvdDate])," "Pending",
Null'. Can you see where I made a mistake?

You tried to edit the Pending textbox.

IT IS NOT EDITABLE. It makes no sense to edit it. It's *just for display*. If
you want to edit something, edit RcvdDate. The textbox properties I described
should make it impossible to set focus to the Pending textbox or to type in it
at all (so you wouldn't get this message).

Since you're apparently using a continuous form (a point I missed, sorry!),
try a different technique. Base the form on a Query of your table. Include
RcvdDate in the query, and put in a calculated field by typing

IsPending: IIF(IsNull([RcvdDate]), "Pending", Null)

in a vacant Field cell in the query grid. Bind one textbox to RcvdDate for
editing, and a second textbox (Enabled = No, Tab Stop = No, Locked = Yes) to
IsPending. You can - again - carefully move the IsPending textbox so that it
is superimposed *OVER* the RcvdDate textbox; when you click into the (two
superimposed) textboxes, or tab into it, the editable RcvdDate textbox will
become active, but you'll still see the word Pending if appropriate.

 

[John W. Vinson]

9. I see a blank textbox for those records that previously had a received
date. This is not good. The received dates previously entered should not
change. Those records are complete.

10. I cannot enter any information into the Rcvd textbox.

I understand when you say the Pending textbox is not editable. I thought
when I tabbed into the textbox that says, “Pending”, I was actually editing
the RcvdDate textbox which should now be active. If I understand correctly,
this will allow me to input a received date as pending requests are filled. I
have followed your instructions line by line (I don’t think I missed
anything) and re-read your explanations. I understand what you’re saying. I
just don’t understand why this isn’t working.

You may need to play with the "Special effect" property of the IsPending
textbox - set it to Transparent. This will let you see the underlying
txtRcvdDate textbox.

 

[Aria]

I hate to keep bothering you with what seem like such trivial needs and I’ll
let it go if I can’t get it to work this time but…I continue to have issues.

I set txtRcvdDate2 to transparent and can now see the previously filled
request dates.

Previous teachings:

You can - again - carefully move the IsPending textbox so that it

is superimposed *OVER* the RcvdDate textbox; when you click into the (two
superimposed) textboxes, or tab into it, the editable RcvdDate textbox will
become active, but you'll still see the word Pending if appropriate.

…then the user can click or tab into the RcvdDate box and type (or

edit) a date.

You tried to edit the Pending textbox.

IT IS NOT EDITABLE. It makes no sense to edit it. It's *just for display*. If you want to edit >something, edit RcvdDate. The textbox properties I described should make it impossible to set >focus to the Pending textbox or to type in it at all…

So my understanding is that when I click or tab into that control, the
RcvdDate textbox becomes active and editable.

This is what is happening (I don’t know why there are various outcomes using
essentially the same procedure.):

1. If I click in the RcvdDate control “pending†remains. When I highlight
the text and try to edit it, the focus is shifted to txtReason (an
explanation textbox for all requests).

2. I move once again to RcvdDate, highlight the text and when I try to type
the first 2 characters of a date, nothing happens and I hear a beep.

3. When I tab into the control, “pending†disappears but still has the blue
back color.

4. When I try to input a date, the focus is shifted to another record much
farther down the form.

5. I am not able to type a date whether I tab into or click into the control.

So I am left with the conclusion (which may be erroneous) that txtRcvdDate2
is still the active control, otherwise I would be able to input a date. The 2
textboxes should be the same size correct?

Am I doing something wrong? Ideas/suggestions?

 

[John W. Vinson]

1. If I click in the RcvdDate control “pending” remains. When I highlight
the text and try to edit it, the focus is shifted to txtReason (an
explanation textbox for all requests).

2. I move once again to RcvdDate, highlight the text and when I try to type
the first 2 characters of a date, nothing happens and I hear a beep.

3. When I tab into the control, “pending” disappears but still has the blue
back color.

4. When I try to input a date, the focus is shifted to another record much
farther down the form.

5. I am not able to type a date whether I tab into or click into the control.

So I am left with the conclusion (which may be erroneous) that txtRcvdDate2
is still the active control, otherwise I would be able to input a date. The 2
textboxes should be the same size correct?

Am I doing something wrong? Ideas/suggestions?

Doublecheck the properties of txtRcvdDate2. You should not be able to set
focus to it AT ALL, if it is correctly set to Enabled = No, Locked = Yes, Tab
Stop = No. I'm perplexed that you're getting this effect!

Try debugging it with the two textboxes separated. I'm not sure how you're
setting the backcolor; you may need a line Me.Repaint in the AfterUpdate event
of txtRcvdDate to "wake up" txtRcvdDate2.

Having the "Pending" still visible while you're trying to edit the underlying
date is an additional complication. You might want to set txtRcvdDate2's
Visible property to No in the GotFocus event of txtRcvdDate, and make it
visible again in LostFocus.