When I add a field to a form from a query with the Max function, I can no longer add/modify records via the form.

presumably the max field is not a linked field, just a form-based text box?
the MAX data applies to a whole table or recordset (maybe on other criteria), while a field only displays data for the current record, hence the reason you cannot update.
when you say 'only displays the most recent record' the datasource of the form needs a subquery deriving the max field, and the record ID (and grouped with whatever criteria you want to use.
This subquery would then be joined to the main datasource (table or another query) on the ID. This way you only display the subset of records corresponding to the MAx for each criteria.

e.g. SELECT Tx.Cust_ID, Max(Tx.TX_InvoicedDate) AS MaxOfTX_InvoicedDate FROM Tx GROUP BY Tx.Cust_ID;
this query gets the customer id and latest invoice date for each customer

Totals queries are not updateable.  Once data is aggregated, the underlying record identification cannot be maintained.  Remove the aggregation from the bound query and add a subform to show the status history.

Sorry for the delayed response.  Out of town for the holidays and all that jazz.  Hope all of you enjoyed them.

@COACHMAN99, I can't get that to work.  I posted an earlier question about it and received a solution.  However, now I can't get the info onto an editable form.  I realize it's because I have the Max function in one of the underlying queries.  But I don't know how to get around using that function to get the information I need.

@PatHartman, thank you.  You always provide the most direct solution (which I often overlook until you point it out).  Unfortunately, a subform won't work in this scenario.  The information I need to display is too "small" to warrant a subform.  It needs to be a part of the subform I'm already using in frmAssignments.

I realize this is quite an involved question.  I tried setting up a live help session with it before posting it here.  I was advised to post it first.  I'm more than happy to either set up a live session or even outsource parts of the project.  I don't quite know how all that works around here so any information is greatly appreciated.

Nothing is too small for a subform.  History implies multiple values.  When you need to show multiple values, the best solution is a subform although sometimes a listbox will work.  You can hide the framework so the subform blends better with the main form and doesn't stand out.  And besides, the subform is a no code solution.  Just set the query to sort descending so the record you want ends up on top and use the TOP predicate to select only one record.  Set the subform to not allow adds or deletes or updates.   If you fiddle a little, you can make the subform look like an ordinary field.

Thanks Pat.  I'm not sure if you've looked at the frmAssignments.  It already has a tabbed subform and the "status" should only be a column in one of the tabs of that subform.  I can't picture how to design it so that it would look seemless.  I'm already struggling with finding a way to allow the users to update the status without having to open another form.  If they have to open/view two more forms to get see/update the status the procedure gets very clumsy in this Assignment form.

To avoid this type of issue, I keep the current status in the main record.  Each time the status changes, I create a history record.  That completely separates history from current.  History includes the current status as well as history so it can be used by itself for analysis if you want to map status over time.

To convert from one status to the history model, create an append query that copies all the current status values to the history table.  Then put the append query into the AfterUpdate event of the main form.  If Status doesn't change each time, then set a flag that status was changed so in the AfterUpdate event you'll know that you have to append the current status.

Let's see if I understand this clearly.

I already have a "history" table, tblProviderStatus.  Every new record needs ProviderID, AssignmentID, CurrentDate and StatusID.  I can pick up all of that information from the Assignments form (which is a tab in a subform of an Orders form).

I need to re-create the "status" field I initially deleted from the tblAssignments.  It was and probably needs to again be, a lookup field to my table tblStatus so I can grab the StatusID, Code and Description but show only the code and description in the a drop-down box.

Now this is where it gets a little off from what you're describing..."Then put the append query into the AfterUpdate event of the main form."

The Assignment Details form which should include the "status" of the assignment, is, again, a tab of a tabbed subform in the Orders form.  I need (or would-like) the user to be able to click the drop-down box on any one of the assignments listed in the datasheet to change the current status to a new status.  So, will it work if I create an Append Query on the On Change event of the Combo box instead of on the AfterUpdate of the form?  I wouldn't even know where to start with the AfterUpdate of the form because of all the nesting.

NEVER, EVER use lookups at the table level.  There are lots of problems with them that many others have written about at length so I won't repeat them here.  table level lookups are a crutch and as soon as you want to create queries or VBA, you will find that they are not the panacea they appear to be.

The Change event runs once for each character that is typed in the box so it is a poor choice for this since it would append multiple rows to the history table.  You could use the AfterUpdate event of the control but the problem with that is that you are appending a history record without knowing if the status change will ever be saved so you would have to get creative in other form events to determine if the record actually got saved because if it didn't, you would need to delete the history record.

I didn't suggest the form's AfterUpdate event randomly.  You only want to add the history record once you know the form record has actually been saved.  So the only event that satisfies that criteria is the Form's AfterUpdate event.  The issue is that once a record is saved, the .OldValue and .Value properties are identical so there is no way to know what fields changed.  Therefore, if the Status doesn't change every time you save the record, you have to set yourself a flag that you can later check in the Form's AfterUpdate event that you can use to trigger the write to the history table.

Pat, I love your solution!  I solves both of the issues of allowing the user to view the current status as well as update the status without having to navigate multiple forms.

Unfortunately, I don't know how to do this:  "set yourself a flag that you can later check in the Form's AfterUpdate event".  I tried looking up some articles and other posts, but...still not very clear for me.

Like I mentioned before, I'm more than happy to set up a Live session if you're set up for those.  That way I don't feel like I'm taking advantage of your time and skill.

"NEVER, EVER use lookups at the table level"  I din't know this!  I have quite a few changes to make to tables.  :-(

"The Change event runs once for each character that is typed in the box"  It will be a combo box so if a code is selected shouldn't it count as a single "change"?  In any case, I can certainly move the Append query to to the form's AfterUpdate event.  The form is viewed in datasheet view, so any change to one of the records, then clicking away from the record saves it.  I'm assuming that the "flag" you're referring to is what will prevent multiple records being added to the status table?

If the max field is unbound then it can be populated independently of all the other (bound) fields on the form. Refresh it on the form current event.
The query for the max field should not be part of the main (form) query.

Coachman,  

I can't get it to work like that.  

Here's the answer I used to gather the correct data from the Provider Status table based on the most recent date.

https://www.experts-exchange.com/questions/28988384/Help-choosing-most-recently-dated-record-in-an-Access-table.html

In the current event
Yourfield=dmax (table field, yourtable, criteria)

Of this isn't valid, or close, them please disregard. And wait for other people's input

I can't get that to work Coachman.  I need to pull the StatusCode from a join of the tables tblProviderStatus and tblStatus where the StatusDate in the tblProviderStatus is the most recent date.  But the tblStatus can't limit the records from the tblProviderStatus so I already have a Left Outer Join.  Then I need  the "most recent" StatusCode for each combination of AssignmentID and ProviderID which come from the table tblAssignments.

I can do all of the above using the Max function in a query, but not in a form control.  

The form I'm trying to add status to is the frmAssignments in the attached database.

Thank you for your help.  Your suggestions are much appreciated.

fabi,
I am not set up to do gigs or live chats.  I can't do them during the day and my rate is higher than EE charges.  I also don't believe that chat works for this type of collaboration.  When I work with clients interactively I use TeamViewer or GoToMeeting.  So make a copy of your database and give it a shot.  What I suggested really doesn't take more than a few lines of code.

1. Declare a form level boolean variable (private but above the first procedure header in the form's class module).
2. Set the variable to False in the Form's currentevent
3. Set the variable to True in the Status Coontrol's AfterUpdate event
4. Check the variable in the Form's AfterUpdate event.  If it is True, insert the history record using the current form field values and then set the variable to False.

If you can do it in a query then you can assign the returned value to the unbound form control (textbox) using the code i supplied above and, instead of a table for the second argument, use your query.

Thank you so much Pat.  I just didn't want to take advantage of your time.  I'm going to give it my best shot because it truly is a seamless solution if I can manage to code it.

Last, hopefully quick, question.  I'm getting many appended records instead of just one like I should.  You said "insert the history record using the current form field values".  It wouldn't matter if the form was in datasheet view would it?

@Coachman, I understand what you're saying.  It would work beautifully if I had a single table I was querrying.  But what I have is a mess of squirrely joins.

I'll keep trying your way if I can't get Pat's suggestion coded.  But I can't imagine how it will work.  I would have:

txtStatus=(StatusDescription,

SELECT [tblProviderStatus.AssignmentID] & "-" & [tblProviderStatus.ProviderID] AS Composite, Max(tblProviderStatus.StatusDate) AS MaxOfStatusDate, Max([PFirstName]) & " " & Max([PLastName]) AS Provider, Max(tblSpecialties.SpecialtyCode) AS MaxOfSpecialtyCode, Max(tblClients.CName) AS CName, Max(tblAssignments.AStartDate) AS AStartDate, Max(tblStatus.StatusCode) AS MaxOfStatusCode, Max(tblAssignments.AEndDate) AS MaxOfAEndDate, Max(tblAssignments.OrderID) AS MaxOfOrderID, Max(tblAssignments.AssignmentID) AS MaxOfAssignmentID, Max(Switch([StatusDescription]="On Assignment",1,[StatusDescription]="Extension",2,[StatusDescription]="Dates Moved",3,[StatusDescription]="Credentialing & Privileging",4,[StatusDescription]="Accepted",5,[StatusDescription]="Submitted",6,[StatusDescription]="Declined",7,[StatusDescription]="Fall-Off",8)) AS SortField, Max(tblProviderStatus.ProviderStatusID) AS MaxOfProviderStatusID
FROM tblStatus INNER JOIN (tblSpecialties INNER JOIN (tblProviders INNER JOIN ((tblClients INNER JOIN tblJobOrders ON tblClients.ClientID = tblJobOrders.ClientID) INNER JOIN (tblAssignments INNER JOIN tblProviderStatus ON (tblAssignments.AssignmentID = tblProviderStatus.AssignmentID) AND (tblAssignments.ProviderID = tblProviderStatus.ProviderID)) ON tblJobOrders.OrderID = tblAssignments.OrderID) ON tblProviders.ProviderID = tblProviderStatus.ProviderID) ON tblSpecialties.SpecialtyID = tblJobOrders.SpecialtyID) ON tblStatus.StatusID = tblProviderStatus.StatusID
GROUP BY [tblProviderStatus.AssignmentID] & "-" & [tblProviderStatus.ProviderID]
,
criteria (I don't even know how to define this.  I need to match AssignmentID and ProviderID from the form record to the query record)

My apologies.
I thought you wanted to display individual max values as informational data on a form bound to an editable source. Please disregard all my input
.

Oh gosh, no, no apologies.  I am beyond grateful that you thought to help.  It's difficult for me to express exactly what the problem is that I'm stuck on when writing it out.

When code runs in a form, it is relative to the current record.  A form is bound to a recordset made from a table or query.  It doesn't matter whether the form is set to show a single record or multiples.  When the form is in continuous or DS view, you are just seeing multiple instances of the same form but only the one displaying the current record is "active".  Think of a form as a window into the data.  When you look through the window, you see only one record at a time.  As the current record changes, the view through the window changes.  Multi-record forms can make data from records before or after the current record visible but they are not "live".  The recordset is like train cars passing a fixed point.

If you are inserting too many history records, the code is in the wrong event or you are not properly initializing the variable tat tells you when the status actually changed.  Please post your code.

I haven't figured out yet how to create and initialize the variable.  Still looking up information and working on that.

To append a record to the 'history' table I created an append query and then called it from the form's AfterUpdate event.

Private Sub Form_AfterUpdate()
        CurrentDb.Execute "qryProviderStatusUpdate"
End Sub

qryProviderStatusUpdate:

INSERT INTO tblProviderStatus ( AssignmentID, ProviderID, StatusID, StatusDate )
SELECT tblAssignments.AssignmentID, tblAssignments.ProviderID, tblAssignments.StatusID, Now() AS StatusDate
FROM tblAssignments;


Instead of using that query, do I need to create temp variables for the form's current record and then use those values to append a record to the history table?

Here is some of the new code I added to define the variable:
1. edited to call the variable correctly
2. it isn't recognizing the Me.Fields as values

Option Compare Database

     Dim StatusChange As Boolean
-------------------------------------------
Private Sub Form_Current()
    StatusChange = False
End Sub
------------------------------------------
Private Sub StatusID_AfterUpdate()
    StatusChange = True
End Sub
-----------------------------------------
'I removed the append query and added this code instead
'I didn't list the ProviderStatusID field to update because it is an auto-number primary key field in the table

Private Sub Form_AfterUpdate()

    Dim strSQL As String
        
    If StatusChange = True Then
    
        strSQL = "INSERT INTO tblProviderStatus (AssignmentID, ProviderID, StatusID, StatusDate)VALUES (Me.AssignmentID, Me.ProviderID, Me.StatusID, Now());"
    
    DoCmd.RunSQL strSQL
   
    End If

End Sub

Select allOpen in new window

This is such a beautiful solution!  I can't thank you enough for your help with this.  

It works like a charm, completely seamless to the user.  Except this message after updating the form:

"Microsoft Access
You are about to append (1) row.
Once you click Yes, you can't undo..."

Is there a way to suppress the message?  Also, am I supposed to reset the variable to False in the form's AfterUpdate event?

Yes, reset StatusChange to False after you run the update query.  I forgot to follow my own directions.

Suppressing warnings is dangerous to your health so when you do it, it is IMPERATIVE that they always get turned back on after you run the code where you want them to be suppressed.  My solution is to use two macros (the ONLY macros I ever use).  One turns the hourglass on and warnings off and the other turns the hourglass off and warnings on.  The purpose of using the hourglass is to give myself a visual clue that warnings are off.  You only have to be burned once to be ever vigilant about getting them back on.  If I am testing and stop the code at some point, having the hourglass on is so annoying that I don't forget to manually run the macro to turn the warnings back on.  I've attached a picture of the warningsoff macro I use. Add
DoCmd.RunMacro "mWarningsOff"   '''''''before running sql

DoCmd.RunMacro "mWarningsOn"   '''''''''' after running sql

See, I told you it wouldn't be hard:)

This is great!  Thanks again for all your help.

You're welcome.