Refer to specific record in continuous form

[GS500]

I have a main form with a continuous sub-form. On the main form I have a series of text boxes that I want to use as a makeshift status bar. The text boxes would be filled in if a value was entered into certain records on my sub-form.
The problem I have is since the sub-form is continuous, how would I refer to the specific record on the sub-form that I want to check for a value?

Thanks!

 

[boblarson]

So, can you describe this a bit in real terms? I'm having a hard time understanding what you want to get. You have, say 20 records, and you want to show something based on only one of those records? That confuses me, so a little more explanation might help (don't try to simplify, use your real terms). And we may have some other input once we find out as it may not be an efficient design and could be done better.

 

[GS500]

Sure, my post was a bit vague, sorry.

This database will be used to track dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc.

The main form has information such as Claim Number, Rep Name, Location. The subform has a list of Event Names and Dates. As a claim progresses the user would fill in the date each event occurs.

At the top of the main form I have a series of unbound textboxes lined up in a row. My idea was that as the user fills in dates in the subform, the background color of the textboxes would change, to show at a glance where in the total process the claim is.

The problem I'm having is that since the subform is continuous and there is only one control for the EventName and one for EventDate, I don't know how, or if its possible to say "If Estimate Date is filled in, then change the background color of textbox3."

Here are the tables that are related to these forms:

tblClaim
ClaimID
ClaimNumber

tblEvents
EventID
EventName

tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate

Thanks again

 

[boblarson]

You could use a DMax function to get the status with the latest date.

 

[GS500]

Dmax would return the latest date, correct? How do I get which status goes with the date?

 

[GS500]

I just realized I said status bar earlier, I meant progress bar. So for instance, once the "Date Assigned to Rep" date is filled in in the subform, the first textbox, which is the first segment in the progress bar, should have its background color change.

Then once the "Date Estimate Complete" is filled in, the 2nd textbox, which would be the 2nd segment of the progress bar, would change.

These date fields are in the subform, which has as its control source a query:

SELECT tblEvents.EventName, tblClaimEventsDates.EventDate, tblClaimEventsDates.fkClaimID
FROM tblEvents INNER JOIN tblClaimEventsDates ON tblEvents.[EventID] = tblClaimEventsDates.[fkEventID];

So I imagine there is a way to have textbox that makes up the progress bar look and see if it's corresponding event is filled in, just not sure how.

 

[GS500]

OK, I think I figured it out. I set the control source of the text boxes to:

=IIf(DLookUp("EventDate","qsfrmEvents","[EventName] = 'Name of corresponding Event for textbox'")>0,True,False)

Which returns True if the date has been entered. I then used conditional formatting to set the background color and text color of the textbox to Blue.