Re: Populating a list

Hi Kass,

look here for ideas on other things you can do with your form:

news://msnews.microsoft.com:119/1149538886.072984.248900@xxxxxxxxxxxxxxxxxxxxxxxxxxxx

news://msnews.microsoft.com:119/uMnyDySiGHA.3884@xxxxxxxxxxxxxxxxxxxx

news://msnews.microsoft.com:119/Odvb5qYiGHA.3780@xxxxxxxxxxxxxxxxxxxx

for choosing multiple activities, consider using a multi-select listbox , then you can loop thru the listbox selected items and construct a WHERE clause for an SQL statement that you can write to show all kit parts

here is some code for constructing a the WHERE clause from a multi-select listbox for an SQL statement

'~~~~~~~~~~~~~~~~~~~~~
Dim varItem As Variant
Dim mWhere As String

mWhere = ""
For Each varItem In me.listbox_controlname.ItemsSelected
'delete the line that doesn't apply
'for text
mWhere = mWhere & "'" _
& me.listbox_controlname.ItemData(varItem) & "', "
'for numbers
mWhere = mWhere _
& me.listbox_controlname.ItemData(varItem) & ", "
Next varItem

if len(mWhere ) > 0 then
mWhere = "[Field_Name] IN (" & mWhere

'remove comma from the end and add a parenthesis
mWhere = left(mWhere ,len(mWhere )-2)) & ")"

end if

'then, if you have other criteria, you can put code here

'~~~~~~~~~~~~~~~~

you can use the same code to construct a string to use for the WHERE parameter of an OpenReport action

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kass wrote:
I need to be able to populate a list using choices from another list for a science kit database I have.

I have one form called "Activities" with a subform call Kit Parts. I choose from a list of Activities, and then populate the subform with all the Kit Parts needed to perform that Activity. I have many Activities and their corresponding Kit Parts housed in a Kit Detail Table. This all works just fine!

Now what I need, is to be able to create a form and a corresponding report where I can choose and add several different Activities into a subform that together make a Kit. The form and its corresponding report will show a comprehensive list of all the Kit Parts needed for all those Activities that make up the Kit.

I know how to make a form where you choose from a list the Activity and it shows the Kit Parts needed for that one Activity. But, I don't seem to be able to take this to the "3D level" so to speak, where I choose MULTIPLE Activities and the form (and eventually a report) will automatically show ALL the Kit Parts needed for those chosen Activities.

Can anyone help me think through this barrier?

Thanks for your help!

Kass
.

Relevant Pages

  • Re: Populating a list -- SQL basics
    ... Server: msnews.microsoft.com:119 ... Here is a link on SQL basics. ... Dim mWhere As String ... science kit database I have. ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- SQL basics
    ... for choosing multiple activities, consider using a multi-select listbox, then you can loop thru the listbox selected items and construct a WHERE clause for an SQL statement that you can write to show all kit parts ... Dim mWhere As String ... science kit database I have. ... is to be able to create a form and a corresponding report where I can choose and add several different Activities into a subform that together make a Kit. ...
    (microsoft.public.access.forms)
  • Re: Populating a list
    ... Dim mWhere As String ... science kit database I have. ... I have one form called "Activities" with a subform call Kit Parts. ... is to be able to create a form and a corresponding report ...
    (microsoft.public.access.forms)