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,

 

[iuianj07]

Thank you again MajP...

 

[iuianj07]

Hello,

I have a follow up question,

I have a frmNewTransaction wherein we enter the data to this form whenever we receive a new loan/deal to work on, it is bound to 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;

On the field though for ASRStChID (Combo box with control source ASRStChID, Row Source: ASR_Status_Change)

Is there a way, that whenever we receive a new deal that they would request an ASR to work on, that when I choose from the Combo Box "Request Received" (it is defaulted to Not Required) from the frmNewTransaction, that when the user opens frmJob_Tracking, they will see the from the ASR Loan Status Form for that particular loan that the status is "Request Received"?

Thank you very much

 

[MajP]

Not sure if I understand.
Before a jobtracking had many ASR_LoanStatus. So there was a Situs_ID foreign key in the ASR_LoanStatus. Has that relationship changed? Now you have a foreign key in your main table "Job_Tracking.ASRStChID".

The only thing I can guess is that if when you create a new job_tracking and select
set the ASRStCHID to "Request Received" you also want to create a new child ASR_LoanStatus with its ASRStCHID set to "Request Recieved".
If that is the case you can do an insert query when you close the new transaction form.
if ASRStCHID = request recieved then insert into the ASR_loanStatus a new record where Situs_ID = the new Situs_ID for that job tracking and the ASRStCHID = request recieved.

 

[iuianj07]

you are correct MajP, when we create a new Job_tracking and set the ASRStChID to Request Received, it will automatically create a new child ASR_LoanStatus with the Request Received status.

Not sure on how to create the insert quesry though?

I tried

INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID )
SELECT Job_Tracking.Situs_ID, Job_Tracking.ASRStChID
FROM Job_Tracking INNER JOIN ASR_Loan_Status ON Job_Tracking.Situs_ID=ASR_Loan_Status.Situs_ID;

and set it on the On Close property of frm_NewTransaction, and every time I close that form, it will run the query and ask that an append query will be ran if i want to continue etc...

please help me in the right direction.

thank you

 

[MajP]

Untested, but guessing here.

dim SitusID as long
dim ASRStID as long 'I assume there is a key not the actual text
dim strSql as string

situsID = nz(me.sometextbox,0)
ASRStID = nz(me.someCombo,0)

if situsID <> 0 and ASRSTID = somenumericvalueRepresentingRequestReceived
strSql = "INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID ) Values "
strSql = strSql & "(" & situsID & "," & ASRSTID & ")"
'comment out after testing
msgbox strSql
'comment the following in if the sql looks good
'currentdb.execute strSql
end if

 

[iuianj07]

Hello MajP,

Sorry for the late reply, our office sent us home early Thursday in preparation for New Year's Hope you had a good New Year too...

I tried the code you gave me, and it didn't create a new child record on the ASR_Loan_Status table.

I am confused though on the your comment here"

dim ASRStID as long 'I assume there is a key not the actual text


I think I might have something wrong on the Control Source of the frm_NewTransaction? The form is bound to 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;

Where ASRStChID is a Long Integer... Should I have done a different approach in the table or the control source of frmJob_Tracking?

Thank you very much

 

[MajP]

Can you show your actual code?

Does the messagbox pop up? This will tell me if the event is firing and the if check passes.
If so paste the strSql.

All I was saying is that I assume that ASRSTID is equal to some number not to a string. I assume there is some number primary key associated with the "Request Received". I could pop open the database and verify this myself, just did not have time.

You may want to add this for debug purposes
debug.print "situsid: " & situsID & " ASRSTID: " & ASRTID
if situsID <> 0 and ASRSTID = somenumericvalueRepresentingRequestReceived
strSql = "INSERT INTO ASR_Loan_Status ( Situs_ID, ASRStChID ) Values "
strSql = strSql & "(" & situsID & "," & ASRSTID & ")"
'comment out after testing
debug.print strSql
msgbox strSql
'comment the following in if the sql looks good
'currentdb.execute strSql
end if

 

[iuianj07]

Hi MajP,

Here is the code:

Option Compare Database

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

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

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

And no messagebox popped up whenever I save the record from the form, or whenever I close the form...

Thank you for your help

 

[MajP]

if no message box pop us then that means that the code never gets inside the if check. So either the event is not firing at all or your if check fails.
1) Either the event is not happening
So check the on close event property is set to [Event Procedure] in form design
2) or
If SitusID <> 0 And ASRStID = 2 Then
is not true
so debug the values of SitusID and ASRSTID

 

[iuianj07]

Hello,

so I have this code on the on close event property and it is set to [Event Procedure] in form design:

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

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

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

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

Not sure though on how I could debug this one? Do you mind if you give me instructions on how I could debug this one? And what should I expect in seeing as results?

I tried to go under Debug -- Compile Loan Sale Project Tracking

but nothing is happening...

Thank you again for your patience

 

[PHV]

Debug.Print "situsid: " & SitusID & " ASRSTID: " & ASRTID
So, what is displayed in the immediate window (Ctrl-G) ?

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

 

[MajP]

The vbe provides a lot of ways to help debug your code. In this case we are looking for logic errors not run-time errors. Here is a pretty good primer

http://www.cpearson.com/excel/Debug.htm

So I personally use a lot of debug.print statements or message boxes to show me the values of my variables and where I am at in the code. For example if I build a sql string I will print it out before ever trying to execute it. I might put a message box to show if I make it inside an if then check.

http://msdn.microsoft.com/en-us/library/aa716276(v=vs.60).aspx

So what I expect to see for:
Debug.Print "situsid: " & SitusID & " ASRSTID: " & ASRTID
is
situsID: 0 ASRSTID: 2

