×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

FILTER SUBFORM USING MAIN FORM FIELDS- VBA
3

FILTER SUBFORM USING MAIN FORM FIELDS- VBA

FILTER SUBFORM USING MAIN FORM FIELDS- VBA

(OP)
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.

RE: FILTER SUBFORM USING MAIN FORM FIELDS- VBA

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/ac...).
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:

CODE

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

2) Now build your sub-form data source:

CODE

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

RE: FILTER SUBFORM USING MAIN FORM FIELDS- VBA

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

CODE

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

and

CODE


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

But, that's a personal preference... pc1

---- Andy

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

RE: FILTER SUBFORM USING MAIN FORM FIELDS- VBA

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

RE: FILTER SUBFORM USING MAIN FORM FIELDS- VBA

Agreed thumbsup2

---- Andy

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

RE: FILTER SUBFORM USING MAIN FORM FIELDS- VBA

Andrzejek,

winky smile

ATB,

Darrylle

RE: FILTER SUBFORM USING MAIN FORM FIELDS- VBA

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close