CODEWITHSUNDEEP
ms-accessms-access-2010
Matt
Matt

Reputation: 982

How can I restrict data choices from linked table MS Access

I am trying to design an MS Access database that stores sports information. I haven't used Access in years and have only limited knowledge of SQL and I'm struggling with something which is probably quite simple but has yielded nothing by searching for answers (I suppose because I'm not quite sure what keywords I'm actually supposed to be searching for).

I'm creating a fixtures table and in the table I have the following fields:

The problem I'm facing is that without restricting the data choices in some way it creates an issue where - when entering data into the database - a user could select the same team for home team and away team.

So my question is - how can I make it so that once the home team has been picked (or away team if that info is entered first), that particular team is no longer available to be selected when picking the opposing team?

I am using MS Access 2010.

Upvotes: 0

Views: 233

Answers (1)

user2204315
user2204315

Reputation:

You make a form to input fixture details. The controls are FixtureDate, Away and Home. Away and Home are lookups to Teams. You then create a combo box for each team, named AwayLookup and Home lookup. They lookup the teams and add them to HomeTeamID and AwayTeamID. In the BeforeUpdate even for each TeamID you add:

If Me.Away = Me.Home Then
    DisplayMessage ("Impossible!")
    Cancel = True
End If

P.S. Const conAppName = "Football"

Public Sub DisplayMessage(strmessage As String)

MsgBox strmessage, vbExclamation, conAppName

End Sub

Upvotes: 1

Related Questions