But I doubt it because if that was the case then the if check would pass and my message box should pop up.

 

[iuianj07]

Hello MajP and PHV

Thanks for the guidance, so what I did was to hit Ctrl G to show the immediate window, and pasted

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

then when I went back to the form to create a new record, with an ASR Request Received, I looked at the code again, and this showed up on the Immediate window:

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

situsid: 1 ASRSTID:

So it didn't give the results you were expecting to see MajP?

What can I do to remedy this?

Thanks

 

[MajP]

in the vbe go to "Tools", "options" and select "Declare Variable Declaration"
This will put at the top of all new modules
Option Explicit
Paste this at the top of existing modules: Option Explicit
This will require you to declare a variable before using it. It is the absolutely most idiotic thing about vb/vba that you can use a variable name without declaring it.

This would have caught part of the problem. I made a typo
it should be ASRSTID not ASRTID. There is not ASRTID and thus prints nothing
Debug.Print "situsid: " & SitusID & " ASRSTID: " & ASRTID
& ASRSTID

So unfortunately this is not yet answering everything, except it is returning a valide SitusID. Now need to know if you are getting a valid ASRStID. Obviously not because the if check fails.

 

[iuianj07]

Hello MajP,

I did the following instructions above, so after that, I created another record from the frm_NewTransaction and Set ASR Received to '2' which is the primary key for Request Received.

After I saved and closed frmNewTransaction, I checked the vbe again and the results in the Immediate Window is:

situsid: 1 ASRSTID: 1
situsid: 1 ASRSTID: 1

What next step can we do? Thank you for helping me out...

 

[MajP]

For the Status combo box. Need you to verify these.
Obviously the first question is, Are you positive that the RequestRecieved status ID is 2 not 1?
If yes then, provide the following.
Verify the status combo name:
verify its rowsource:
verify it bound column:

On the change event of your status combo put
msgbox yourComboBoxName.value 'I assume the name is simply "status"

then change to Request recieve and see if it shows 2.

 

[iuianj07]

Hello,

Yes, I am positive that the Request Received Status ID is 2...

The Status Combo Name: Status
Rowsource: ASR_Status_Change
Bound Column: 1
Default Value: 1 (in case this helps...)
Column Count: 2
Column Widths: 0";1"

ASR_Status_Change table (just to verify all)

ASRStChID Status
1 No Data Required
2 Request Received
3 Entry in Process
4 Entry Complete
5 QC in Process
6 QC Complete
7 Sent to Client


I also entered the code on the change event on the combo box, and when I chose Request Received, the message box popped up as 2...

Thank you for your patience in helping me out....

 

[MajP]

any chance you could post the database? I use 4shared.com. If the database is very big then just create a blank database and import just this form, the ASR_Status_Change and the loan_status_table.

 

[iuianj07]

Hello MajP,

Thank you, here is the link:

http://www.4shared.com/file/mZ4XJI6H/DB_online.html

I tried it again earlier, and I think the debug on the Immediate window says situsid 1 asrstid 2, but still when I looked at frm_JobTracking, it still doesn't automatically add the child record...

And I also wanted to do the same thing with File Source Sub Form, that whenever we choose a File Source from frmNewTransaction, then it would automatically create a child record on the File Souce Sub Form on frmJob_Tracking.

I thought that after you help me with the ASR creating a child record, I could try the File Source for myself, but in case I mess it up again and ask you over and over, then maybe you could help me out as well...?

Thank you very much, I tried to sanitize data as much as I could, and I apologize if I missed something...

Thanks

 

[MajP]

when I write sql string in code:
I write the string.
I check it first using a debug or message box
If it is a good string then I will try to run/execute.

So my structure is always

dim strSql as string
strSql = "some sql string"
debug.print strSql
'or Msgbox strSql
'currentdb.execute strSql
At this point the execute is commented out and will not run. Now I run the code and check the debug. If it looks good I change my single quotes (') to

dim strSql as string
strSql = "some sql string"
'debug.print strSql
'or Msgbox strSql
currentdb.execute strSql

Now try to execute with a verified string

So your only real problem was that you never uncommented the execute. My message was a little cryptic
'comment the following in if the sql looks good
'CurrentDb.Execute strSql
So the execute did not run. Should have been
CurrentDb.Execute strSql
not
'CurrentDb.Execute strSql

Now the other problem was my fault. When you close a form several other events happen. When you close a form, the following sequence of events occurs for the form: Unload ? Deactivate ? Close
It actually unloads the data. So your situs_ID, and Status would not return the correct value for the current record. You actually need to use the unload event

I prefer to put all my code in their own procedures and then have the events call the procedures. This allows multiple events to call the same procedure. So I added a close button to the form to demonstrate. Or I could add the current event.

Private Sub cmdClose_Click()
  If Nz(Me.Status, 0) = 2 Then
    Call createReqReceived
  End If
  DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Unload(Cancel As Integer)
  If Nz(Me.Status, 0) = 2 Then
    Call createReqReceived
  End If
End Sub

Public Sub createReqReceived()
 Dim SitusID As Long
 Dim ASRStID As Long
 Dim strSql As String
 
 SitusID = Nz(Me.Situs_ID, 0)
 ASRStID = Nz(Me.Status, 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 & ")"
   'comment out after testing
   MsgBox strSql
   'comment the following in if the sql looks good
   CurrentDb.Execute strSql
 End If
End Sub

However, in this case you have to pick one or the other. Because if you pick the command button then it will call the code, but when the form closes it calls again. However, I think you have to ensure that no records already exists for that SitusID with a status of 2. Usually this is done with a dcount. You use a dcount and if the count is 0 for that situsID and status = 2 then run your insert query.