FILTER SUBFORM USING MAIN FORM FIELDS- VBA 3

[Kim296]

Hi everyone, I have tried researching this and watching videos for a week now, but my brain hasn't been able figure this out to save me. Any help is appreciated. I have a simple program that will be designed for keeping up with documentation on employees (tardiness, verbal warnings, recognition, etc...) I'm trying to figure out the permissions, so that each user can only see the employees under their own command and not other employees.

This program only has one primary table that pulls from our main Record Maintenance System (RMS) a vendor program. This holds the employee table (dbo_emmain). Each employee record has 4 different fields (Division, Unit, Shift and Rank).

The main form "frmNavigation" is based on a user query for the individual that is signed in. In my train of thought, if the particular permission, tblUsers, is checked and matches the subform employee table record (dbo_emmain), then the record should be visible else if not then filtered out.

Please see the two attachments (code and form). My subform (employee listing records) is based on a query to show only active employees and not terminated or retired employees. The query also sorts. I don't want to base the subform on a table as that would be even more vba coding on top of what I'm trying to figure out.

I want to be able to filter the subform records (on right) if they match the table permissions (on left). All of the employee details will be hidden under the subform if I can figure it out. I don't know if this make sense or not. Thank you for any help.

 

[Darrylles]

Kim,

I would list each of your permission 'types' (Division, Unit, Shift, Rank) in multi-select list boxes (not individual checkboxes, which are cumbersome to process).
The list box entries for 'Rank' would be the actual 'Rank' values.
Multi-select means that zero, 1 or many can be selected at once.

I don't know your table names for the main data, but let's assume it's called tblMainData...

Once the user has multi-selected in the relevant list-boxes, you can then build a SQL statement on the fly, when they click 'Refresh'.

So:

1) Get the list of selected values from the list boxes, into string variables - comma separated.
(Here's how to do that:

https://docs.microsoft.com/en-us/office/vba/api/access.listbox.itemsselected).

E.g. So, we'll end up with strings (containing items as selected on your form), which YOU must enclose in brackets - ready for the SQL:

strRank =  "('CAPT','LT','SGT','CPL') "
strShift = "('PA','PB','PC','PD') "

2) Now build your sub-form data source:

strSQL = "SELECT empl_id, " & _
                "employee," & _
		"[hire date]," &_
		"divison," & _
		"section," & _ 
		"shift," & _
		"rank " & _
	 "FROM 	 tblMainData " & _
	 "WHERE  rank  IN " & strRank & _
	 "AND	 shift IN " & strShift

3) Assign this built strSQL content as the subform recordsource and requery it.

It will return records that have only Ranks in strRank and Shifts in strShift (and any other 'types' that you may wish to add).

My way (but not the only way).

Darrylle

 

[Andrzejek]

To build on your approach, Darrylle, I would do:

strRank =  "'CAPT','LT','SGT','CPL'"
strShift = "'PA','PB','PC','PD'"

and

[blue]
If Len(strRank) * Len(strShift) > 0 Then[/blue]
    strSQL = "SELECT empl_id, " & _
                "employee," & _
		"[hire date]," &_
		"divison," & _
		"section," & _ 
		"shift," & _
		"rank " & _
	 "FROM 	 tblMainData " & _
	 "WHERE  rank  IN (" & strRank & ")" & _
	 "AND	 shift IN (" & strShift & ")"[blue]
Else[/blue]
    [green]'Message to the user to select Rand [u]and[/u] Shift[/green]
[blue]End If[/blue]

But, that's a personal preference...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Darrylles]

Andrzejek,

Of course, but I'd do far more than that to 'polish' this, however, this is a complete change to the solution 'architecture'; it's only a 'framework' of how to not use 'check-boxes'.
I haven't even implied how the user should be guided etc. - that's up to the developer (and trivial really).

I could have noted that 'All' was an option, and as such - there should be an IF statement BEFORE building the SQL string - e.g.:
If 'ALL' is checked: SQL = "SELECT *" else ..... (build the selective SQL string).

I get your drift Andrz (and you've been here a long time - like me).
I COULD provide the entire code, but, we don't (because Kim won't learn - if he / she doesn't 'do').

ATB,

Darrylle

 

[Andrzejek]

Agreed

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Darrylles]

Andrzejek,

;-)

ATB,

Darrylle

 

[Kim296]

Thank you all for your responses. Yes, I do like to learn and try different things for myself. I will try what you both suggested. Thank you for your time and examples; I really appreciate your responses. It does help me think of different ways to approach my issue.

 

[Darrylles]

Kim,

Please report back here, when you achieve your solution.
You have no need to follow advice, these things can be done many different ways, but, it's always nice to see how things panned out.

;-)

Darrylle

 

[Andrzejek]

... and help others who may be looking at his thread for a solution to their similar needs.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson