Open/Create a report based on form list box with multi select data.

[dstone10]

I've searched for a while today and haven't found anything that made sense to me so i'm back with, what seems to be, a difficult request.

To keep things simple, just so I could figure out how to do this, I created a new database with 1 table. That table has 3 columns, the default ID column, a column titled "user" and one more titled "skill".

I put various names in the user column and 3 different skill levels in the skill column like what you see below:

UserA Novice
UserB Expert
UserC Intermediate
UserD Expert
UserE Novice

From here I am lost as to how to make a form take the data they are selecting to pass those values into a query for a report.

I can make a query ask for the values and get it to work that way but I want the form to determine the values, not the user typing in the information from the query popup. Also, I want them to be able to select more than one value because I have the form set up to use list boxes. If they want to see all users who are in the expert and novice categories then selecting both should display a report of all users and their respective skill levels.

The list box row sources show the correct data (because I used a query in the row source) and multi select works.

Getting that select data to pass through when clicking the "Submit" button is where i'm hitting a wall. :banghead:

I've read other similar posts but there seems to be several steps left out for a beginner like myself to understand. Those steps, for a more advanced user, are probably just implied. I guess what i'm saying is if you don't mind...spell it out as much as possible. Thanks in advance for your help.

Using Access 2010

 

[dstone10]

Found an extremely useful post that led me to this site:

http://www.baldyweb.com/multiselect.htm

Sorry for the extra thread on an issue that was resolved before hand. Searched for about a half an hour and for some reason couldn't find that thread.

Maybe if I worded my search properly, instead of creating/opening a report to "filtering" I would have found it.

This site has helped me so much...Love this forum!

I got it working with 1 list box, the skill box...but what if I want multiple list boxes?

Say, I want the report to open based on UserA and UserB and I want to see all users in skill level of "Intermediate"?

Thanks in advance!

 

[nanscombe]

This function should take a Listbox as an argument and convert the selected items into a comma separated string.

