Filter Text/date From A Sub-form Help 2

[iuianj07]

Hello guys,

First of all, I would like to thank you for the time in reading my post, as it could be rather lenghty... And this is also my first time creating a properly normalized table. I have been creating Access databases before, but were not properly normalized. I have been creating spreadsheet like tables/databases before instead of a relational database tables etc...

I have a question with regards on how I will be able to filter date/text through a sub-form? This situation is new to me since before I have been using a spreadsheet style method which makes it easier to filter.

Basically what this database/our project does is that, we have a client (a Bank) wherein we index loan files for them, and sometimes create ASR (Asset Summary Report) for certain loans. They will send us notifications that they want a loan indexed, and we use this database to track our statuses for the loans we index for them. There will be different phases a loan will go through from receiving a new request up until we return the file to the client (documents received, indexing in process, indexing complete, indexing QC (quality control) in process, indexing QC complete etc...) which we put date fields, so we now on what day was this work being done.

My question now is, since we have multiple users in the database, I would like to be able to have a button on my form (Draft Form) that filters for a loan that has been worked on by the user who logged in from the login form AND loans that hasn't been worked on yet... So in example, if I logged in the database as Samuel Adams, then when I open the Draft Form, the loans that would appear on my queue are loans that I have worked on (put my name in a status -- like indexing in process etc...) And loans that hasn't been worked by anyone yet.

My Main form is bound to this query

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, Job_Tracking.Property_Loan_Name, Job_Tracking.PortfolioID, Job_Tracking.Date_Requested, Job_Tracking.FileSourceID, Job_Tracking.Specific_File_Instructions, Job_Tracking.File_Completion_Requested_Date, Job_Tracking.Date_Situs_Received_File, Job_Tracking.Situs_Est_Date_Of_Completion, Job_Tracking.PriorityID, Job_Tracking.Link_To_Completed_Files
FROM Job_Tracking;

Then my subform which is linked to the main form via Situs_ID is bound to Indexing_Loan_Status Table. The table structure is:

LoanStatusID - PK
Situs_ID - FK to Job_Tracking
Analyst
InStChID - FK to Indexing_Status_Change
StatusTime
StatusDate
StatusComment

Indexing_Status_Change table structure is:

InStChID - PK
Status - (which lists all of the different status the loan could go through)


Any help is greatly appreciated and please let me know if I need to give more information.

Thank you,

 

[vbajock]

This is how I handle problems like that, there are a bunch more, but I've used this for years without a problem. This code detects the Active Directory user name:


->this goes in top declaration part of module

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

-then this function goes anywhere in the module

Public Function NetworkUserName() As String
On Error GoTo Err_Handler
'Purpose: Returns the network login name.
Dim lngLen As Long 'Length of string.
Dim strUserName As String
Const lngcMaxFieldSize As Long = 50 'Length of field to store this data.

'Initialize
NetworkUserName = "Unknown"
strUserName = String$(254, 0)
lngLen = 255&

'API returns a non-zero value if success.
If apiGetUserName(strUserName, lngLen) > 0& Then
lngLen = lngLen - 1& 'Without null termination char.
If lngLen > lngcMaxFieldSize Then 'Maximum field size
lngLen = lngcMaxFieldSize
End If
NetworkUserName = Left$(strUserName, lngLen)
End If

Exit_Handler:
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, "SystemLib", ".NetworkUserName", , False)
Resume Exit_Handler
End Function

In your code, !SomeField=NetworkUserName() to store users name into the table where you need it, or me!SomeFormField=NetworkUserName() to do it via a form.

In your Query, you would use something like sql=sql+"WHERE somefield=" ' "+NetworkUserName()+" ' "

 

[iuianj07]

hello vbajock...

thank you for you help, i think i was unclear on explaining the login part of database.

In terms of the login name, in the access database that I have, it has a login form, with a combo box list of users in an employee table. and the user, will just select their name on the combo box, then the Draft form should show up...

My question then is, with my explanation above, is there a way that I could put a button in the Draft form, to filter the loans based on who logged in the database and records that have not been assigned to anyone yet?

Any help is greatly appreciated.

 

[MajP]

It is not clear what field, in what table identifies a record that "you" have worked on. Is that field "Analyst" in Indexing_Loan_Status Table?

