UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3  (Go to first unread post)
   Reply to this topicStart new topic
> Filtering Records Based On Password Entered, Access 2013    
 
   
tina t
post Jun 2 2020, 04:46 PM
Post#41



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


QUOTE
If I am understanding correctly the site can be selected from the switchboard applied to all the forms from the pop up filter form? This would set all the forms in the database to only show the site records? I am not too familiar tempvars so is there any templates you can suggest so I can see how this works?

no, that's not the solution i suggested. in my suggested setup, you would apply the site value in the WHERE clause of the query or SQL statement that is being used as the form's RecordSource. then you would filter the records returned by the RecordSource, according to whatever additional criteria you want to add, after the form opens.

you only need to filter for specific records you want to see, from the data form's recordset. and that recordset has already been set to include only one chosen site.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
sarinadipity
post Jun 3 2020, 03:30 PM
Post#42



Posts: 74
Joined: 20-March 20



Okay but how would they choose the site to make that possible? Would I need a different front end for each site?
Go to the top of the page
 
tina t
post Jun 3 2020, 04:47 PM
Post#43



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


no, separate frontend apps are not necessary.

QUOTE
I am currently using the button password VBA by allen browne which works for standard security I am just not sure how to covert it to different passwords. The added later of difficultly to this is I have unbound search boxes for searching the data using a filter but I need a main filter that only shows the site's records but still has the search feature search those records in the site's filter. If that makes sense?

Edit* Or maybe not even a password, maybe if I could do something where after they click on the button on my switchboard a popup comes up telling them to select a Site from the drop down menu and it brings up the records as I mentioned before. Something along those lines is what I need.

if you just need the user to choose a site, either put it in the switchboard form, or the "choose a filter" form. just do it at some point before you open the data form, so the choice get assigned to the TempVar. then when the form is opened, the underlying query or SQL statement will only return the records that match the TempVar value as referenced in the WHERE clause.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
sarinadipity
post Jun 4 2020, 03:09 PM
Post#44



Posts: 74
Joined: 20-March 20



Can you put multiple TempVar criteria?
Go to the top of the page
 
tina t
post Jun 4 2020, 03:16 PM
Post#45



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


how do you mean? give me an example of what you're thinking to do, pls.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
tina t
post Jun 4 2020, 11:40 PM
Post#46



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


ok, hon, i wrote a small demo db to show you what i mean by restricting the records in a form's RecordSource query, and filtering the recordset returned by the query.

i won't be able to upload it until Friday evening, because i wrote it at work, but can't zip it for upload until i get home Friday. so, patience for one more day, if you will.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
sarinadipity
post Jun 5 2020, 02:23 PM
Post#47



Posts: 74
Joined: 20-March 20



Thank you so much! Sorry for the confusion I am still so new with this that I am trying to redo old mistakes while adding to the database.
Go to the top of the page
 
tina t
post Jun 5 2020, 11:59 PM
Post#48



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


okay, here's the demo db. it has two tables, tblSites and tblData, with a one-to-many relationship between the two - on the assumption that one site may have many data records, and each data record may belong to only one site. so, tblSites.sID 1:n tblData.d_sID

there is one standard module, which dims a tempvar to hold the value of tblSites.sID, for the site chosen. more on that coming up.

there are two forms. frmData is used to display the records in tblData. there is one query, which is used as the RecordSource of frmData. the query includes a WHERE clause to restrict the recordset to one site at a time. more on that in a bit.

the second form is frmFilter, an unbound form that is used to choose a site from a combobox control, and then optionally enter a value in a textbox control that will be used to filter the recordset of frmData. the user must choose a site before clicking the command button to open frmData. on doing that, the VBA code behind the button will

1) set the value of TempVars!tvSiteID to the primary key of the site chosen in the combobox.

2) assign the value of the textbox control to a string variable, as a filter expression. if there is no value entered in the textbox, the string is set to a zero-length string (ZLS), as "".

3) open frmData, using DoCmd.OpenForm, and setting the OpenArgs argument to the string variable.

opening frmData from frmFilter ensures that the user chooses a site first. then when frmData is opened, the query runs, using the value of the tempvar in its' WHERE clause, so the recordset that is loaded in the form includes records from only one site. then, if a filter value was entered in the textbox in frmFilter, then the filter string is applied to the form's recordset. so the user will see frmData open, including only the records for one site, filtered to show only the desired records from that site. there is a button on frmData to remove the filter. when you click the button, you'll see all the records for the chosen site, with no filter applied. when frmData is closed, and then frmFilter is closed, the Close event of frmFilter runs code to reset the value of the tempvar to Null.

