?
Solved

Subform values/datasheet based on a listbox(multiple selection) on main form

Posted on 2006-05-18
31
Medium Priority
?
792 Views
Last Modified: 2008-02-01
Hi,

It seems this should be easy but I can't seem to do it...

I have a main form that is not based on a table.  On it I have a list box to select multiple categories.  Source for that is a select distinct categories based on a combo box.  That list comes from a table called Expanded.  subform is based on table Expanded.  All I want to do is have the subform display the data where user selected from the list box (list box is defined as multiple).  I wrote code attached to a button that gets the ItemsSelected in the listbox and creates a where clause.  I don't want to DoCmd.OpenForm (djfskldfjsl, whereclause) because it opens another window with that form in it.

I want the subform to display the data.  These forms are not linked because parent isn't based on a table.  It's just a "selection" form.

Any ideas?

Thanks
0
Comment
Question by:kissybean12
  • 8
  • 8
  • 8
  • +2
30 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16713973
Hello kissybean12

Perhaps something like this:

    strCriteria = <code to generate the where clause without WHERE>
    With subFormExpanded.Form
        .Filter = strCriteria
        .FilterOn = True
    End With

Or did I miss something? You do have the code to transform the listbox selection into something like "SomeField In('aaa','bbb','zzz')", right?

Cheers!
(°v°)
0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16714934
I do this all the time be resetting the subforms recordsource.  Depending on your database and environment, this is not always a good idea because it doesn't "keep the wire cool" as some guys say.  Here's how i do it.

Private Sub cmdUpdateView()
Me!Subform1.Form.RecordSource = "Select * from tblWhatever Where "
End Sub
0
 
LVL 3

Expert Comment

by:RohitPattni
ID: 16716363
Hi

Have a look the the properties of the sub form. In there set the Master property to the name of your list box and the the child propertiy to the name of the equivalent field in the sub form. Then every time the value in the list box changes the sub form data will change accordingly.

Rohit
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 4

Expert Comment

by:HKComputer
ID: 16717386
Rohit,

I think that will not work because it doesn't take the possibility of multiple selections into consideration.  Right or wrong?
0
 
LVL 3

Expert Comment

by:RohitPattni
ID: 16717462
to get mutliple fields selctions say we have listboxA to Field1 and listboxB to field 2 then the Master would be ListboxA;listboxB and then the child wold be Field1;Field2.
You basically can have multiple selection each seperated by a semicolon. The sequence for the master field must be the same as the child fields.

Rohit
0
 
LVL 58

Expert Comment

by:harfang
ID: 16717557
Multi-select listboxes cannot be used as Link Master Fields, because they do not have a value.
(°v°)
0
 
LVL 3

Expert Comment

by:RohitPattni
ID: 16717759
It does work with that. I just tried it ?? For two list boxes, I set the values in the RowSource and the sub form worked according to the values selected in the list box. Why do you think that this would not work?

Rohit
0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16717844
I'm not getting it.  I think the original post was using one multi-select listbox (as master but not actually configured that way) and one subform as a child.  I believe harfang to be correct:

>Multi-select listboxes cannot be used as Link Master Fields, because they do not have a value.
0
 

Author Comment

by:kissybean12
ID: 16718122
Thanks ...  the code to get the values is attached to a button (on click)... which is a private function.... maybe I should make it public and assign the whereclause to a text box on the main form so that I can use it in my query?

Query would look like:

select *
from table
where [var]

this is really getting to me... how hard can this be??? I see app's do this all the time... select something or a list and display results.... I'm still stumped!

0
 
LVL 3

Expert Comment

by:RohitPattni
ID: 16718138
Hi HKComputer

You and Harfang are right. I had not set the multiple section on the list boxes when I tested them. I was also testing using two seperate list boxes ie multiple boxes  (not the same as Mutilselect list boxes)

You are right that the Listbox will not return any value so cannot be used as suggested. The only way to get the values from a multiple selection list box is to use the Listindex property from VB and then you would have to relate that back to a list of values and have the relate back to the original listing. But then how to get the subform to update ?

