Subform development - Selecting a subset of the database

[Sherri726]

I need help suggestions for the best development approach for a form. The database is loaded with a master set of records. Tbl1 contains a master "record filing structure" related to tbl2, a set of "record schedules" (note many-to-many) each schedule then has separate possible dispositions tble3 (one-to-many). I want a user to be able to view this master File Plan with hierarchy and relationships defined but only select records that would apply to their organization, then output the results.

I want to allow the user the ability to deselect or remove a large number of records at the tbl1 level (the user then does not have to review all the records below or related) or deselect single items as needed.

I am using a subsubform method now but if anyone has a solution to viewing the records in a way that a user can deslect items at any level and apply to those below I would appreciate it. I basically need for the user to be able to view the records as if they were in a report ( all at once but I have too many layers of subforms) then deselect those that don't apply. I have received suggestions to try combo or listboxes but I have multiple columns for each record (ID and description).

Please ask me any questions you need to - I seriously need help with this layout.

 

[stix4t2]

Sherri,
I don't quite get the whole problem but consider this. If you create a query to build a temporary table with the records you want to show and an extra yes/no field for selection. You could build a subform to show the table and allow users to select/unselect particular records. You could also add fields to the main form to set filters for the list. Lastly you could include select all/unselect all buttons to run update queries on the temp tables. Would this help?

 

[Sherri726]

stix4t2,

Possibly, I'm not sure I follow all of it or know enough to do this. I've tried something similar to this I have the table But when you say build a subform the show the table what would be the main table? Also, a number of my records have the same titles which need to be suppressed if within the same table. How can you do this when viewing on the same entry form?

 

[stix4t2]

Maybe you could build a query of tbl1.flds tbl2.flds tbl3.flds. Then you could use this for a subform that shows the values. On the main form you could use listboxes with multi-select to select/deselect different field's unique values. Then you could use the listboxes selections to build filters for the subform. You could also use the filters to generate delete queries to remove records.

 

[Sherri726]

Stix4t2,

I think this would actually work for me, if I'm envisioning it right and have the skills to program it. Can you guide me a little on how you use the listbox selections to "build filters" for the subform?

Thanks so much!

Sherri

 

[stix4t2]

Sherri,

Do want the code intensive way or the temp table way? Also can you tell me more about the number of users and the distribution of the database. Are you using a backend or all in one db? Are you distributing the entry database or sharing?

 

[Sherri726]

stix4t2,

Here are the answers to your questions. It is a all in one db and we will be distributing the database rather than sharing it.

Just some additional information. Tbl1 has 161 records tbl2 has 532 with the many-to-many relationship 599 and tbl3 (one-to-many between tble2 and 3) has 1248 once a query report is run (subsubform) my total count of records 1400. My delima is to allow the user to view this as a whole (if possible) and elminate what is not applicable resulting in a report of what is valid.

Thanks for your patience.

 

[stix4t2]

Sherri,

I think it is important to set the framework first and then followup with good development. That said, may I ask a few more questions? The distributed database you described does not share data, is this a problem or is this database truly standalong?

The development ideas I have in mind could be more of an Access solution or a code solution. Would you prefer the easier Access stuff or do you want the faster code stuff that takes longer to implement and test? The main difference is that the code stuff builds dynamic sql statements while the access stuff uses temporary tables and merely calls requery. You may want to consider which of these techniques will better serve you in the future?

 

[Sherri726]

Stix4t2,
This is truly a standalone, we are providing a tool using Access and distributing it but sharing support on the network isn't supported, regrettably.

Something tells me in the long run the code solution is better but my skill set and time contraints say I want the easier Access stuff. I'd prefer to say both (so I can work the other later) but you have provided me with so much help the Access stuff is fine. Can I provide you with any other answers?

Sherri

 

[stix4t2]

Sherri,
Sorry, I wrote the response yesterday and the server croaked when I submitted and the answer went off to the bit bucket. So lets try this again.
After writing both solutions, I think you may be able to handle the code version, and seems to be much cleaner. So if your ready, away we go.

The theory here is to have a main form with listboxes for selection and a subform holding a query of information. As the user selects values in the listboxes, the code will update the filter to the subform to limit the list.

I will assume you can create the listboxes and subform yourself. The field name that listbox will filter for is placed in the tag property so the filter can be made by passing the listbox.
listbox.tag = "fieldName"

The code is pasted below. Each listbox has an onClick event to build the filter. The call is a generic sub that builds the filter for each listbox on the form. The generic sub calls another sub to build a filter for a single listbox and adds it to any previously defined filters. After each listbox has had a chance to build a filter, the final filter is applied to the subform to limit the list.

Option Compare Database
Option Explicit

'add events for each listbox you want to offer
Private Sub List0_Click()
    buildListBoxesFilter
End Sub
Private Sub List1_Click()
    buildListBoxesFilter
End Sub

Private Sub buildListBoxesFilter()
Dim sWhere As String
    'build a line for each listbox defined
    sWhere = buildListBoxFilter(sWhere, List0)
    sWhere = buildListBoxFilter(sWhere, List1)
    Me.SubForm.Form.FilterOn = True
    Me.SubForm.Form.Filter = sWhere
End Sub

Private Sub buildListBoxFilter(sWhere As String, lbList As ListBox)
Dim lbWhere As String, sFilter As String
Dim lItem As Long
    'build filter for list box, OR each value so they are included
    For Each varItem In lbList.ItemsSelected
        lbWhere = lbWhere & IIf(Len(sfWhere) > 0, " OR ", "")
        sFilter = lbList.Tag & " = '" & lbList.ItemData(varItem) & "'"
        lbWhere = lbWhere & sFilter
    Next varItem
    If (Len(lbWhere) > 0) Then 'test if listbox has filter to add
        sWhere = sWhere & IIf(Len(sWhere) > 0, " AND ", "")
        sWhere = sWhere & "(" & lbWhere & ")"
    End If
End Sub

If you think you can handle this and get it to work, submit a post for the Delete solution or select/deselect all buttons. Or let me know if you have any questions.
Good Luck

 

[stix4t2]

Sherri,

I guess i should of tested before i pasted. This is a better code, sorry.

Option Compare Database
Option Explicit

'add events for each listbox you want to offer
Private Sub List0_Click()
    buildListBoxesFilter
End Sub
Private Sub List1_Click()
    buildListBoxesFilter
End Sub

Private Sub buildListBoxesFilter()
Dim sWhere As String
    'build a line for each listbox defined
    buildListBoxFilter sWhere, List0
    buildListBoxFilter sWhere, List1
    Me.SubForm.Form.FilterOn = True
    Me.SubForm.Form.Filter = sWhere
End Sub

Private Sub buildListBoxFilter(sWhere As String, lbList As ListBox)
Dim lbWhere As String, sFilter As String
Dim varItem As variant
    'build filter for list box, OR each value so they are included
    For Each varItem In lbList.ItemsSelected
        lbWhere = lbWhere & IIf(Len(lbWhere) > 0, " OR ", "")
        sFilter = lbList.Tag & " = '" & lbList.ItemData(varItem) & "'"
        lbWhere = lbWhere & sFilter
    Next varItem
    If (Len(lbWhere) > 0) Then 'test if listbox has filter to add
        sWhere = sWhere & IIf(Len(sWhere) > 0, " AND ", "")
        sWhere = sWhere & "(" & lbWhere & ")"
    End If
End Sub

 

[Sherri726]

Thanks stix4t2,

I will give it a try and let you know how it goes.

Sherri