Are you looking for the main form to show all records where there is at least one record in Indexing_Loan_Status where the logged in user worked or it or no one has worked (no records)? If that is the case build a simple query left joining Job_tracking and Indexing_Loan_Status. Include Situs_ID and the criteria is where and Indexing_Loan_Status.Situs_ID is Null or Analyst = getloggedInAnalyst().
This will give you a query that returns all the situID that have not been worked or the logged in analyst has worked them. Call the qryLoggedUnworked

The main form query would then be "where Job_Tracking.Situs_ID in (select Situs_ID from qryLoggedUnworked)".

Then the next question, where is the value of the logged in user saved? Public variable, table, or form. Anyway to use in a query wrap in a function.

Public function getLoggedInAlayst() as variant
  'some code depending on how stored
  'getLoggedinAnalyst = publicVariable
  'or if in local table
  'getLoggedinAnalsyt = dlookup(....)
  'or form still open or hidden
  'getLoggedInAnalyst = forms("loginForm").cmboLogIn
  'if this is a text field then wrap in single quotes
  getLoggedinAnalyst = "'" & getLoggedInAnalyst & "'"
end function

 

[iuianj07]

Hello MajP,

I tried creating this query on the Form's property like you suggested:

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, Job_Tracking.Property_Loan_Name, Job_Tracking.PortfolioID, Job_Tracking.Date_Requested, Job_Tracking.FileSourceID, Job_Tracking.Specific_File_Instructions, Job_Tracking.File_Completion_Requested_Date, Job_Tracking.Date_Situs_Received_File, Job_Tracking.Situs_Est_Date_Of_Completion, Job_Tracking.PriorityID, Job_Tracking.Link_To_Completed_Files
FROM Job_Tracking LEFT JOIN Indexing_Loan_Status ON Job_Tracking.Situs_ID = Indexing_Loan_Status.Situs_ID
WHERE (((Indexing_Loan_Status.Analyst)=[Forms]![Login Form]![Combo0]));

But for some reason, when I ran it, it only gives one record (from the main form) that has the user logged in. But I know that there are 3 records that the user has worked on and therefore should show up.

Below is a link of a copy database I am working on...

First form you will see is the login form where there is a combo box that should list all names of the users, after I hit logon, open the Draft Form, and on the Indexing/Redaction tab, you will see the Indexing/Redaction Loan Status sub form, where I want it to filter only for records that at least the user who logged on worked on it, and any other unassigned loans.

http://www.mediafire.com/?9ub3cjlse0e2522

Any help is greatly appreciated.

 

[iuianj07]

Hello,

Sorry, but to answer your questions, Yes the Analyst field is found in Indexing_Loan_Status Table

And you are right, I am looking for the main form to show records where there is at least one record in Indexing_loan_Status table where the logged in user worked on, and records that has never been assigned out yet.

Thank you MajP...

 

[MajP]

in a standar module

Public Function getUserName() As String
  If Not CurrentProject.AllForms("Login Form").IsLoaded Then
    DoCmd.OpenForm "Login Form", , , , , acDialog
  End If
    getUserName = Nz(Forms("Login Form").combo0, "")
End Function

qryLoggedInOrNotWorkedIDs

SELECT DISTINCT 
 Job_Tracking.Situs_ID
FROM 
 Job_Tracking 
LEFT JOIN 
 Indexing_Loan_Status 
ON 
 Job_Tracking.Situs_ID = Indexing_Loan_Status.Situs_ID
WHERE 
 Indexing_Loan_Status.Analyst = getuserName() OR Indexing_Loan_Status.Situs_ID Is Null

qryLoggedInOrNotWorked

SELECT 
 Job_Tracking.Situs_ID, 
 Job_Tracking.Project, 
 Job_Tracking.Facility_Number, 
 Job_Tracking.Requested_By, 
 Job_Tracking.Client_Sponsor, 
 Job_Tracking.Property_Loan_Name, 
 Job_Tracking.PortfolioID, 
 Job_Tracking.Date_Requested, 
 Job_Tracking.FileSourceID, 
 Job_Tracking.Specific_File_Instructions, 
 Job_Tracking.File_Completion_Requested_Date, 
 Job_Tracking.Date_Situs_Received_File, 
 Job_Tracking.Situs_Est_Date_Of_Completion, 
 Job_Tracking.PriorityID, 
 Job_Tracking.Link_To_Completed_Files
