Date and Form

[Bryan Hughes]

Hello,

I am not sure how to build this form. I have a table, tblCase_Activity that
has the following:

tblCase_Activity
[CAID] - AutoNumber Field
[CFID] - Case File ID
[CAD] - Case Activity Date; dtm Field
[CAS] - Case Activity Status; blnField

The data is entered on a monthly basis with date and activity (yes for
active and no for inactive).

The [CFID] can be entered up to a total of 42 times.

My users want to be able to view on a form, the activity for a [CFID] in a
month format the activity status for each month.

Basically Month1: Active; Month2:Active; Month3:Inactive; Month4:Inactive,
etc.

How can I set up the form so it takes the entered data, and shows this
format in unbound textboxes?

I hope this makes sense.
Please Help!

-Bryan

 

[Dale Fye]

Bryan,

Why is there a limit on the number of times a CFID can be used?

I don't think I would use unbound text boxes. I'd probably use a
listbox with a RowSource of something like:

Select [CAD], IIF([CAS], "Active", "Inactive")
FROM tblCase_Activity
WHERE [CFID] = Me.txtCFID
ORDER BY [CAD]

This assumes you have a textbox on your form where CFID is displayed.
If this is a bound textbox, then you would need some code in the
current event of the form and in the AfterUpdate event of the textbox
to requery the listbox. If txtCFID is unbound, then you would need to
requery the listbox in the Lost Focus event of txtCFID.

--
HTH

Dale Fye


message Hello,

I am not sure how to build this form. I have a table,
tblCase_Activity that
has the following:

tblCase_Activity
[CAID] - AutoNumber Field
[CFID] - Case File ID
[CAD] - Case Activity Date; dtm Field
[CAS] - Case Activity Status; blnField

The data is entered on a monthly basis with date and activity (yes for
active and no for inactive).

The [CFID] can be entered up to a total of 42 times.

My users want to be able to view on a form, the activity for a [CFID]
in a
month format the activity status for each month.

Basically Month1: Active; Month2:Active; Month3:Inactive;
Month4:Inactive,
etc.

How can I set up the form so it takes the entered data, and shows this
format in unbound textboxes?

I hope this makes sense.
Please Help!

-Bryan

 

[Bryan Hughes]

Dale,

There is no limit in the database the CFID can be used. But there is an
eligibility limit of 42 months for case files for my users. That is why
there is a limit.

If I used a list box how could I show month number along with the activity
status. ("Month1:Active", "Month2:Inactive", etc.)

I do not need it to show actual month. I need it to show month instead as a
month count "Month1", "Month2", etc.

Is there a way I can do this with a list box?

TFTH

-Bryan

 

[Dale Fye]

Bryan,

Is there any chance that a month will be missing? Assuming that there
is no more than 1 record in the table for a CFID and month
combination, you should be able to set the listboxes RowSource of the
listbox to a query that looks something like:

SELECT "Month-" & CSTR(SELECT Count(CFID)
FROM yourTable
WHERE CFID = T.CFID
AND [CAD] <= T.CAD) as
CMonth
,IIF([CAS], "Active", "Inactive")
FROM yourTable T
WHERE CFID = Forms!yourForm!cboCFID

This assumes you have a combo box on the form that lists your
CaseFileID. If you are using a textbox, then you would need to change
the WHERE clause above appropriately. You will need some code, in the
AfterUpdate event of that control, which will requery the listbox when
you make changes to that control.

--
HTH

Dale Fye


message Dale,

There is no limit in the database the CFID can be used. But there is
an
eligibility limit of 42 months for case files for my users. That is
why
there is a limit.

If I used a list box how could I show month number along with the
activity
status. ("Month1:Active", "Month2:Inactive", etc.)

I do not need it to show actual month. I need it to show month
instead as a
month count "Month1", "Month2", etc.

Is there a way I can do this with a list box?

TFTH

-Bryan

Bryan,

Why is there a limit on the number of times a CFID can be used?

I don't think I would use unbound text boxes. I'd probably use a
listbox with a RowSource of something like:

Select [CAD], IIF([CAS], "Active", "Inactive")
FROM tblCase_Activity
WHERE [CFID] = Me.txtCFID
ORDER BY [CAD]

This assumes you have a textbox on your form where CFID is displayed.
If this is a bound textbox, then you would need some code in the
current event of the form and in the AfterUpdate event of the textbox
to requery the listbox. If txtCFID is unbound, then you would need to
requery the listbox in the Lost Focus event of txtCFID.

--
HTH

Dale Fye


message Hello,

I am not sure how to build this form. I have a table,
tblCase_Activity that
has the following:

tblCase_Activity
[CAID] - AutoNumber Field
[CFID] - Case File ID
[CAD] - Case Activity Date; dtm Field
[CAS] - Case Activity Status; blnField

The data is entered on a monthly basis with date and activity (yes for
active and no for inactive).

The [CFID] can be entered up to a total of 42 times.

My users want to be able to view on a form, the activity for a [CFID]
in a
month format the activity status for each month.

Basically Month1: Active; Month2:Active; Month3:Inactive;
Month4:Inactive,
etc.

How can I set up the form so it takes the entered data, and shows this
format in unbound textboxes?

I hope this makes sense.
Please Help!

-Bryan