frmFilter opens when you open the db. suggest you ignore it and look at standard module first, to see the tempvar. then the tables and their relationship. then look at the query's Design view and SQL view, to see the restriction on field d_sID by the tempvar. then go back to frmFilter and try opening frmData with the command button, with and without entering a filter value. last, take a look at the VBA code behind both forms, to understand how it's doing what you saw the forms do. hopefully this demo will help you understand the difference between restricting the recordset returned by a form's RecordSource, and filtering the records that are in the recordset.

or maybe i've confused you worse. i hope not!

hth
tina

Attached File  Demo_tina.zip ( 26.16K )Number of downloads: 3

This post has been edited by tina t: Jun 6 2020, 12:06 AM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
sarinadipity
post Jun 8 2020, 02:33 PM
Post#49



Posts: 74
Joined: 20-March 20



Thank you this is very useful!


So it all works and I understand it when I am playing with your database but when I apply it to mine it doesnt work. It functions without glitching but doesnt filter the documentsearch form it opens. I cant figure out why.


Okay I see my mistake. If I am not using a query as the recordsource how do I apply to the filter to my recordsource? My recordsource is a select statement as followed.


CODE
SELECT tblQualityQMSDocuments.ID, [Site] & " " & [QMSNumber] AS [Site IMS], tblQualityQMSDocuments.[Management System], tblQualityQMSDocuments.[Document Class], tblQualityQMSDocuments.Department, tblQualityQMSDocuments.[Document File], tblQualityQMSDocuments.[Document Name], tblQualityQMSDocuments.[Date Written], tblQualityQMSDocuments.[Latest Revision Date], tblQualityQMSDocuments.Revision, tblQualityQMSDocuments.[File Change Log], tblQualityQMSDocuments.EnteredOn, tblQualityQMSDocuments.EnteredBy, tblQualityQMSDocuments.UpdatedOn, tblQualityQMSDocuments.UpdatedBy, tblQualityQMSDocuments.Inactive, tblQualityQMSDocuments.InactiveOn, tblQualityQMSDocuments.InactiveBy, tblQualityQMSDocuments.OriginalCopy, tblQualityQMSDocuments.QMSNumber, tblQualityQMSDocuments.Audits, tblQualityQMSDocuments.[Reference Documents], tblQualityQMSDocuments.Site, tblQualityQMSDocuments.UpdateChecks FROM tblQualityQMSDocuments WHERE (((tblQualityQMSDocuments.Inactive)>-1) AND ((tblQualityQMSDocuments.OriginalCopy)>-1));


I changed my code to this



CODE
SELECT tblQualityQMSDocuments.ID, [Site] & " " & [QMSNumber] AS [Site IMS], tblQualityQMSDocuments.[Management System], tblQualityQMSDocuments.[Document Class], tblQualityQMSDocuments.Department, tblQualityQMSDocuments.[Document File], tblQualityQMSDocuments.[Document Name], tblQualityQMSDocuments.[Date Written], tblQualityQMSDocuments.[Latest Revision Date], tblQualityQMSDocuments.Revision, tblQualityQMSDocuments.[File Change Log], tblQualityQMSDocuments.EnteredOn, tblQualityQMSDocuments.EnteredBy, tblQualityQMSDocuments.UpdatedOn, tblQualityQMSDocuments.UpdatedBy, tblQualityQMSDocuments.Inactive, tblQualityQMSDocuments.InactiveOn, tblQualityQMSDocuments.InactiveBy, tblQualityQMSDocuments.OriginalCopy, tblQualityQMSDocuments.QMSNumber, tblQualityQMSDocuments.Audits, tblQualityQMSDocuments.[Reference Documents], tblQualityQMSDocuments.Site, tblQualityQMSDocuments.UpdateChecks FROM tblQualityQMSDocuments WHERE (((tblQualityQMSDocuments.Inactive)>-1) AND ((tblQualityQMSDocuments.OriginalCopy)>-1) AND ((tblQualityQMSDocuments.Site)=[TempVars].[tvSiteCode]));


(I changed the code to have it has tvSideCode) but the opened form is coming up blank which I think means the TempVars isnt setting and I am not sure why.



GOT IT TO WORK! Thank you so much! I still have to tweak it a bit but this is incredible! Thank you!


This post has been edited by sarinadipity: Jun 8 2020, 03:17 PM
Go to the top of the page
 
sarinadipity
post Jun 8 2020, 03:21 PM
Post#50



Posts: 74
Joined: 20-March 20



This code works perfectly but if I were to have it pull up two sites at once how would I set both. So if all sites need to see just their site and Site A how would I set it to both in TempVars?
Go to the top of the page
 
tina t
post Jun 8 2020, 04:35 PM
Post#51



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


the setup in my demo won't support two sites. so, if the business process includes the requirement to include two sites in the form's RecordSource, it would require a different setup. i don't think i want to try to explain it in a post, but i can add to the demo, to show a different setup.

