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,

 

[MajP]

Please look at those links from 3 Jan on debugging. You would save yourself a whole lot of time by understanding how to debug your code. It will really make you a much better database developer.

 

[iuianj07]

Hello MajP,

thanks for all the help... I think it is adding child records now when I uncommented the execute.

There is one problem that arose though...

Ok after we received a loan to work on, and an ASR is needed, we will choose Request Received from frmNewTransaction, and now it is creating a child record on frm_JobTracking.

the problem now is with 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.

Meaning that with what you helped me with before, when we received for example loan 111 and is ASR Request Received, and when a different user, logs in frmLogin and opened the frm_JobTracking,loan 111 wouldn't show up in their queue, because ASR Loan Status sub Form has a child record, but is not assigned (Analyst field) to anyone or the user who logged in? And what you helped me with to setup frmJob_Tracking is to show records that either have been assigned to the user who logged in or records that doesn't have any child records on both Indexing and ASR loan Status sub form... So this could be a business proccess logic error on my part? Was I confusing in explaining?

I hope I made sense, and not that hard to fix?

I will read the links you sent me on Jan 3 and hopefully it improves my debugging skills, I apologize if I always sound like such a newbie on this field...

Thanks...

 

[MajP]

Sound like you may need to modify the query to show any of the three
1)Show any record without child records
2)Show any record with child records assigned to the logged in user
3)Show any record that has child record of request received and analyst not assigned.

Does the buisness rule require that you assign an analyst before a status changes.

 

[iuianj07]

Hello,

Yes, for the Indexing Loan Status Sub-form, we should assign the analyst first, user will look for a loan that has never been worked on yet/doesn't have a child record, then on the Indexing Loan Status sub-form, I made the Analyst combo box to default the name of whoever logged in, then, the user will change the status to Request Received.

So i guess that we need to create another query to show records that has child record of request received and analyst not assigned then join it in the union query...?

Thanks,

 

[iuianj07]

Hello MajP,

Okay so I made another query:

qryASRSt2

SELECT 
 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) Is Null) AND ((ASR_Loan_Status.ASRStChID)=2));

then on frmJob_Tracking

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 uQryLoggedIDs) Or (Job_Tracking.Situs_ID) In (Select Situs_ID from qryNotInIDs) Or (Job_Tracking.Situs_ID) In (Select Situs_ID from qryASRSt2)));

Let me know if there's something wrong with what I did, in case you could see some loopholes that I might have missed.

thanks...

 

[PHV]

In qryASRSt2 the LEFT join is defeated by the clause (ASR_Loan_Status.ASRStChID)=2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

so should it be only an Inner Join instead?

Thanks

 

[PHV]

should it be only an Inner Join instead?
Depends of what you want to retrieve.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

I tried to change it to Inner Join, and it gives the same result as the Left Join? Which are records that is ASRStChID = 2 and Analyst is Null? let me know if despite giving the same result that there is an error on the query.

Thanks

 

[iuianj07]

I try to modify the code to have the Insert Query for ASR to also apply in the File Source subform, that on the frmNewTransaction, if I change status on the File Source, then it'll create also a child record on the File Source Sub form on frmJob_Tracking.

I tried to modify it to:

Private Sub Form_Unload(Cancel As Integer)
Dim SitusID As Long
Dim ASRStID As Long 'I assume there is a key not the actual text
Dim strSql As String
Dim FileSourceID As Long

SitusID = Nz(Me.Situs_ID, 0)
ASRStID = Nz(Me.Status, 0)
FileSourceID = Nz(Me.File_Source, 0)

Debug.Print "situsid: " & SitusID & " ASRSTID: " & ASRStID

If SitusID <> 0 And ASRStID = 2 Then
  strSql = "INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID ) Values "
  strSql = strSql & "(" & SitusID & "," & ASRStID & ")"
  CurrentDb.Execute strSql
  'comment out after testing
  'MsgBox strSql
  'comment the following in if the sql looks good
  If SitusID <> 0 And FileSourceID <> 0 Then
  strSql = "Insert INTO File_Source_x_Loan ( SitusID, FileSourceID) Values "
  strSql = strSql & "(" & SitusID & "," & FileSourceID & ")"

  CurrentDb.Execute strSql
End If
End If


End Sub

I tried to test it twice and it looks like it is appending the right records into the right tables etc...

Let me know if there's still something wrong or a loophole that I haven't noticed.

Thank you guys...

 

[PHV]

change it to Inner Join, and it gives the same result as the Left Join
As expected, due the WHERE clause.
If you didn't want rows from Job_Tracking without corresponding Situs_ID in ASR_Loan_Status then keep the INNER JOIN, otherwise tell us.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

