Robin-
I don't think you can do it with the Default Value property. You would need to write some code in the AfterUpdate event of the MemberID field in whatever form you use to add new member history information. You would also need a query something like:
SELECT tMemberClubHistory.MemberID, tMemberClubHistory.Status
FROM tMemberClubHistory
WHERE tMemberClubHistory.DateUpdated =
(SELECT Max(DateUpdated)
FROM tMemberClubHistory AS MCH2
WHERE MCH2.MemberID = tMemberClubHistory.MemberID)
That query will return the "latest" status for each member. You can then use that in a DLookup with a filter on MemberID to find the latest status for any given member.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
On Feb 23, 2014, at 10:29 PM, Robin Chapple <robinski@mymail.net.au> wrote:
> I am using Access 2013 on a Win7 system. I am NOT a guru.
>
> I have a club membership project the uses a table 'tMembers' to
> record member information and 'tMemberClubHistory' to record the
> history of each member and the clubs of which they have been members.
>
> MemberID is the key field for 'tMembers'.
>
> The status of each member is recorded in 'tMemberClubHistory'.
> Examples = "Active" or "Honorary" or "Terminated" for instance. Each
> record shows "DateUpdated" when changed.
>
> Is there any way that I can collect the most recent 'Status' for a
> member from 'tMembersClubHistory' and have it entered by default in
> the 'Status' field in 'tMembers'?
>
> Currently this is a manual operation by the data entry persons and is
> open to problems.
>
> Many thanks,
>
> Robin Chapple
>
>
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar