Use Dlookup to set value of a related field

[CharlieT302]

Hi All,

I have a database that tracks the most recent program to which a patient is assigned. The current programs assigned to all patients comes to us via a spreadsheet each month (it must be a spreadsheet. we have no say in this).

We import the program names from the spreadsheet each month and each program is identified with a number (1-3). If a program changes, we need to mark the [Disenrolled Date] field of the most recent (current) assignment with the current date and then import the new program name into a new record. Importing the new record is not an issue.

My Only Issue:
I have a Totals query which locates the Program# of the most recent entry (based upon the "Enrolled Date" field). However, a Totals query cannot be used in the editing of existing records. I wanted to use a Dlookup function that uses the query to locate the most recent record and then set the value of the [Disenrolled Date] field.

In other words, I want to locate a specific record based on the value of one field, and then enter a date value in a corresponding field. Does that make sense?

Thanks in advance for any input

 

[PHV]

What have you tried so far and where in your code are youy stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CharlieT302]

Actually, that's the point. I am drawing a blank on setting up the code. I can use a dlookup can locate the record, but how do I reference another field on that same record and set its value, or is this a bad approach?