Hi Group

I have a form with a subform on it. Is it possible to have a combo box that would limit subform to its selection ?

Yes, you would code the combobox's AFTER UPDATE event to change the subform's source object property using an SQL statement that varies based on your selection. For example:

Where the actual "Select ..." would be a REAL statement instead of these fake ones, or they would be names of valid tables or queries.




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hi SerranoG
doesn't work; the combo box now shows only distinct records that are in a column. (1,2,4)
I need to specify which column of the subform should equal that selection
i am trying

Private Sub cmbFrm1_AfterUpdate()
    Me.FormQuery1.SourceObject = "'Select * From FormQuery1 Where AppsID =' & cmbFrm1.text & '"'
End Sub

No, your select statement is not correct. You have

Me.FormQuery1.SourceObject = "'Select * From FormQuery1 Where AppsID =' & cmbFrm1.text & '"'

Me.FormQuery1 is the name of your subform, correct? How can you SELECT FROM FormQuery1? You cannot select from a form, you have to select from a table or query. What's the name of your table or query?

Me.FormQuery1.SourceObject = "Select * From {your table or query name here} Where AppsID = '" & Me.cmbFrm1 & "'"

Also, note the use of " and '. You have them backwards.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

No, still doesn't work; actually i decided to make it simpler. Instead of subform i put list.
so now i assume it should be something like ---

Private Sub cmbFrm1_AfterUpdate()
     lstForm1.Column(0) = cmbFrm1.text
End Sub

still doesn't work though :(

I'm sorry, but you're not making any sense to me.

lstForm1.Column(0) = cmbFrm1.text

That statement does not change a subform's source object to match a combobox (your original question). The above statement you wrote is the way to make the the first item of a list box = the current value of a combobox.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Serano,

I dicided to use list boxes instead of subforms, so i need the same, only for list boxes. That

Private Sub cmbFrm1_AfterUpdate()
     lstForm1.Column(0) = cmbFrm1
End Sub

doesn't work however
i get 'Object Required' error

actually, i don't seem to be needing all the extra quotes ;
lstForm1.RowSource = "Select * From FormQuery Where AppsID =" & cmbFrm1.Text

works fine