Sorry for the wrong info. Guess I should read the info more carefully and be more careful setting up the testing. :-(
Rohit
0
 

Author Comment

by:kissybean12
ID: 16718233
Thanks ....

Here is the code on the "Search" button:

Private Sub Command60_Click()
On Error GoTo Err_Command60_Click

' Build where clause based on selected values

Dim strWhere As String, varItem As Variant

    ' If no items selected, then pop up message
        If Me!List55.ItemsSelected.Count = 0 Then
           MsgBox "You need to select Level IV Categories", vbOKOnly, "No Categories Selected"
           
           
        Else
           ' Get all the choices user selected
           For Each varItem In Me!List55.ItemsSelected
                strWhere = strWhere & Chr$(34) & _
                Me!List55.Column(0, varItem) & _
                Chr$(34) & ","
           Next varItem
           
           'Get rid of last comma
           strWhere = Left$(strWhere, Len(strWhere) - 1)
           
           'Open Dataset Preview Form
           
           gstrWhereLevelIV = "[Level IV Category] IN (" & strWhere & ")"
           
           'Open Form using the where clause
           
         
           
           
           'Me![Dataset Preview].SetFocus = True
           
            'DoCmd.OpenQuery [Build Fee - Preview]
       
           [Build Fee - Preview].Query.Filter = gstrWhereLevelIV

           '[Dataset Preview].Form.FilterOn = True
           [Dataset Preview].Form.Filter = gstrWhereLevelIV    
           'Me![Dataset Preview].Visible = True
       
        End If
       
       DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70



So I guess I need to create a text box on main form (Me!) and assign it to the gstrWhereLevelIV.  Then in the query (Build Fee - Preview) use that as the Where.... going to try it now....

0
 
LVL 58

Expert Comment

by:harfang
ID: 16718305
kissybean12

A where clause in a textbox is useless, because you cannot integrate it back into a query. However, you have promissing things here:

    Me.[Dataset Preview].Form.Filter = gstrWhereLEvelIV
    Me.[Dataset Preview].Form.FilterOn = True

This uses the where clause as a filter on the subform displaed in [Dataset Preview].

Also, you can build a new SQL sentence with:

    strSQL = "Select * From TheTable Where " & gstrWhereLevelIV

And then, depending:

    CurrentDb.QueryDefs("qselSomeQuery").SQL = strSQL
    Me.[Dataset Preview].Form.RecordSource = strSQL

etc...

(°v°)
0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16718330
Try it out with something simple first.  Create a combo box, and bind it to your query.  Example row source for the combo:

"Select AcctNo, FirstName, Lastname FROM tblContacts"

Then, make sure you configure the combo like this:

Column count 3
Column widths 0";1.5";1.5"  (this makes the first row hidden, usually you put in the primary key for first row)
Bound Column 1

Now in the combo's after update event, use the code i showed you before:

Private Sub cboSearch_AfterUpdate()
Me!Subform1.Form.RecordSource = "Select * from tblContacts Where AcctNo = " & Me.cboSearch & ""
End Sub

A few notes:
You can reference a specific combo column: " & Me.cboSearch.Column(1) & "
If the field type your are referencing in your listbox or combo is text you will need to use apostrophies:
"Select * from tblContacts Where AcctNo = '" & Me.cboSearch & "'"
"Select * from tblContacts Where AcctNo = '" & Me.lstContacts & "'"

The apostrophie comes first and then the quote.  You can also use a variable " & intAcctNo & "

Once you get this figured out you will need to enlarge on the idea to use a multi selection listbox.  You'll need to run a loop to figure out which fields are in the listbox and build your SQL statement from that.

0
 

Author Comment

by:kissybean12
ID: 16718567
Thanks Harfang.... In the above code I already tried that... ( Me.[Dataset Preview].Form.Filter = gstrWhereLEvelIV
    Me.[Dataset Preview].Form.FilterOn = True)

It doesn't work.... message is "You entered an expression that has an invalid reference to the property Filter".

?????  I'm losing my mind right now... can't think of any other way.....
0
 
LVL 58

Expert Comment

by:harfang
ID: 16718835
kissybean12

That is strange. Is it a form or a query displayed as datasheet? What is the Source Object property of [Dataset Preview]? Can you filter the subform useing filter by form, filter by example? If  yes, can you see the filter using:

    ? Forms!<your form name>![Dataset Preview].Form.Filter

From the immediate pane?

(°v°)
0
 

Author Comment

by:kissybean12
ID: 16718888
Hi... I just tried the other method of setting strSQL and then using that as record source

gstrWhereLevelIV = "[Level IV Category] IN (" & strWhere & ")"
           strSQL = "Select * From [Expanded - XRef] Where " & gstrWhereLevelIV
           Me.[Dataset Preview].Form.RecordSource = strSQL

I get the message "You entered an expression that has an invalid reference to the property RecordSource".

This code is on a button control.  The main form has form Dataset Preview as the subform and currently I have no recordsource defined because that's what I'm doing in this code above right?  

0
 
LVL 3

Expert Comment

by:RohitPattni
ID: 16719020
Hi,

Harfang maybe you can put  this in the correct code. I was playing with the multiselect list box and for each time I click on a list item i could retrieve the value in another text box using the code =Forms!form1.list0.itemdata(Forms!form1.list0.ListIndex) where the form is Form1 and the listbox is listbox0.

What i think you need is to build the WHERE on each update of the list box ie where$ =where$&" OR "  &Forms!form1.list0.itemdata(Forms!form1.list0.ListIndex). I can see this working whiel you select additonal lines but not when you deselect items.

I think that this shoudl run in 3 parts. 1 clear the WHERE string.  2. select line items in the list box adding to the existing WHERE$ based on when the list box is updated. 3 Final run the whole SELECT statement.

Sorry I cannot express this in VB as my VB is very basic, but I hope that it gives you some ideas as to how this can be solved. I really want to know too.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16719065
Hmmm...

Open your form in design view, select the subform (twice) so that you see the RecordSource property. As recordsource, use: Select * From [Expanded - XRef] Where False

Or even without any where clause. Does that work? While in break mode – using [Debug] when the error occurs, can you see it:

    ? Me.[Dataset Preview].Form.RecordSource

Is [Dataset Preview] really the name of the subform control?

It's frustrating. By looking over your shoulder, this would work in a couple of minutes!

Good luck!
(°v°)
0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16719067
Your SQL statement doesn't look right to me.  I would rename the fields to names without spaces for starters.  And try giving yourself a message box so you can analyze the SQL statement before using it.

gstrWhereLevelIV = "[Level IV Category] IN (" & strWhere & ")"
           strSQL = "Select * From [Expanded - XRef] Where " & gstrWhereLevelIV
           Msgbox strSQL
           Me.[Dataset Preview].Form.RecordSource = strSQL

A final sql statement should really read something like this:

"SELECT * FROM [tblEpandedXRef] Where ([LevelIVCat] = 1 or [LevelIVCat] = 5 or [LevelIVCat] = 7)"

I don't think you should use the IN statement the way you are.

And this won't work either:

"SELECT * FROM [tblEpandedXRef] Where ([LevelIVCat] = 1 5 7)"

Practice by setting the record source on your subform with an SQL statement that you think should work.  Work until you get one that does work.  Start without any variables and once you find the statement that works, start using variables that create a statement that looks the same as your working statement.

0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16719104
Yeah, reference your subform this way:

Me!Subform1.Form.RecordSource

And name it something that follows the standard naming conventions.  And don't use spaces.  I would use:

fsubResults
0
 

Author Comment

by:kissybean12
ID: 16719183
yes... Dataset Preview is the name of the (sub)form.... I set the RecordSource to select * from Expanded-XRef

then in the button code I have:

           gstrWhereLevelIV = "[Level IV Category] IN (" & strWhere & ")"
           strSQL = "Select * From [Expanded - XRef] Where " & gstrWhereLevelIV
           MsgBox strSQL
           Me.[Dataset Preview].Form.RecordSource = strSQL
           Me.[Dataset Preview].Visible = True

BTW... message box call produces correct results "select * from ... whereclause"  
Next message is The expression you entered refers to an object that is closed or doesn't exist.... ?????????

Dataset Preview exists... is the syntax wrong???

I'm going crazy over here!

0
 
LVL 58

Expert Comment

by:harfang
ID: 16719406
Hello,

Let's get a working sample. This might make it all easier to follow:
http://www.harfang.ch/expert/Q_21855892.zip

Cheers!
(°v°)
0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16719633
You're going wrong with the in statement.  Without using any variables, here is how you use an in statement:

"SELECT * from tblCustomers Where AcctCode IN (SELECT AcctCode FROM tblCustomers Where CustName = 'Jerry')"

And IN statement must always be followed by a full query, whether it be a saved query or an SQL statement.  The above statement is very useless because you coud achieve the same results with:

"SELECT * from tblCustomers Where CustName = 'Jerry'"

I used it for demonstration purposes only.  Please note that the query in parenthesis will run first and generate a recordset that will be used to narrow the recordset results in the main query.  Using these IN statements has been a little complex for me and for a newbie, I don't recommend using them unless they really are the only way to get what you want.
0
 

Author Comment

by:kissybean12
ID: 16719819
Harfang... I'm sorry but my office doesn't allow me to download anything off the internet....

I'm going to try a few more ideas but honestly I think this should be an easy thing.... not sure why I'm having so much trouble.

0
 
LVL 58

Expert Comment

by:harfang
ID: 16719857
Send me a mail (see my profile, it's a .ch address) and I'll send you the zip back...
(°v°)
0
 

Author Comment

by:kissybean12
ID: 16719897
I got it !!!!!  I needed to set the control source on the sub(form) to Dataset Preview.... now it works....

I can't beleive it took me this long!  Thanks to all of you for all your help!  I really appreciate it!
0
 
LVL 58

Expert Comment

by:harfang
ID: 16720060
Wow. I'm glad you sorted that one out! ;)
Good luck now!
(°v°)
0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16720145
I'm glad you figured it also.  But I'm not quite understanding what fixed it.  The way I read your previous posts, you are saying "I set the control source on the subform to Dataset Preview (which is the name of my subform)."  

And it works?
0
 

Author Comment

by:kissybean12
ID: 16720302
yes... I don't understand either... all this time if I had that set it would have been fine... since it was an embedded subform on main form I wasn't opening it up in design mode.. only main form in design mode, that's why I missed it!!
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16974788
PAQed with points refunded (125)

GranMod
Community Support Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question