Filtered records

[ArviLaanemets]

Look next post instead!

[ArviLaanemets]

You can also use your existing code to fill the dummy table. It will be slower, but change to code will be minimal.

Private Sub CmdViewSelection_Click()

Dim strQry As String
Dim ctl As Control
Dim varItem As Variant
Dim dbs As DAO.Database

On Error GoTo CmdViewSelection_Click_Err

'make sure a selection has been made
If Me.SearchResults_Multi.ItemsSelected.Count = 0 Then
  MsgBox "At Least 1 Record Must Be Selected!!", vbInformation, "Record Selection Required"
  Exit Sub
End If
'add selected values to string
Set ctl = Me.SearchResults_Multi
 For Each varItem In ctl.ItemsSelected
  strQry = "INSERT INTO tblDummy (RoomID) VALUES (" & ctl.ItemData(varItem) & ")"
  dbs.Excecute strQry
 Next varItem

 DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(SELECT RoomID FROM tblDummy)"
...

[hinchi1]

Do i have to add all the relevant field names to the dummy table? as i still cannot get it to work.

[hinchi1]

i put your code in to the click event of the "Select All" button and nothing was added to the table i created. I am at a totla loss. But thanks for your help anyway.

[hinchi1]

I tried the code and got method or data member not found and the following line was highlighted. So not sure where this is going wrong. Sorry to keep coming back too you on this.

dbs.Excecute strQry

Private Sub CmdViewSelection_Click()

Dim strQry As String
Dim ctl As Control
Dim varItem As Variant
Dim dbs As DAO.Database

On Error GoTo CmdViewSelection_Click_Err

'make sure a selection has been made
If Me.SearchResults_Multi.ItemsSelected.Count = 0 Then
MsgBox "At Least 1 Record Must Be Selected!!", vbInformation, "Record Selection Required"
Exit Sub
End If
'add selected values to string
Set ctl = Me.SearchResults_Multi
For Each varItem In ctl.ItemsSelected
strQry = "INSERT INTO tblDummy (RoomID) VALUES (" & ctl.ItemData(varItem) & ")"
dbs.Excecute strQry
Next varItem

DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(SELECT RoomID FROM tblDummy)"

[hinchi1]

Tried code again and got this error now.

Run time error '91'
object variable or with variable block not set.

[ArviLaanemets]

There was missing code row, as I did write the code on fly. And instead of Execute I used DoCmd. I needed to manipulate warnings, but so you don't need to activate DAO.
And I added a possibility to use a single report button - when no filter is set, all rooms are selected for reporting.

Private Sub CmdViewSelection_Click()

Dim strQry As String
Dim ctl As Control
Dim varItem As Variant

On Error GoTo CmdViewSelection_Click_Err

'Delete previous entries from tblDummy
DoCmd.SetWarnings (False) ' warnings are set off - otherwise you may have to click OK for several hundred times
strQry = "DELETE FROM tblDummy"
DoCmd.RunSQL strQry

'Fill tblDummy
If Me.SearchResults_Multi.ItemsSelected.Count = 0 Then ' when nothing is selected, the report includes all rooms
    strQry = "INSERT INTO tblDummy SELECT RoomID FROM qryRoomInformation"
    'dbs.Execute strQuery
    DoCmd.RunSQL strQry
Else ' the table tblDummy is filled with selection
    Set ctl = Me.SearchResults_Multi
    For Each varItem In ctl.ItemsSelected
        strQry = "INSERT INTO tblDummy (RoomID) VALUES (" & ctl.ItemData(varItem) & ")"
        'dbs.Execute strQuery
        DoCmd.RunSQL strQry
    Next varItem
End If
DoCmd.SetWarnings (True) ' warnings are set back on

' Open the report
DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(SELECT RoomID FROM tblDummy)"
... ' continue as in original event

[hinchi1]

Thanks. On way home from work will try later and let you know. Much appreciated.

[June7]

Instead of the SelectAll button that actually selects all records in the listbox, use a checkbox and code:

If Me.chkAll Then

    strWHERE = "[CampusNames] & [BuildingName] & [RoomNumber] & [RoomActivity] & [RoomType] & [WorkType] & [SquareFootage] Like '*" & Me.SearchFor_Multi & "*'"

    DoCmd.OpenReport "rptPartBuildings", acViewReport, , strWHERE

Else

    ...

End If

If users are manually selecting group of records in the listbox and the resulting IN() string is more than 255 characters, then the temp table approach may be best, otherwise build a search form with multiple search controls. See example in http://allenbrowne.com/ser-62.html

[hinchi1]

Will try in the morning, thanks for all ideas,/suggestions, much appreciated.

[hinchi1]

You can also use your existing code to fill the dummy table. It will be slower, but change to code will be minimal.

Private Sub CmdViewSelection_Click()

Dim strQry As String
Dim ctl As Control
Dim varItem As Variant
Dim dbs As DAO.Database

On Error GoTo CmdViewSelection_Click_Err

'make sure a selection has been made
If Me.SearchResults_Multi.ItemsSelected.Count = 0 Then
  MsgBox "At Least 1 Record Must Be Selected!!", vbInformation, "Record Selection Required"
  Exit Sub
End If
'add selected values to string
Set ctl = Me.SearchResults_Multi
 For Each varItem In ctl.ItemsSelected
  strQry = "INSERT INTO tblDummy (RoomID) VALUES (" & ctl.ItemData(varItem) & ")"
  dbs.Excecute strQry
 Next varItem

 DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(SELECT RoomID FROM tblDummy)"
...

good morning

I have run your code this morning and it works a treat. As you stated it is a little slow and would be interested how to speed this function up, although not critical. Thanks again for your support as it has been invaluable and i have learnt a lot. This was a problem I came across this problem several months ago during the design of my database and just left it on the back burner, but thanks to your guidance you have solved a burning issue for me. Thanks.

[ArviLaanemets]

About speeding up:
1. When filter string will be shorter, you can use your previous approach. But it is difficult to decide, at which point to change the approach. And when the number of rooms to filter is small, the real time gain is minimal;
2. When there is no records selected to filter, and you use the code example from my last posting, you can run DoCmnd.OpenReport separately for "If Me.SearchResults_Multi.ItemsSelected.Count = 0" (without filter) and for "Else" (with filter);
3. Instead of filling tblDummy stepwise, you can use VBA to construct a SQL string and fill the table executing a single query string ("INSERT INTO tblDummy SELECT RoomID FROM qryRoomInformation WHERE ..."). For sure it will be faster, especially when the number of filtered rows is bigger. At same time I got the feeling, that columns compared in WHERE clause will depend on filter text in SearchResults_Multi text box. And you have to take another filter (the dropdown one) into account too. So a lot of more code spent on SQL string construction to get this to work properly.

[hinchi1]

Thanks for your response and to be honest i am really happy with what i have. My next stage is to learn Visual Studio and work with the data i have and maybe redesign the front end, but that is a long way off. Thanks again for your help. I have changed the code to all 5 of my report forms and it works perfectly.