By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,741 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Trouble with form expressions

P: 3
I am building an event management database in Access 2003. I have a form called Player. This form has a command button which enters the currently selected player into the current days event (in the Attendance table). What I need is a control on the Player form that shows the player is registered in the current days event (this is to eliminate redundancy, as I don't want the same player registered into the event more than once). I would like this to have a green background if they are registered, and a red background if they are not. Any help would be greatly appreciated.
Feb 1 '08 #1
Share this Question
Share on Google+
5 Replies


JustJim
Expert 100+
P: 407
I am building an event management database in Access 2003. I have a form called Player. This form has a command button which enters the currently selected player into the current days event (in the Attendance table). What I need is a control on the Player form that shows the player is registered in the current days event (this is to eliminate redundancy, as I don't want the same player registered into the event more than once). I would like this to have a green background if they are registered, and a red background if they are not. Any help would be greatly appreciated.
Hi
This shouldn't be hard (especially changing the properties of a control (background colour) depending on the value of another control (registered or not)).

How are you recording whether or not the player is registered for a current day? I think that holds the key to your question.

Let us have more information and we'll see what we can do to help.

Jim
Feb 1 '08 #2

P: 3
Hi
This shouldn't be hard (especially changing the properties of a control (background colour) depending on the value of another control (registered or not)).

How are you recording whether or not the player is registered for a current day? I think that holds the key to your question.

Let us have more information and we'll see what we can do to help.

Jim
Re: Trouble with form expressions
--------------------------------------------------------------------------------


Jim

Unfortunately I am using a brute force macro to do it. I couldn't figure it out programmatically. There are 3 tables involved. The Player table contains all the player info. The Event table contains all the event info. The Attendance ties both those tables together, and contains 3 fields only (AttendanceID, PlayerID, EventID).

Once the appropriate player is on screen, clicking the macro button will open the attendance table, and insert the current player into the (last, by default) event. Not pretty, but it works.

What I need is a textbox, or some control, on the Player form that shows whether current player is registered for the (last) event. I imagine it needs to lookup somehow on the Attendance table. I also need it to change colors per my above post.

Let me know if you need more info
Feb 1 '08 #3

P: 3
Anyone have any ideas?
Feb 4 '08 #4

JustJim
Expert 100+
P: 407
Anyone have any ideas?
Sorry for the delay. No, I'm lost here. I'll re-read it all tomorrow and maybe there'll be a lightning strike of genius.... don't hold your breath though.

Jim
Feb 7 '08 #5

Minion
Expert 100+
P: 108
Wow my head kind of hurts now... but I think I'm getting the idea. If I understand correctly the Attendance table is a link table between the two other tables (Player and Event respectively). When you click your macro it adds the PlayerID and EventID to the table and the AttendanceID is probably generated (autonumber). Am I right?

If this is correct you then want to know if the player has been entered into the Attendance table with that days events. Well that shouldn't be too bad.

Ok... here goes the fun part. In lieu of waiting for a clarification I'm going to assume a few things (as follows) in the hopes that it gets you moving in the right directions.

List of assumptions and variables used:

1. Each day's event has a unique ID and that is what you're actually looking for to be attached to the player.

2. That I'm not totally crazy.

3. PlayerName is the control name for what ever you are trying to set the color for.

The code will look something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub checkAttendance()
  2.     'Look up the day's event in the event table. You'll have to provide the exp here
  3.     Dim daysEvent = dlookup("[EventID]","tblEvents",["EventID] = '" & <<expression to find event ID>> & "'")
  4.  
  5.     With Me
  6.         'Check to see if the player has been registered to the days event in the attendance table
  7.         If isnull(dlookup("[PlayerID]","tblAttendance","[EventID] = '" & daysEvent & "'")
  8.             'If they haven't them make it green
  9.            .PlayerName.BackColor = 65280
  10.         Else
  11.             .PlayerName.BackColor = 255
  12.         End If
  13.     End with
  14.  
  15. End Sub
  16.  
This is the rough code you'd be looking at as I understand your situation. You'll need to fill in a couple blanks and modify it for your naming scheme, but it should get the job done. If you have difficulties (which is not uncommon) let me know.

Hope this helps.

- Minion -
Feb 7 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.