I want to show records that has child record of request received and analyst not assigned..?

Thanks..

 

[PHV]

show records that has child
So, use an INNER JOIN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Thanks PHV...

 

[iuianj07]

Hello guys,

I have a follow up question though...

So whenever I create a new record in frmNewTransaction, and I put focus on a record that has an ASR Request Received, it automatically creates a new record in the ASR_Loan_Status table that duplicates the record for ASR Request Received.

Is there a way that when a record has already been created and has an ASR Request Received that it would not create a new record once I unload frmNewTransaction?

Since this thread has been very long and might take time to scroll through this thread to look at the vba or query bound to the form: i'll just post it again:

frmNewTransaction: Form is bound on this query:

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Property_Loan_Name, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, 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, Job_Tracking.ASRStChID
FROM Job_Tracking;

Then on the UnLoad event:

Private Sub Form_Unload(Cancel As Integer)
Dim SitusID As Long
Dim ASRStID As Long 'I assume there is a key not the actual text
Dim strSql As String
Dim FileSourceID As Long

SitusID = Nz(Me.Situs_ID, 0)
ASRStID = Nz(Me.Status, 0)
FileSourceID = Nz(Me.File_Source, 0)

Debug.Print "situsid: " & SitusID & " ASRSTID: " & ASRStID

If SitusID <> 0 And ASRStID = 2 Then
  strSql = "INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID ) Values "
  strSql = strSql & "(" & SitusID & "," & ASRStID & ")"
  CurrentDb.Execute strSql
  'comment out after testing
  'MsgBox strSql
  'comment the following in if the sql looks good
  If SitusID <> 0 And FileSourceID <> 0 Then
  strSql = "Insert INTO File_Source_x_Loan ( SitusID, FileSourceID) Values "
  strSql = strSql & "(" & SitusID & "," & FileSourceID & ")"

  CurrentDb.Execute strSql
End If
End If


End Sub

Any help is greatly appreciated.

Thank you

 

[MajP]

do a dcount to check if a record already exists. This is pseudo code because do not have the time to look at the real names, but something like

Dim strWhere as string
dim cnt as integer
strWhere = "Situs_ID = " & me.Situs_ID & " AND ASRStChID <> 2"
debug.print strWhere
'make sure the strwhere looks good
cnt = dcount("Situs_ID","ASR_Loan_Status",strwhere)

if cnt = 0 then
your code here
end if

 

[MajP]

also index the table so you have to have unique combinations of

SitusID and ASRstChID

that way it is impossible to have
SitusIS ARSstChid
1 2
1 2

 

[iuianj07]

not sure what I did wrong, but I modified the code, but it still does the same thing...

Private Sub Form_Unload(Cancel As Integer)
Dim SitusID As Long
Dim ASRStID As Long 'I assume there is a key not the actual text
Dim strSql As String
Dim FileSourceID As Long
Dim strWhere As String
Dim cnt As Integer

strWhere = strWhere = "Situs_ID = " & Me.Situs_ID & " AND ASRStChID <> 2"
Debug.Print strWhere
cnt = DCount("Situs_ID", "ASR_Loan_Status", strWhere)
If cnt = 0 Then

SitusID = Nz(Me.Situs_ID, 0)
ASRStID = Nz(Me.Status, 0)
FileSourceID = Nz(Me.File_Source, 0)

Debug.Print "situsid: " & SitusID & " ASRSTID: " & ASRStID

If SitusID <> 0 And ASRStID = 2 Then
  strSql = "INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID ) Values "
  strSql = strSql & "(" & SitusID & "," & ASRStID & ")"
  CurrentDb.Execute strSql
  'comment out after testing
  'MsgBox strSql
  'comment the following in if the sql looks good
  If SitusID <> 0 And FileSourceID <> 0 Then
  strSql = "Insert INTO File_Source_x_Loan ( SitusID, FileSourceID) Values "
  strSql = strSql & "(" & SitusID & "," & FileSourceID & ")"

  CurrentDb.Execute strSql
End If
End If
End If

End Sub

also, What do you mean by index the table? On my ASR_Loan_Status table, I have my ASRSTatusID (PK) as Indexed = Yes(No Duplicates) and Situs_ID as Indexed = Yes(Duplicates OK) which should be correct since there will always be the same Situs_IDs that goes through different statuses.

thank you and forgive me for my ignorance in debugging code.

 

[PHV]

I'd use this:
strWhere = "Situs_ID = " & Me.Situs_ID & " AND ASRStChID = 2"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

http://thedailyreviewer.com/office/...-over-two-fields-in-an-access-table-107207646