if you're interested, i'll modify the demo, hon. it's not difficult, but it may take me all week to get it done, because i can only work with a .accdb file at work, meaning i have to work on it during my lunch break or after i clock out for the day. post back if you want to see the modification, pls.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
sarinadipity
post Jun 9 2020, 08:34 AM
Post#52



Posts: 74
Joined: 20-March 20



What would you think would be the easiest way to do it?
Go to the top of the page
 
tina t
post Jun 9 2020, 02:47 PM
Post#53



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


there are numerous ways to set it up, depending on the business requirement and developer preference. as i said, i don't want to try to explain a method in a post. adding to the demo db is easiest. let me know if you'd like to see it.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
sarinadipity
post Jun 9 2020, 03:34 PM
Post#54



Posts: 74
Joined: 20-March 20



If you have the time I would appreciate it because I have tried to see if I could get it to work but I just cant seem to
Go to the top of the page
 
tina t
post Jun 11 2020, 01:54 AM
Post#55



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


QUOTE
if all sites need to see just their site and Site A how would I set it to both in TempVars?

okay, i left the original demo setup intact. i added

qryData1
frmData1
frmFilter1

these three objects all work together, just as the original objects work together. open qryData1, and you'll see all the records from tblData for SiteA. open the query in Design view, and SQL view, and you'll see that all i did was hard-code the primary key value of SiteA (from tblSites) into the

--------------------
"the wheel never stops turning"
Go to the top of the page
 
tina t
post Jun 11 2020, 02:21 AM
Post#56



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


QUOTE
if all sites need to see just their site and Site A how would I set it to both in TempVars?

okay, i left the original demo setup intact. i added

qryData1
frmData1
frmFilter1

these three objects all work together, just as the original objects work together. open qryData1, and you'll see all the records from tblData for SiteA. open the query in Design view, and SQL view, and you'll see that all i did was hard-code the primary key value of SiteA (from tblSites) into the WHERE clause, along with the reference to the tempvar. open frmFilter1, and it looks the same, except the command button points to frmData1. you can still choose any of the three sites in the combobox control on frmFilter1; choose B or C and click the command button to see all the records for Site1 and the site you chose. the filter works the same, too - it filters the records in the form's recordset. the fact that the recordset contains records from two sites doesn't matter.

QUOTE
if I were to have it pull up two sites at once how would I set both

this is a slightly different question. if you want to choose two sites, the setup is just a bit different. i added a third demo group:

qryData2
frmData2
frmFilter2

these three objects all work together, just as the other groups of objects work together. this time, open the standard module, and you'll see that i declared a second tempvar to hold a second siteID. open qryData2, and you'll see that no records are returned, because no site(s) are chosen yet. open frmFilter2, and you'll see two combobox controls, so that one or two sites may be chosen. (you can get fancy and force the user to choose from the first combo first, and only then allow a second site to be chosen, and filter the droplist in the second combo to not display the site already chosen in the first combo. i didn't bother to do any of that, in this demo; if you choose SiteB twice, then frmData2's recordset will consist of the records for SiteB - only once, there's no possible duplication of records in the form.)

go back to qryData1, and open it in Design view and in SQL view. remember that in this query, we hard-coded the primary key value of SiteA in the WHERE clause. take a look at that again, and then open qryData2 in Design view and SQL view. you'll see that, in this WHERE clause, we just replaced the hard-coded value with a reference to the second tempvar.

that's pretty much it. frmFilter2 and frmData2 run pretty much the same as the other demos; choose one site, and the data form returns the records for that site; choose two sites and you get the records for both sites. and, again, if you enter a filter value, the filter is applied to the form's recordset, displaying only the targeted records. remove the filter, and all the records in the form's recordset will display.

keep in mind that there are usually several ways to accomplish most things in Access. concentrate on learning techniques, so that you have a skillset to choose from and apply, depending on how you want to do a given task. and how you do any given task will usually be guided - at least to some extent - by what is required to support the business process.


hth
tina

Attached File  Demo_tina1.zip ( 47.84K )Number of downloads: 6

--------------------
"the wheel never stops turning"
Go to the top of the page
 
sarinadipity
post Jun 11 2020, 12:53 PM
Post#57



Posts: 74
Joined: 20-March 20



This works perfectly! I cant thank you enough!
Go to the top of the page
 
tina t
post Jun 11 2020, 03:03 PM
Post#58



Posts: 6,694
Joined: 11-November 10
From: SoCal, USA


you're welcome, hon, we're all glad to help. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
3 Pages V < 1 2 3


Custom Search


RSSSearch   Top   Lo-Fi    15th July 2020 - 10:33 PM