Iv found some code that allowes user to select from a option group then
results appear in list box. But am unsure what this code should be
attached to/where to put it..
Also i understand most of it but I am confused as to what

[By Search Type].RowSource = "SELECT................"

does this ref the query you are working from looks like SQL to me??

code is:

Private Sub Type_of_Search_AfterUpdate()

If [Type of Search].Value = 1 Then
[By Search Type] .RowSource = ""
[Search Text].Caption = "Select the School Name to Search For"
[By Search Type].ColumnCount = 3
[By Search Type].ColumnWidth = "2 in;1 in;0 in"
[By Search Type].BoundColumn = 3
[By Search Type].RowSource = "SELECT................"
ElseIf [Type of Search].Value = 2 Then
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Cost Centre to Search For"
[By Search Type].ColumnCount = 2
[By Search Type].ColumnWidth = "1 in;2 in"
[By Search Type].BoundColumn = 1
[By Search Type].RowSource = "SELECT............"
End If

End Sub

Any1 got any idea????



Wayne Morgan

The code is adjusting the properties of the listbox or combo box named [By
Search Type] depending on the option selected in the option group named
[Type of Search]. The SQL statement is being assigned to the RowSource of
[By Search Type] to change the items in the list or drop down to match the
selection that was made in [Type of Search]. When the RowSource of a combo
box or listbox is changed, the control will automatically Requery to get the
list of data that matches the new RowSource.


ok get yah...!!!
What i really need is not to be doing this. I want a use to enter a
School Name and/or Month
and the results to be brought up in a list box, How do i do this?



Wayne Morgan

I would probably create 2 combo boxes. One would have a list of months to
pick from, the other would have a list of available schools to pick from.
Actually, one combo box with an option group selection of School or Month
would work also. In the AfterUpdate event of the combo box (i.e. once the
use has made their selection), you would send the correct SQL to the
listbox's RowSource for the selection made in the combo box.

To do this, you would start off in the AfterUpdate event of the option group
(School or Month) and send the following to the combo box:

Dim strRowSource As String
If Me.optOptionGroup = 1 Then
strRowSource = "SELECT SchoolName FROM Table1 ORDER BY SchoolName;"
Me.cboMyCombo.RowSourceType = "Table/Query"
Me.cboMyCombo.RowSource = strRowSource
Me.cboMyCombo.RowSourceType = "Value List"
Me.cboMyCombo.RowSource =
End If

Then in the AfterUpdate of the combo box, adjust the RowSource of the

Dim strSQL As String
If Me.optOptionGroup = 1 Then
strSQL = "SELECT * FROM Table2 WHERE SchoolName = """ & Me.cboMyCombo &
Me.lstMyListbox.RowSource = strSQL
strSQL = SELECT * FROM Table2 WHERE MonthName = """ & Me.cboMyCombo &
Me.lstMyListbox.RowSource = strSQL
End If

Of course, you'll have to adjust the table and field names to match your
database. Also, if you have a School ID field, the combo box probably needs
2 columns when School is selected, one for the ID field and one for the
SchoolName field. The ID field would be hidden from the user, but you would
use this when creating strSQL instead of using the name.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VB syntax....' " " ' " ??? 3
VB Question 6
VB Code 2
VB Critique 1
VB code 1
VB PrperCase 1
VB, DMax with 'And' 3
VB Coding 1