Very complex, can i set validation rule between a textbox value and a field of table?

[1308057]

Hi everyone, it may be a bit long to describe the situation, thanks for showing understand.

*************************************************
I work in a bank and am establishing a database for internal staff in my department to apply different event, such as golf day, soccer.



To make it simple, assumed that there is 3 teams in my department, each team can apply various events and then get tickets of events.
Each team can at most get the amount of tickets assigned by me.

To do this, I have a table call [tbl_event], there are 4 fields on it, namely,
"Event Name" --- the name of the events
"Team A " ---- maximum ticket assigned to Team A
"Team B " ---- maximum ticket assigned to Team B
"Team C " ---- maximum ticket assigned to Team C

for example,
Event name | Team A | Team B | Team C |
ABC event -----4---------- 5------- 6--------

which means that under ABC event, Team A can get 4 tickets at most.


i have form [frm_event], there is a combo box which row source is [tbl_event].[Event Name]
in this form, there is also a datasheet subform called [subform_event]

When team representive enter the form, they need to enter a password of their team, and then in the form there will be a textbox "Team Number" showing their team name , that is "Team A", "Team B" or "Team C", as a value

When a team representative of Team A choose particular event on the combobox of [frm_event], say ABC event, the subform will come out, and he can enter a number in a column of subform called "request ticket".
Finally I can know how much ticket they need in certain event by viewing the table/query

What I want to do is that, if they enter a number larger than the maximum number of tickets assigned, they can't update the subform and have to lower to number. (E.g. team A can only enter at most "4" as the number of ticket request under "ABC event", there will be msgbox warming them if number is larger than 4)

How can I establish a validation rule between the text box value "Team Number" and field of a table "Team A/B & C" ??


*********************************]
I hope it is clear enough, could someone tell me how can I achieve the above effect?
It is difficult for me to set a validation rule between a text box "Team Number" and a field of a table.


i appreciate so much if someone can give me some advice

 

[dkinley]

I am not completely sure what you are after, so am going to throw a couple of examples to see if any of these satisfy.

Let's say you have your Event table and in it the fields ....

EventID, EventName, TeamName, MaxTickets, PurchasedTickets.

In a form they have to password into, either your form is bound to the Event table or a subform embedded on it with the event table information with the exception that the control txtMaxTickets is displayed or hidden (personal preference).

When the user attempts to exit or save the form you could place something along the lines of ...

If Me!txtPurchasedTickets > Me!txtMaxTickets Then
 
MsgBox "You are not authorized to purchase this many tickets." & vbCrLf & _
"You are only authorized " & Me!txtMaxTickets & " tickets." , vbCritical, "Purchase Ticket Error"
        Me!txtPurchasedTickets = vbNullString
        Me!txtPurchasedTickets.SetFocus
End If

Now, suppose MaxTickets was in the PlannedEvent table and PurchaseTickets was in CurrentEvent table and the tables were linked with PlannedEventID.

When the user attempts to exit or save the form you could place something along the lines of ...

Dim iMaxTickets As Integer
 
iMaxTickets = DLookUp("[MaxTickets]", "PlannedEvent", _
      "[PlannedEventID] = " & Me!txtPlannedEventID)
 
If Me!txtPurchasedTickets > iMaxTickets Then
 
MsgBox "You are not authorized to purchase this many tickets." & vbCrLf & _
"You are only authorized " & iMaxTickets  & " tickets." , vbCritical, "Purchase Ticket Error"
        Me!txtPurchasedTickets = vbNullString
        Me!txtPurchasedTickets.SetFocus
End If

-dK