FROM 
 Job_Tracking 
INNER JOIN 
 qryLoggedInOrNotWorkedIDs 
ON 
 Job_Tracking.Situs_ID = qryLoggedInOrNotWorkedIDs.Situs_ID;

If I log in as Sam Adams the above returns records 5,7,8. Sam Worked on 5 and 7 and no one has worked on 8

 

[iuianj07]

Thank you MajP, it is working correctly!

I am trying to add something though, and am trying to tweak it myself but unsuccessful,

The above queries works correct, wherein it filters for all records that the logged in user have worked on and records that have not been assigned at all.

I want to add though that, it will show all records that logged in user has worked in the Indexing_Loan_Status sub-form OR in the ASR_Loan_Status sub-form (in the ASR Tab) and records that have not been assigned out to anyone yet.

I have tried to create a query

qryLoggedInOrNotWorkedIDsASR:

SELECT DISTINCT Job_Tracking.Situs_ID
FROM Job_Tracking LEFT JOIN ASR_Loan_Status ON Job_Tracking.Situs_ID = ASR_Loan_Status.Situs_ID
WHERE (((ASR_Loan_Status.Analyst)=getuserName())) OR (((ASR_Loan_Status.Situs_ID) Is Null));

then on the main form:

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, Job_Tracking.Property_Loan_Name, Job_Tracking.PortfolioID, Job_Tracking.Date_Requested, Job_Tracking.FileSourceID, Job_Tracking.Specific_File_Instructions, Job_Tracking.File_Completion_Requested_Date, Job_Tracking.Date_Situs_Received_File, Job_Tracking.Situs_Est_Date_Of_Completion, Job_Tracking.PriorityID, Job_Tracking.Link_To_Completed_Files
FROM (Job_Tracking INNER JOIN qryLoggedInOrNotWorkedIDs ON Job_Tracking.Situs_ID = qryLoggedInOrNotWorkedIDs.Situs_ID) INNER JOIN qryLoggedInOrNotWorkedIDsASR ON Job_Tracking.Situs_ID = qryLoggedInOrNotWorkedIDsASR.Situs_ID;

but it gives the wrong results...

I apologize if I haven't mention it all at once in my previous posts, as I was trying to get help with how to filter on one sub-form then hoping I'll be able to tweak/modify it to filter based on 2 sub-forms...

Thank you very much MajP...

 

[MajP]

you will have to do that a little differently because an inner join will give you an AND and you want an OR

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, Job_Tracking.Property_Loan_Name, Job_Tracking.PortfolioID, Job_Tracking.Date_Requested, Job_Tracking.FileSourceID, Job_Tracking.Specific_File_Instructions, Job_Tracking.File_Completion_Requested_Date, Job_Tracking.Date_Situs_Received_File, Job_Tracking.Situs_Est_Date_Of_Completion, Job_Tracking.PriorityID, Job_Tracking.Link_To_Completed_Files
FROM Job_Tracking
WHERE 
 (((Job_Tracking.Situs_ID) In (Select Situs_ID from qryLoggedInOrNotWorkedIDs))) OR (((Job_Tracking.Situs_ID) In (Select Situs_ID from qryLoggedInOrNotWorkedIDsASR)));

 

[iuianj07]

Oh, so a subquery in the Situs_ID field is needed if I wanted it to filter on 2 sub forms... Thank you very much MajP..

You have always been a great help at me..

Have you taken a glimpse of the table structure or the relationship diagram? this is my first attempt on creating a properly normalized database, as if you remember that my prior database questions, the table structure were always not properly normalized...

Thank you again,

 

[MajP]

You are "not filtering on two subforms" you are returning a the set of records that have related child records in at least one of two tables for a given user, or have no child records. The semantics makes a difference here.

However, this returns all records for Sam Adams that he worked on in either the asr or indexing table. But also any records that have not been worked in either the ASR or Indexing. So if it has no child records in the ASR table, but there is a record in the indexing table it would appear. That may not be what you want, you may only want to return the records that do not have children in either table.

