Connecting two combo boxes

[JoeyB]

Airport Database 0.5 1311121.zip
I am struggling to connect two combo boxes to each other. I got 103 airports and data over 28 year. When I select an airport i want the cboYear to only display the years that are connected to this airport. What it does now is keep jumping back to the first airport and I have no idea way.

The combo box for the Airport is called cboAirport. The combo box for the Year is called cboYear.

The data for the form is derived from the query "QueryTest", and the data for Airport comes from the table AirportT, and same goes for Year->YearT, Aircraftmovement-> AircraftMovementT etc.
My last question on this forum was about connecting two subforms with each other, but after spending hours I came to the idea I might as well just put the AircraftMovement and Passengernumbers in 1 Query. The information that is being displayed works just fine, now I have to make it user friendly.

Images:



Thanks for your help, much appreciated!

[ItsMe]

You have Me.cboAirport.RowSource in the cboAirport afterupdate

After cboAirport is updated the only thing that is really happening is you are causing cboYear to equal null and the rowsource of cboAirport to equal your SQL string.

Maybe Me.cboYear.Rowsource should be in the afterupdate.
Also, you could add a Me.cboYear.requery for good measure. Don't think it is absolutely needed though when assigning a rowsource.

[JoeyB]

You have Me.cboAirport.RowSource in the cboAirport afterupdate

After cboAirport is updated the only thing that is really happening is you are causing cboYear to equal null and the rowsource of cboAirport to equal your SQL string.

Maybe Me.cboYear.Rowsource should be in the afterupdate.
Also, you could add a Me.cboYear.requery for good measure. Don't think it is absolutely needed though when assigning a rowsource.

I have attached the database in the post(I first couldn't get it attached, but apparently you have to Zip)

[ItsMe]

Did you try to adjust your afterupdate code?

[JoeyB]

I have attached the database in the post(I first couldn't get it attached, but apparently you have to Zip)

Option Compare Database
Option Explicit
Private Sub cboAirport_AfterUpdate()

' Set the Year combo box to be limited by the selected Airport
Me.cboYear.RowSource = "SELECT YearT.YearID, YearT.Year FROM QueryTest " & _
" WHERE AirportID = " & Nz(Me.cboAirport) & _
" ORDER BY Year"

Me.cboYear.Requery

EnableControls

End Sub

Sorry I did not yet removed the =NULL, after removing this code it still wont work...

[ItsMe]

Couple of things. First, you had an embedded macro in the afterupdate event for the combo that needed to be cleared.
I added AirportID to your cboAirports RowSource.
Changed the collumn count to include two columns.
I adjusted the column width to hide the ID columns and show the other field for bothe combos.
Adjusted the bound column property for hte combos.
Changed "allow value list edits" to no. Changed "limit to list" to yes.

Then I adjusted your VBA for the afterupdate event.

If Not IsNull(Me.cboAirport) Then
Me.cboYear.RowSource = "SELECT QueryTest.YearID, QueryTest.Year" & _
                       " FROM QueryTest" & _
                       " WHERE AirportID = " & Me.cboAirport & _
                       " ORDER BY Year"
  Me.cboYear = Null
  Me.cboYear.Requery
End If

Now you need to adjust your form's recordsource or filter to show the value of your year combobox.

Me.cboYear.Column(0) has the value of the ID
Me.cboYear.Column(1) has the literal test value of the year

Also, recommend not using periods in file names. I would avoid spaces too.

[JoeyB]

Thank you very much, but why do I need to change the form's recordsource or filter?

[ItsMe]

You do not have to change or update the form's properties. However, if you want the form's recordset to reflect the user's input via the comboboxes you will need to change the filter or recordsource to reflect the values of both comboboxes. Perhaps a button labeled submit could handle a click event.

[JoeyB]

You do not have to change or update the form's properties. However, if you want the form's recordset to reflect the user's input via the comboboxes you will need to change the filter or recordsource to reflect the values of both comboboxes. Perhaps a button labeled submit could handle a click event.

I really appreciate your input mate, I have spend so much time in this and just cant figure it out. If you can help with where I have to do what I will be forever grateful...

[JoeyB]

Does anyone has suggestions for me? I have been looking for hours/days for a solution and just cant figure it out...

Thank you

[ItsMe]

I added an event handler to your cboYear. Ir assigns an SQL string to the form's recordsource. The SQL string is based off of the query that the form orignaly used. I cleared te Record Source propert for the form in the properties window. THe VBA will handle that now.

The SQL strin also has a WHERE clause to include the values of your two combo boxes.

Check the data and make sure it jives. I only tested for errors. Oh, I added a box too. You will want to resize it and make it look pretty. I placed it there in a conspicuous way so you can adjust it and see if that is the effect you are after.

I think that is all I changed

[JoeyB]

Thanks mate, I finally got something to work on again, I have been stuck on this for to long hahaha.