Populate a COMBO BOX from a querie?

[tbpcpa]

PROGRAM: Fantasy Football Player Analysis

SCREEN FUNCTION: To add/remove players from a team roster within a specific league

SCREEN OBJECTS: Text box for League Code, Combo Box for players already on a team roster, Combo for players not on a team roster, (eventually buttons that will draft/undraft players back and forth to teams)

KNOWNS: I have queries already built in MS Access on databases that will pull the league roster and pull the players not yet drafted.

QUESTION: Is there a way to enter a numeric league code in a VB Text Box and use it as part of a querie that will populate a combo box. Eg. I would like to be able to key in a '1' in the League Code text box, and have the querie I've built pull all 'League Code = 1" rostered players into a ACTIVE ROSTER combo box, and at the same time pull all NON_ACTIVE ROSTER player in a second combo box... Eventually I would like to create buttons between the two combo boxes that would allow me to move selected players from one box to the other (drafted/released). Is this possible?

Any ideas of lead would be greatly appreciated!

 

[zemp]

To answer your first question, Yes. What you would need to do is to build your SQL statement after the league code entry has been made (...League Code=" & cint(text1) & "...). Perhaps a button build and execute the SQL statement and to fill the roster lists.

Initially my thoughts would be to place the leagues into a combo box and use its click event to build and execute the SQL statement. However this requires being able to keep track on the correct league code while displaying the correct league name. To do this i would use an array of a UDT (user defined data type) that holds both pieces of information in each array element. Then use the combo box listindex property to determine which array element to use to build the SQL statement. (...League Code=" & udtleauges(combo1.listindex).code & "...). This may sound confusing, I can explain further if you are interested in pursuing it.

As for the second question, yes it is possible. A couple of this to keep in mind.
1. You will need a data flag of some sort to determine if a player is active vs non-active. This can be a separate field or be determined by the data in an existing field. The choice is yours and may depend on how your database is set up.
2. As you change the status of a player from active to non-active, or vice-versa, you will need to update the right information for that player and then requery the database to fill each list again.

If you want to avaid trips that many trips to the database you will have to hold the necessary information in an array of players and then just change the array and fill the lista from the arrays. This however will use more memory because of the arays. Either way has its pros and cons. It's really up to you.

I hope that I haven't confused you too much. If you need clarification or would like more information just post again. Thanks and Good Luck!

zemp