After Update
Value From a Form
Value From Subform

So, use the after-update event, get the value from the subform, and put it into the top continuous form. The Update Event would be placed on the worksheet subform and carried over to the other form; is that correct?

When I open the form, it shows the same thing for each row until I click on each row, and then it shows the proper entry for each row. Is there a way to resolve that issue?

Also, if I refresh, it resets every as though I just opened the form with all the same entries being the same.

Is there a way to resolve the issue from the last screenshot? When I open the form, it shows the same thing for each row until I click on each row, and then it shows the proper entry for each row.

I have tried several different options to correct this issue. If I bound the form to a query, it lists each row with something written in it. How do I fix this issue? The last two screenshots show what it looks like when opened and when each row is clicked on, correcting itself. I am using { =[LoopExtraNotesForm].[Form]![Notes] } to get the information from one form to another. I am at a loss on how to resolve it. Any help would be great.

I'm out of the office this week, but if none of the guys can help you let me know and I'll look at it when I get back Monday.

Thank you for your response. I can sent or post more screenshots. All in the information is fictitious at this point until its ready to go live. We are working on going paperless and using Access to keep a lot of the records. Thanks again.

Hello, Mr. Rost
I'm currently working hard to find a solution to the problem at hand. I would greatly appreciate any assistance. I appreciate your help. If you require additional screenshots, kindly let me know. I am highly motivated to rectify the issue. Your classes and technical help videos have been beneficial and informative to me.

I'd love to help you, but thanks to the global issue affecting Delta, I'm stuck out of state until at least Friday now. Sorry.

Hi William, you see the same thing in the entire column because the criteria is not correct.  To show row-specific data, you need row-specific criteria.  What constitutes "busy" or "not busy"?  In the first row, what "location" do you expect to be in the textbox next to "busy", and what "location" should be next to "not busy"?  I see you want "Colfax and Billings" put next to "not busy" on the first row, but I'm not clear on how it is considered "not busy" from your screenshots.

The "busy" and "not busy" indicators are functioning correctly. However, there is an issue with the carryover line from the other form, which displays the location where Loop Extras are being staged. The dispatchers have requested a quick overview of the Loops without having to click on each Loop to see their location.

The "Busy" and "Available" are activated by the dispatchers to show each other which Loop has been assigned an assignment.

The location should be the last note or location entered into the other form.

However, when I open the form, all the locations are the same until I click on each line.

The red outlines are the same information; the top part is a continuous form, and the bottom left is the form to fill out. The other form is more like the assignments given to each Loop Extra. The section I am trying to carryover is the "Notes / Location." It does carryover, but the glitch is when you open the form listing all the same locations as the first entry.

=[LoopExtraNotesForm].[Form]![Notes] is what I used to carryover the notes.

For Access to know what the "last note entered' is, there needs to be a date/time field indicating when the user made the entry.  I don't see that field in your forms or tables.  Access doesn't record user entries' dates and times for you.  You have to do it yourself.

As I said, you need to use row-specific criteria in order to show row-specific info in each row.  From your screenshots of the top form, each row has these fields: Time Out, Time In, Division, Loop Name, etc.  THESE are what you need to use to form your row-specific criteria.

The Start Time & End Time are Date/Time stamps with only visible Time.

A hidden Date/Time field indicates when the entry was made. The "StartTime" & "EndTime" are meant to be time fields that the dispatcher can change

The reason for the Date/Time on the Start Time & EndTime is for the DateDiff function in the TimeSaved.

The control source in your picture =[LoopExtraNotesForm].[Form]![Notes] simply shows whatever Notes field that is on the screen, and that is why you see the same value in the whole column.  Later they show different values only because they haven't been refreshed yet.  Press F9 (the key for refresh) and you will see the whole column is the same again.  That's because =[LoopExtraNotesForm].[Form]![Notes] can only show *one* value, the one that is on the screen where the record selector is pointing at.  As I said, your control source needs to evaluate to row-specific info, based on other info that is NOT necessarily on the screen.  You need to use DLookup() to look up values of Notes that pertain to other records -- records that aren't shown on the screen.  If you aren't familiar with DLookup, this site has free and paid videos on that.  More importantly, you also need to fundamentally understand what a control source does exactly.

This is covered in Expert 11, Lesson 5.

Ok, If I do away with the =[LoopExtraNotesForm].[Form]![Notes], which still pulls the values from the notes field from the LoopExtraNotesForm and copies them to the LoopExtraForm but lists all the fields as the same until I click on each row.

