Forms and Queries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George - 25 May 2007 08:24 GMT

Dear friends

I have a table tblParts. One field is called Category. I want to have a
form to select parts by selecting various categories. My problem is that I
have more or less 30 categories, e.g. A/C1, A/C2, A/C3, TOOLS1, TOOLS2 etc.
When I select either A/C1, A/C2 or A/C3 I need to select all parts within all
the three categories.

On the other hand I need to select more categories, e.g. I will select A/C2
and TOOLS1 (using combo boxes on my form). In this case all parts fall
within A/C1, A/C2, A/C3, TOOLS1 and TOOLS2 categories must be selected.

Any help will be highly appreciated.

Thanking you in advance

GeorgeCY

Reply to this Message

Klatuu - 25 May 2007 14:13 GMT

If you need to make multiple selections from your category list, you probably
need a multi select list box rather than a combo box. It takes a bit more
code, but will allow you to do this.
To determine which categories the user has selected, you will need to use
the ItemsSelected collection of the list box to find which are selected.
Here is an example from one of my applications. It builds a Where string that
can be used as the where part of a query. The way this works is that if a
user wants to select the entire list, they do not select any items.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Signature

Dave Hargis, Microsoft Access MVP

> Dear friends
>
[quoted text clipped - 13 lines]
>
> GeorgeCY

Reply to this Message

George - 26 May 2007 09:14 GMT

My friend, thanks a lot for your answer,

Sorry, but i got a bit confused. I am a newbie in coding. I have already
created on my form a list box (list1) which shows all different categories
(grouped) from a query. What shall I do next? I need to have multiple
selctions from this list and then to run a second query to match (from my
table) all related records.

Thanks again,

GeorgeCY

Ο χρήστης "Klatuu" έγγραψε:

> If you need to make multiple selections from your category list, you probably
> need a multi select list box rather than a combo box. It takes a bit more
[quoted text clipped - 51 lines]
> >
> > GeorgeCY

Reply to this Message

Klatuu - 26 May 2007 22:06 GMT

The code I posted will create a string that can be used as a Where clause for
a query, but without the word WHERE.

I would suggest a command button to run the query or queries you need. To
call the function, you pass it the control object.

strWhere = BuildWhere(Me.List1)

Now, how you construct the query can be done a number of ways. You could do
it all in VBA:

strSQL = "SELECT [SOME_FIELD], [ANOTHER_FIELD-], [FOOTBALL_FIELD] FROM
tblAnyOldTable WHERE " & strWhere & ";"

Or you could use a stored query that has no criteria and add the criteria in
code:

strSQL = CurrentDb.QueryDefs("BaseQuery").SQL
strSQL = Replace(strSQL, ";", " WHERE " & strWhere & ";")
CurrentDb.QuerDefs("ProductionQuery").SQL = strSQL

With this technique, you create BaseQuery that pulls the data, but you never
actually use it. It is just there to store the SQL for you. This technique
is good if there is more than one place in your app you may use the same
unfiltered query.

Then, you save a modified version as ProductionQuery which now has the
filtering criteria. This will be the query you actually execute.

To set this up initially, you write BaseQuery and save it then make a copy
called ProductionQuery. That way it is there for the code above to use.
(Please use better names, this is only an example.

As to the second query, since I don't know what you are trying to
accomplish, I can't give any advice on that. See if you can get this part
working and if you need more assistance, post back with what you have so far
and what you want to do next.

Signature

Dave Hargis, Microsoft Access MVP

> My friend, thanks a lot for your answer,
>
[quoted text clipped - 65 lines]
> > >
> > > GeorgeCY

Reply to this Message

George - 28 May 2007 10:07 GMT

Thanks a lot Dave,

Your help is highly appreciated. I have also found a sample database to do
what I need to do at

http://www.rogersaccesslibrary.com/download3.asp?SampleName=CreateMultiSelectQue
ry.mdb

Have a nice day!

GeorgeCY

Ο χρήστης "Klatuu" έγγραψε:

> The code I posted will create a string that can be used as a Where clause for
> a query, but without the word WHERE.
[quoted text clipped - 103 lines]
> > > >
> > > > GeorgeCY

Reply to this Message