TheDelimiter is what, if anything, should be wrapped around the data. Text ("), dates (#), numbers (nothing).

Public Function listBoxToString(ByRef theListBox as Listbox, optional byVal theDelimiter as String = vbNullString)
  dim varTemp as Variant

  listBoxToString = vbNullString

  For Each varTemp in theListBox.itemsSelected
    If listBoxToString <> vbNullString then listBoxToString = listBoxToString & ","
    listBoxToString = listBoxToString & theDelimiter & theListBox.itemData(varTemp) & theDelimiter
  Next varTemp
End Function

The way I would use this would be to create a Where Clause to pass to the report.

Private Sub myButton_Click()
  Dim strWhere as String

  strWhere = vbNullString

'  strWhere = listBoxToString(me.myListBox) ' For numbers no delimiter required 
  strWhere = listBoxToString(me.myListBox, chr(34)) ' For Text, Chr(34) is " 
'  strWhere = listBoxToString(me.myListBox, "#)") ' For dates 

  If strWhere <> vbNullString then strWhere = "yourFieldName In(" & strWhere & ")"

  DoCMd.OpenReport "yourReportName", acViewPreview, , strWhere

End Sub

Found an extremely useful post that led me to this site:

http://www.baldyweb.com/multiselect.htm

Sort of SNAP!!

By doing it as a function you could re-use if elsewhere with other Listboxes.

 

[dstone10]

By doing it as a function you could re-use if elsewhere with other Listboxes.

Your way worked perfectly as well, I am not familiar at all with functions so all of my code so far has been "per event" if you will. I know, not very good coding practice but I am a database guy trying to delve into the world of development lol.

The part I am not 100% certain on is how this can/will apply to my other list box on the same form.

One list box (that I currently have working with your method) is called "skill" and the other list box i'd like to be included is called "user".

Thanks for your help so far!

 

[nanscombe]

... I got it working with 1 list box, the skill box...but what if I want multiple list boxes?

Say, I want the report to open based on UserA and UserB and I want to see all users in skill level of "Intermediate"?

Thanks in advance!

Private Sub myButton_Click()
  Dim strWhere as String, strTemp as String

  strWhere = vbNullString

  If Len(Me.UserA & vbNullString) > 0 Then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[UserA] = '" & Me.UserA & "'"
  End If

  If Len(Me.UserB & vbNullString) > 0 Then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[UserB] = '" & Me.UserB & "'"
  End If

  strTemp = listBoxToString(me.[skill level], chr(34)) ' For Text, Chr(34) is " 
  If strTemp <> vbNullString then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[skill level] In(" & strTemp & ")"
  End If

  strTemp = listBoxToString(me.listBoxWithNumbers) ' No Delimiter required 
  If strTemp <> vbNullString then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[aNumberField] In(" & strTemp & ")"
  End If

  strTemp = listBoxToString(me.listBoxWithDates, "#") ' Delimiter for dates 
  If strTemp <> vbNullString then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[aDateField] In(" & strTemp & ")"
  End If

  DoCMd.OpenReport "yourReportName", acViewPreview, , strWhere

End Sub

 

[nanscombe]

ListBoxes called skill and User?

Private Sub myButton_Click()
  Dim strWhere as String, strTemp as String

  strWhere = vbNullString

  strTemp = listBoxToString(me.[skill], chr(34)) ' For Text, Chr(34) is " 
  If strTemp <> vbNullString then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[skill] In(" & strTemp & ")"
  End If

  strTemp = listBoxToString(me.[User], chr(34)) ' For Text, Chr(34) is " 
  If strTemp <> vbNullString then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[User] In(" & strTemp & ")"
  End If

  DoCmd.OpenReport "yourReportName", acViewPreview, , strWhere

End Sub

By putting the function listBoxToString() in the form code you can use it anywhere on that form. If you insert a Module and put it in there it can be used from anywhere in your database.

A module is a collection of code, rather like that behind a form, which can be used anywhere as long as it is declared as Public Sub (a subroutine which doesn't pass back any values) or Public Function (A function which can pass back a value).

 

[dstone10]

OK that worked

  Dim strWhere as String, strTemp as String

  strWhere = vbNullString

  strTemp = listBoxToString(me.[skill], chr(34)) ' For Text, Chr(34) is " 
  If strTemp <> vbNullString then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[skill] In(" & strTemp & ")"
  End If

  strTemp = listBoxToString(me.[User], chr(34)) ' For Text, Chr(34) is " 
  If strTemp <> vbNullString then
    if strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & "[User] In(" & strTemp & ")"
  End If

  DoCmd.OpenReport "yourReportName", acViewPreview, , strWhere

I had to change the "AND" to "OR" on both parts because if I selected a user that wasn't a part of the skill group I selected the report came up blank.

I cannot thank you enough for your help. I would have spent days and even weeks trying to figure this out.

Thanks again!

 

[MarcieFess]

Will this procedure work with a comboBox instead of a listBox?

I've tried, using comboBoxToString where this process says listBoxToString; I'm getting a Type Mismatch error.

I only want a single input-single return, and I want it to open a new form where the user can make the second selection (the first form must have a "Create New" option in case the item they are looking for isn't there yet). In my case they need to select the Company they are working with (or create a new Company). When they select the company, they press a button "Choose This Company" and it will open another form.

In this second form, once they've made their second choice (which StoreName within that Company), or created a new entry for the second choice, then I want them to be able to do multiple things with the new selection. They should be able to open a third form to update product information, or they should be able to print the 3 reports required for that company.

 

[nanscombe]

No. For a ComboBox you are only concerned with returning a single value so it's like a Textbox.

If your Combobox is showing a list of values rather than a lookup:

Strings:

If Len(Me.myComboBox & vbNullString) > 0 Then
  if strWhere <> vbNullString Then strWhere = strWhere & " AND "
  strWhere = strWhere & "[myField] = '" & Me.myComboBox & "'"
End If

Numbers:

If Len(Me.myComboBox & vbNullString) > 0 Then
  if strWhere <> vbNullString Then strWhere = strWhere & " AND "
  strWhere = strWhere & "[myField] = " & Me.myComboBox
End If

Dates:

If Len(Me.myComboBox & vbNullString) > 0 Then
  if strWhere <> vbNullString Then strWhere = strWhere & " AND "
  strWhere = strWhere & "[myField] = #" & Me.myComboBox & "#"
End If

If the Combobox is doing a lookup, ie the bit shown is not the underlying value it would be a bit different, assuming it is a number.

If Len(Me.myComboBox & vbNullString) > 0 Then
  if strWhere <> vbNullString Then strWhere = strWhere & " AND "
  strWhere = strWhere & "[myField] = " & Me.myComboBox.Column(0)
End If