Combo Boxes in a form

[millerlite2ky]

I have a database containing car race results. I'd like to make a form where I could use combo boxes (based on separate tables) to choose a Year, Track, Track Race Number and then have the results displayed in a subform. Is there anyone who could help me with this?

Thanks in advance........Scott

 

[SydneyGeek]

This is not difficult, but it takes a bit of setting up because there are a lot of steps. You will need the following:
1. Two tables: Events (fields are EventID, Year, Track and Race) and Results (Fields are ResultID, EventID, Position, Driver and other relevant fields). The tables are related via the EventID fields, with referential integrity enforced.
2. An unbound Search form. This contains three combo boxes and a textbox, all unbound. These controls will be used to filter data from the Events table. The combos are cmbYear, cmbTrack and cmbRace; the textbox is EventID
3. 2. A subform called Results Subform, based on the Results table.
Top of the cascade is cmbYear, which displays a list of years for which you have race information. Next is cmbTrack, which displays results filtered on cmbYear. Third is cmbRace, which shows races for a particular race meeting. This is filtered on both cmbYear and cmbTrack. Finally, EventID is a text box which displays the EventID field from cmbRace. The subform then displays results from this unique event.

OK, details for the frmSearch form. This is being presented in shorthand to save space on the reply, but all information is there.
Create a blank form. With the Control Wizard OFF, drag 3 combos and one text box onto the form. Save it as ResultSearch.
First Combo. Right-click, select Properties, click the All tab. Set these properties:
Name cmbYear
Row Source Type Table/Query
Row Source SELECT DISTINCT Events.Year FROM Events;
Column Count 1
Column Widths 3

Second Combo, still on the All tab:
Name cmbTrack
Row Source Type Table/Query
Row Source SELECT DISTINCT Events.Track FROM Events WHERE (((Events.Year)=[cmbYear]));
Column Count 2
Column Widths 3;0

Third Combo:
Name cmbRace
Row Source Type Table/Query
Row Source SELECT Events.EventID, Events.Race FROM Events WHERE (((Events.Track)=[cmbTrack]) AND ((Events.Year)=[cmbYear]));
Column Count 4
Column Widths 0;3;0;0

Now switch to the Events tab. Double-click in the After Update event line for each of the 3 combos. You will see [Event Procedure] in each of the lines. Click the builder (…) button on any one of them to be taken to the code for the form. Copy and paste until the form looks like this:

Option Compare Database
Option Explicit

Private Sub cmbRace_AfterUpdate()
[EventID] = [cmbRace].Column(0)
End Sub

Private Sub cmbTrack_AfterUpdate()
[cmbRace].Requery
[EventID] = ""
End Sub

Private Sub cmbYear_AfterUpdate()
[cmbTrack].Requery
[EventID] = ""
End Sub

Explanation:
The Requery commands cause the combos to refresh their contents, so the lists are updated based on the upstream data.
[EventID]=”” blanks the EventID textbox, removing display of records in the subform.
[EventID] = [cmbRace].Column(0) pushes the Event ID from cmbRace into the EventID textbox, so the subform can update and display the filtered data.

Save the form.

Now, the subform.
Create an Autoform based on the Results table. In Design view, change the Default View to Datasheet. Save the form as subResults, and close it.

Back to the main form. Open the search form in Design view. Restore so you can also see the database window. Drag the subform icon onto the main form and release it. You will be prompted for a name; take the default.
Now, click the subform ONCE and go to its properties. You should see Link Child Fields and Link Master Fields. Enter EventID into each line, and save the form.

View the data and give it a run. You should also be able to use the subform for data entry.

Denis

 

[millerlite2ky]

Hey thanks for the reply. It looks like alot, but i'll give it a shot. I wanted to ask one other thing: I have 3 tables (Year,Tracks,RaceNum) that have just 2 fields per table (Fields are ID & List). Can I use these tables in addition to my Race Results table and set up relationships?

Scott

 

[SydneyGeek]

Can't see why not, but you will have to change the SQL statements in the Combos to suit. Once you have pasted them in, hit the Builder button and you'll see the standard query window -- you may get a warning about Access not finding some of the tables. Click OK, add the required tables and felds, and save the query. You will also need to change the Column Number to 2, and set the Column Widths to 0;3
Leave Bound Column as 1.

There should be no need to change the event code.

See how you go, post back if you get stuck.

Denis

 

[millerlite2ky]

Everything works out great. That's exactly what I wanted the form to do! I didn't use the 3 other table, though, I just used the two separate ones like you suggested in the first reply.
What does the Bound Column & Column Width's have to do with anything?
Just Curious!!

Also, I was wanting to make up another form similar to the form you helped me with. I want to make a form where I can use a combo box to pick a driver's name and another one to pick a track, and see the driver's results at the track. I've tried using some of the info you gave me, but couldn't get it figured out!! I have tables called:

Tracks........Drivers..........Events.........Results.......Car Makes

The Tracks table has only 2 columns, as does the Drivers table. I can't figure out the enforce integrity to work right.

Basically, I'd like to just use my Results table as the source, and kind of AutoFilter by Driver & then by Track to get the results that I want, but it's not working out too well.............Would appreciate your help if you have the time & patience with me!!

Scott

 

[SydneyGeek]

Hi Scott,

What does the Bound Column & Column Width's have to do with anything? 
Just Curious!!

Bound Column is the field whose data gets stored. When you refer to the Combo in a filter, that field is the one that gets used.
Column Widths determines which field you actually view in the Combo. I usually only display one column in the drop-down, so I make the width of that column 3 and all others 0. (You could use 1 instead, if you measure in inches: being an Aussie, I use metric so 3 gives a wide enough column to view the data properly) :wink:
As for the rest of the questions, I'm working something out now. Will post back when I've worked out a usable solution.

Denis

 

[SydneyGeek]

Scott,

I rearranged some stuff and built a new form to enable driver/track filtering. It's easier to send you the example than to explain it. Send me a PM with your email address and I'll forward a copy.

Denis

 

[millerlite2ky]

Is there a way that I could add a text box, which would display the number of wins for a driver for a certain year?

 

[SydneyGeek]

Yep. A bit of work to set up, though.
1. Open the subform for the Drivers/Tracks form in Design view. In the FOOTER for that form, add a new Textbox. Call it Wins. The Control Source for this text box is =SUM(IIF([Position]=1,1,0)). Save and close.
2. Open the main Driver/Track form in Design view. Add a new Textbox. Call it DriverWins. The Control Source for this is =[WhateverTheSubFormIsCalled].Form![Wins]

Explanation: To summarise data in a form, you need a control on that form. Placing it in the footer (if it's a subform) means that it doesn't display. You can then reference that control from the main (or parent) form.

Denis

 

[millerlite2ky]

That works! I was trying to use Count(iif and it was giving me a total. Also, I wasn't putting a ! between Form and the textbox name.

Thanks again for your help!