Now, if I use the DMax to pull the ExtraDate(hidden time stamp) from the LoopExtraNotesForm to find the last entry for the LoopExtraID and then use the DLookUp to find the [Notes] for each row from the LoopExtraNotesForm to display in the LoopExtraCForm.

Something Like:
Private Sub Form_Current()

If IsNull(LoopExtraID) Then Exit Sub

Dim MaxExtraDate As Date

MaxExtraDate = NZ(Dmax("ExtraDate","LoopExtraNotesT","LoopExtraID=" & LoopExtraID),0) 'Last DateTime Entry on the LoopExtraNotesT
If MaxExtraDate = 0 Then Exit Sub
DLookUp("Notes","LoopExtraNotesT","ExtraDate="""& NZ([Notes],"") & """")

I should have referred to the LoopExtraNotesForm as the LoopExtraNotesT when I described pulling ExtraDate from the form.

Ok, I have been able to create the queries to pull the notes from the table.

Query1
SELECT LoopExtraT.LoopExtraID, Max(LoopExtraNotesT.ExtraDate) AS MaxOfExtraDate
FROM LoopExtraT LEFT JOIN LoopExtraNotesT ON LoopExtraT.LoopExtraID = LoopExtraNotesT.LoopExtraID
GROUP BY LoopExtraT.LoopExtraID;

Query2
SELECT LoopExtraMaxDateQ.*, IIf(IsNull([MaxOfExtraDate]),"No Information Submitted",DLookUp("Notes","LoopExtraNotesT","LoopExtraID=" & [LoopExtraID] & "AND Extradate=#" & [MaxOfExtraDate] & "#")) AS [Location Notes]
FROM LoopExtraMaxDateQ;

You are missing a space before the 'And'.

Now the only part I'm looking at is doing the deal look up to pull the notes from the quarry and put on the field in the form.

Dlookup***.
Auto spell is a pain

If I'm correct, I would do a DLookUp, pulling the notes from the query to be able to go in the source on the form field?

Why don't you put the whole IIF function directly in the Control Source of the field on your Form? That way you use one DLookup instead of two.

Ok I'll try it.

Ok, so I tried to use the:
=IIf(IsNull([MaxOfExtraDate]),"No Information Submitted",DLookUp("LocationNotes","LoopExtraNotesT","LoopExtraID=" & [LoopExtraID] & "AND Extradate=#" & [MaxOfExtraDate] & "#"))
in the control source, all I get is the #Name?.

Is the query I get the notes I am looking for but am having an issue getting the note onto the form where it is needed. How can I get the notes carried over onto the form from the query?

This is the part in the query I use:
Location Notes: IIf(IsNull([MaxOfExtraDate]),"No Information Submitted",DLookUp("LocationNotes","LoopExtraNotesT","LoopExtraID=" & [LoopExtraID] & "AND Extradate=#" & [MaxOfExtraDate] & "#"))

Using DLookup, can I use the same principles except for pulling from a quarry, not a table. I have tried using a quarry to feed the form with the information from the other quarry for the notes then that becomes unupdatable. I have also tried creating a table from a quarry, which would not update from the other query. Could this be handled using vba, if so where would it need to be placed?

Once you create an aggregate query, then you instantly become Not Updateable. So if you need to use those dates with an updatable query, make a second query and then link to the ID. It's kind of like the concept that I use in this video: Sum of Last Orders

After a lot of trial and error, I was able to get it to work somewhat. Then, after adding it to the control source and adding VBA to the subform to have it requery, it worked.

1st Q
SELECT LoopExtraNotesT.LoopExtraID, Max(LoopExtraNotesT.ExtraDate) AS MaxOfExtraDate
FROM LoopExtraNotesT
GROUP BY LoopExtraNotesT.LoopExtraID;

2nd Q
SELECT LoopExtraMaxDateNotesQ.LoopExtraID, LoopExtraMaxDateNotesQ.MaxOfExtraDate, IIf(IsNull([MaxOfExtraDate]),"No Information Submitted",DLookUp("LocationNotes","LoopExtraNotesT","LoopExtraID=" & [LoopExtraID] & "AND Extradate=#" & [MaxOfExtraDate] & "#")) AS LocationNotes
FROM LoopExtraMaxDateNotesQ;

Form: Control Source
=DLookUp("LocationNotes","LoopExtraMaxDateNotesQ2","LoopExtraID=" & [LoopExtraID] & "")

Subform:  vba
Private Sub LocationNotes_AfterUpdate()

     Forms!LoopExtraCForm.Requery
     Forms!DispatcherNavigationForm.Requery
    
End Sub

As I always say... if it works, it works. :)