Need help to run this logic in VBA from Access On Click event

[kee2ka4]

Hey Guys,

I a newbie in VBA code and need some help. I have an access database and have the following structure:

I have two tables called player and sports. Also the table sport has a field name sportType, and every sports name belongs to a type 1, 2, or 3. So for example, Football has type 3, Tennis has type 2 etc etc..

I created a form which displays sports name from the query in a dropdown list with a submit button. Now if you select a sport name from a Dropdown and press submit, I want the onclick event to run the following logic in VBA with maybe a switch statment:


Case 1:
If the selected sport name has type 1, then open Reports called reportOne and display all the players with that type in reportOne
Case 2:
If the selected sport name has type 2, then open Reports called reportTwo and display all the players with that type in reportTwo
Case 3:
If the selected sport name has type 3, then open Reports called reportThree and display all the players with that type in reportThree
End

Is this possible in access with a vba script and what would be the correct syntax. I have attached a sample access file with sample data with the tables, query and the three reports to give a better idea on what I mean?

Thanks for any help and guidance
Ket

 

[jzwp22]

If the report will look the same except for the actual names of the players (as it appears in your example), then you only need 1 report not three. You would use code to filter the names being supplied to report. Is that what you are after?

Also, regarding your table structure, you relate the person to the sport type rather than the actual sport they are playing. Was that your intent?

 

[kee2ka4]

Hi, thanks for your email. Yea actually all the three reports is gonna like very different hence I will need three different reports and also I need to relate the person to the sport type rather then the name. I gave the above example to simplify things

 

[jzwp22]

With the proper table structure, you can relate the name of the person with the sport as well as the sport type. I have modified your table structure accordingly and provided the code example to select the appropriate report; see the attached DB. Hopefully it helps.

 

[kee2ka4]

Thanks for your help man..

 

[jzwp22]

You're welcome; good luck with your project.