ACCESS FEED: RE: [MS_AccessPros] Value of a field based on another field

Senin, 12 Agustus 2013

RE: [MS_AccessPros] Value of a field based on another field

Phil-

You can't do it directly in the tables, which is what you imply. But you
can design a Form to edit the Matches table, then write a bit of code in the
After Update event of the ClubID to lookup the appropriate ManagerID. It
might look something like this:

Private Sub ClubID_AfterUpdate()
Dim varManager As Variant

' Make sure a date has been entered.
If IsNull(Me.MatchDate) Then
MsgBox "You must enter a match date."
' Put the focus back there
Me.MatchDate.SetFocus
' Do nothing else
Exit Sub
End If

' Try to look up the ManagerID
varManager = DLookup("ManagerID", "ManagersAndClubs", _
"ClubID = " & Me.ClubID & _
" AND StartDate <= #" & Me.MatchDate & _
"# AND EndDate >= #" & Me.MatchDate & "#")
' Will get Null if no record
If IsNull(varManager) Then
MsgBox "No manager found for the club and date selected."
Else
Me.ManagerID = varManager
End If
End Sub

Hope that gets you started.

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)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of pdk444444
Sent: Monday, August 12, 2013 1:20 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Value of a field based on another field

Hi Team

I need a bit of help once more if anyone would be kind enough to have a look
at this.

I have a table called Managers and Clubs with the following fields

id
managerid
clubid
startdate
enddate

It keeps records for the manager history of a club

I have a table called Matches with these (relevant) fields

matchdate
clubid
managerid

When I leave the clubid field, I would like to get the manager id
automatically from the managers and clubs table where clubid matches and
matchdate is between startdate and enddate.

If the relevant record doesn't exist I would like to display a message to
that effect

I want the managerid to then be stored in the matches record

It is probably one of those that is very straightforward but I am getting in
a bit of a mess with it and not now thinking clearly !!!

Any help would be very much appreciated

Phil

------------------------------------

Yahoo! Groups Links