If that is the case, make for queries.
qryLoggedIndexingIDs "returns ID in the indexing table for the logged in user"
qryNotInIndexingIDs "returns IDs of the Jobs not in the indexing table"
qryLoggedASRIDs "retruns IDs in the ASR table for the logged in user"
qryNonInASRIDs "returns Job IDS not in the ASR table"

Create a subquery like shown, in the criteria for values that returns records in qryLoggedIndexingIDS or in qryLoggedASIDs or in (qryNotInIndexing and qryNotInASRIDS). A little more involved.

 

[iuianj07]

Hello MajP,

I created queries:

qryLoggedIndexingIDs

SELECT DISTINCT Job_Tracking.Situs_ID
FROM Job_Tracking LEFT JOIN Indexing_Loan_Status ON Job_Tracking.Situs_ID = Indexing_Loan_Status.Situs_ID
WHERE (((Indexing_Loan_Status.Analyst)=getuserName()));

qryNotInIndexingIDs

SELECT DISTINCT Job_Tracking.Situs_ID
FROM Job_Tracking LEFT JOIN Indexing_Loan_Status ON Job_Tracking.Situs_ID = Indexing_Loan_Status.Situs_ID
WHERE (((Indexing_Loan_Status.Situs_ID) Is Null));

qryLoggedASRIDs

SELECT DISTINCT Job_Tracking.Situs_ID
FROM Job_Tracking LEFT JOIN ASR_Loan_Status ON Job_Tracking.Situs_ID = ASR_Loan_Status.Situs_ID
WHERE (((ASR_Loan_Status.Analyst)=getuserName()));

qryNonInASRIDs

SELECT DISTINCT Job_Tracking.Situs_ID
FROM Job_Tracking LEFT JOIN ASR_Loan_Status ON Job_Tracking.Situs_ID = ASR_Loan_Status.Situs_ID
WHERE (((ASR_Loan_Status.Situs_ID) Is Null));

I am a little confused though on what's the next step? Do I put these queries into the properties of the sub-form?

Thanks again...

 

[MajP]

Just to verify
You want
any Jobs that the logged in user worked in either the ASR or Indexing table
Or
any Jobs that do not have any child records in ASR AND no child records in Indexing
..........................................
Not
any Jobs that the logged in user worked in either ASR or Indexing table
Or
any Jobs that do not have any child records in ASR OR no child records in Indexing

 

[iuianj07]

Hello MajP,

I want:

any Jobs that the logged in user worked in either the ASR or Indexing table
Or
any Jobs that do not have any child records in ASR AND no child records in Indexing

Thank you very much...

 

[MajP]

test

 

[MajP]

For some reason I cannot post my whole reply. So I will post in parts. Part I

I added one more job for test purposes. It has an ID of 9. I picked Sam Adams

qryLoggedASRIDs: returns the IDs of jobs in the ASR table related to the logged in user.
5,6

qryLoggedIndexingIDs: returns IDs of jobs in the indexing table related to the logged in user
5,7

qryNotInASRIDs: (Note I changed the name from Non to Not) this returns the job IDs without records in the ASR table
9

qryNotInIndexingIDs: This returns the job IDs without records in the Indexing table
8,9

 

[MajP]

Part 2

So to make this easier I will make another simple query. If I join qryNotInSARIDs to qryNotInIndexingIDS it will return those records not in either table

qryNotInIDs

SELECT
qryNotInIndexingIDs.Situs_ID
FROM
qryNotInASRIDs
INNER JOIN
qryNotInIndexingIDs ON qryNotInASRIDs.Situs_ID = qryNotInIndexingIDs.Situs_ID;

9

 

[MajP]

I cannot post any answer for some reason. Here is a link:

http://www.4shared.com/document/87TyqLqn/Queries.html

 

[iuianj07]

Hello MajP,

thank you very much that despite having difficulty in posting answers, you still came up with other ways to help me out...

Is it also possible, to send as a link your copy of the database that you made all the changes above? that way I could compare what should I have done, and with the queries you made?

I appreciate all you help... a single star won't be enough in expressing my gratitude...

 

[MajP]

Sure.

http://www.4shared.com/document/ypnoK1Ds/UserLogin.html