Filter One Combobox based on selection in another combobox (1 Viewer)

rachelkm2

Registered User.
Local time
Today, 05:11
Joined
May 29, 2009
Messages
24
I have two comboboxes on a form (cbo_Start and cbo_End).

The record source of both is currently set to the same query: qry_StartEnd.

qry_StartEnd contains the following fields:

StartEnd_Name (Text, example - "08/2010")
StartEnd_Start (Date, example - "08/01/2010")
StartEnd_End (Date, example - "08/31/2010")

The bound column of each combo box is "StartEnd_Name".

I would like to be able to change the values of the second combo box (cbo_End) based on the value selected in the first combo box (cbo_Start) so that the user is forced to select an end month greater than the value selected in the first combo box.

Any ideas?

Thanks!
 

rachelkm2

Registered User.
Local time
Today, 05:11
Joined
May 29, 2009
Messages
24
Thanks for the tip! I feel like I'm heading in the right direction, but there's something wrong - maybe you can help identify where I've gone wrong?


Code:
Private Sub cbo_Start_AfterUpdate()

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection 'set the active connection
Dim rstStart As New ADODB.Recordset
rstStart.ActiveConnection = cnn1

Dim sqlStart As String
Dim selectedStart As Date

sqlStart = "SELECT * FROM qry_StartEnd WHERE StartEnd_Name = '" & Me.cbo_Start.Value & "'"
rstStart.Open sqlStart

    With rstStart
        .MoveFirst
        selectedStart = rstStart.Fields("StartEnd_Start").Value
    End With
    
    cbo_End.RowSource = "SELECT * FROM qry_StartEnd WHERE StartEnd_Start > #" & CStr(selectedStart) & "#"

rstStart.Close
Set resStart = Nothing
cnn1.Close
Set cnn1 = Nothing

End Sub
 
Last edited:

rachelkm2

Registered User.
Local time
Today, 05:11
Joined
May 29, 2009
Messages
24
Figured it out!

Just in case someone else stumbles across this and needs the answer...

I was just missing:

cbo_End.RowSourceType = "Table/Query"
 

smig

Registered User.
Local time
Today, 12:11
Joined
Nov 25, 2009
Messages
2,209
just a side note:
it won't work nicely on continous form as cbo_End.RowSource = ... will change all instences of cbo_End on the form
 

Users who are viewing this thread

Top Bottom