Need to display current phase & date from a list of phases & dates 2

[Sherman6789]

We have an Access database which records the status of completion and other information about most publications and products created or distributed by our office.

Encluded on the main input form are tabs which display all sorts of data, deadlines, costs, etc. about the items. One area tracks the phase status of the items in a subform pull down combo box. Each item can go through from 2 to 12 phases before completion. Phases can be different for each item type That's why a combo box is used to allow for the differences. There are three phase related fields. (Phase name, expected "in-phase" date and the actual "inphase" date. This works well; however we would like for the current phase and actual received date to show on the main form at all times. Whenever the item enters into a new phase, we would like the current phase and date to be automatically updated. All previous phases and date will continue to be shown in the list.

Thanks for your help. Hope this makes sense.

 

[Sherman6789]

I forgot to mention that a few phases and expected In-dates may be listed or all phases and expected in-dates may listed. As the item goes from phase to phase, the actual in-date is entered into the actual in-date will be entered.

We only want the latest "actual in-date" and "phase name" to be shown in the area on the main form.

Thanks.

 

[Remou]

What is the difficulty? From the above it seems that selecting the phase associated with the max date from the phase table for each item should work. What point have I missed?

 

[dhookom]

If I understand correctly you can create a subform to display on the main form. A example of the record source for the subform using the Northwind sample database where you want to disply the most recent order record for a CustomerID

SELECT Orders.*
FROM Orders
WHERE Orders.OrderDate=(SELECT MAX(OrderDate) FROM Orders O WHERE O.CustomerID = Orders.CustomerID);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sherman6789]

Thanks for your comments, Remou.
Either what I am asking is simpler than I think or I have not presented the "problem" correctly.

There are two tables linked together.
The first table has general information about the products. The second table contains three fields and are shown on a tab (sub form). The information on the subform can be seen only after the tab is clicked. Here are the fields in table two.

1. Phase name (selected from combo box)
2. Date "Expected" to reach the phase
3. Date "Actually" reached the phase

Examples:
PHASE Expected-In Actual-In
===================================================
In Development 7/10/07 7/11/07
Editing 7/15/07 7/13/07
Costing Dept. 8/16/07 8/17/07
Waiting Approval 9/20/07
Completed 9/30/07


Three of the fields in the first table are:

Item Name: 2007 History Book
Current Phase Status: Costing Dept.
Since: 8/17/07

The above three fields will be updated immediately as the phase staus is updated in Table two. This items along with other general information can be seen at all times. Since the list of phases can be fairly long; the user might need to use the scroll bar to get to see the current phase in table 2. (bottom of the list)

However, the user will be able to "instantly" see the current phase status from table one at all times on the input form.

The user will not be able to change the status except through table two inputs.

Hope this explains better. If it is still very simple, please tell me what I need to do and how.

In any event, thank you for your advice.

 

[Sherman6789]

Thanks Dhookom,
Your reply was sent while I was preparing my answer to Remou. I am studying you suggestion now.

 

[Sherman6789]

Hi again,

The subform is shown on the main form as you mentioned. However it is shown within a tab.
A "lot" of information is shown on the main form (dozens of bits of information). To keep it from looking so cluttered. eight tabs break up the information. Above the tabs, the user see certain information at all times without having to click a tab until necessary. The user would like to see the current status at all times. Remember that this information will be pulled from fields in the second table.

When the phase tab is clicked, a list of phases is seen starting with the first phase to the end. The could easily be 12 or more phases.

 

[dhookom]

My suggestion was to put the subform showing the most recent status on the main form, not on any tab. You would use the link master/child as usual but set the locked properties to yes and enabled to no.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sherman6789]

Hello hookom,

I think you have it. I DO want the current phase information placed on the main form. The entry of the various phases along with the dates are done in the tab now. This works well. We want to see the current phase and date on the main form. I mentioned the tab only because that is were the updates are done now. The separate display of the current phase should be on the main form.

I think that you have answered it. I just need to understand and follow you advice. I will re-read your last two messages. Will the info be placed in a label or a table on the main form?
Thanks!

 

[dhookom]

As I have mentioned twice already, the information from the query will be displayed in a subform.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sherman6789]

Thanks.
It finally soaked in.