Help with Tracking Database Form

[June7]

1. don't include the Applicant_Tbl in the form RecordSource - and correction, still an editable form but cannot display applicant info without some fancy VBA code

2. Why is the code in there? Is there a button somewhere that opens the form to "Add New Applicant"? Don't use code in the Open event to move to new record, use the code (embedded macro?) that opens form to open in new record mode.

4. these are edits to the [Applicant_Search_Qry]
SELECT Applicant_Tbl.Application_ID, Applicant_Tbl.ID, Applicant_Tbl.First_Name, Nz([MI],"") AS MidIn, Applicant_Tbl.Last_Name_Company_Name, Nz([Suffix],"") AS Suf
FROM Applicant_Tbl
WHERE (((Applicant_Tbl.First_Name) Like "*" & [Forms]![Applicant_Search_Frm].[Query_First_Name] & "*") AND ((Nz([MI],"")) Like "*" & [Forms]![Applicant_Search_Frm].[Query_MI] & "*") AND ((Applicant_Tbl.Last_Name_Company_Name) Like "*" & [Forms]![Applicant_Search_Frm].[Query_Last_Name_Company_Name] & "*") AND ((Nz([Suffix],"")) Like "*" & [Forms]![Applicant_Search_Frm].[Query_Suffix] & "*"));

[KCC47]

1. That isn't really an option, so I guess I have to live with it appearing twice.

2. Fixed

4. Fixed, although I also made the first_name and last_name_company_name possible for null values too.

New issues:

1. The vehicle_search_frm lists in the application_tbl_subform1 the following: application_ID, status, dealer_ID, Last_Name/Company, and First_Name.

Is there a way I can add a control to filter the status in the main vehicle_search_frm. For example, I enter a VIN and then I select that I only want to see applications that haven't been completed...

2. The applicant_search_frm opens the applicant_search_qry on pressing enter, is there a way it can open the query in form view. Or can it open the query on the applicant_search_frm as a sub_report or form?

[June7]

New 1. The subform RecordSource can be a parameterized query, just like on the main form. The query parameter would reference the control on the main form the same way. There are other methods but involve more code, not much more. Could use code to set Filter and FilterOn properties of form.

New 2. Queries don't have a 'form view'. Yes, table or query can be the SourceObject of a subform container control.

[KCC47]

2. Solved

1. Could you walk me through creating a query parameter on the form, or is there a guide or tutorial. I'm trying to accompish the following:

I need to filter out applications that have a status of either 'rejected to dealer' or 'completed', a button to push or other option would work best.

3. How does one establish a textbox on a form that seves as the input for a parameter query? I need it to work for dates, office locations, status?

[June7]

1. You already have an example of parameterized query with the DataPig tutorial. Alternative is code to set the Filter and FilterOn properties of the subform http://msdn.microsoft.com/en-us/libr...ffice.11).aspx.

3. See DataPig examples http://datapigtechnologies.com/AccessMain.htm
There are 3 on that page including the one already provided
If you want one textbox to serve for any single input of any value type, is very tricky, especially involving dates.

[KCC47]

I have created a new form, "Application_Search_Qry_Frm," that will serve my purposes as far as filtering. It allows me to filter the subform at will; for instance, I can select all, blanks, or each individual dealer. Why is it that only this subform allows the full filtering functionality? All the others onlyl allow me to create custom text filters like on the "Applicant_Search_Frm" or rather the form that it opens on query, "Applicant_Search_Qry_Frm."


The form I am having the most trouble with now is the "Office_Search_Frm." Let me explain what I am needing here:

I query by office, and it returns all records with matching Tracking_ID's and Application_ID's.

The query I have already setup for the "Tracking_Tbl subform" has a criteria to only return records with a status of "awaiting processing" and "applicant contacted."

If you inquire on the main office, you will see four matching records in the subform. There is one record for application 3 and three for application 4. My main goal with this form is to return records where the most recent tracking_ID is at this office. In other words, I don't need to see the history of the application at this office. I only want to see the most recent receipt of the application at this office.

I found this resource: http://office.microsoft.com/en-us/ac...96309.aspx#BM4, but I'm not certain that it will help me in this case.

Would this be of any benefit to me?

[June7]

I tried your new Application_Search_Qry_Frm and it just errors when I click the button.

In the Office_Search_Frm, I presume you want to show the most recent record for each application ID and still have an editable form. That source you found doesn't really show you how to accomplish.

One way is with DMax in the form's RecordSource query. Under the Tracking_Date field, filter criteria:
=DMax("Tracking_Date","Tracking_Tbl","Application_ ID=" & [Tracking_Tbl].[Application_ID])

[KCC47]

I had forgotten to delete that filter command button; it never functioned.


I have added the filter criteria you suggested, but I am getting this error:

Syntax error (missing operator) in query expression 'Application_ID=2'.

That appears if I try to run the Tracking_Tbl subform on its own, it changes to 'Application_ID=1' if I run the Office_Search_Frm.

I did have to add the 'tracking_date' as a field to the record source query because it wasn't there previously.

[June7]

You put the criteria in the Tracking_Tbl subform RecordSource? The Tracking_Date was already in the RecordSource because of the field wildcard (*), dragging it individually to the field grid means it is there twice so uncheck the Show row for the one with the criteria so it won't actually show as a field with label Expr1 in the RecordSource. The criteria seems to be working for me - no error message anyway. Show your exact SQL statement.

However, that expression returns the max date for an application regardless of office or status. This means Application_ID 4 will show 4/7/2013 as the maximum date even though that application is associated with 2 offices, so regardless of which office is selected by the filter, the max date will be 4/7/2013.

Also, Application_ID 3 has 2 tracking records for office 3 on the same date so both records will show.

[KCC47]

SELECT Tracking_Tbl.*, Tracking_Tbl.Tracking_Type, Application_Tbl.Application_ID, Application_Tbl.Vehicle_ID, Application_Tbl.Dealer_ID, Application_Tbl.Status
FROM Application_Tbl LEFT JOIN Tracking_Tbl ON Application_Tbl.Application_ID = Tracking_Tbl.Application_ID
WHERE (((Tracking_Tbl.Tracking_Date)=DMax("Tracking_Date ","Tracking_Tbl","Application_ ID=" & [Tracking_Tbl].[Application_ID])) AND ((Application_Tbl.Status)="Awaiting Processing" Or (Application_Tbl.Status)="Applicant Contacted"));

[June7]

This version considers the Office_ID in the DMax expression and it also does not error for me.

SELECT Tracking_Tbl.*, Tracking_Tbl.Tracking_Type, Application_Tbl.Application_ID, Application_Tbl.Vehicle_ID, Application_Tbl.Dealer_ID, Application_Tbl.Status FROM Application_Tbl LEFT JOIN Tracking_Tbl ON Application_Tbl.Application_ID = Tracking_Tbl.Application_ID WHERE (((Application_Tbl.Status)="Awaiting Processing" Or (Application_Tbl.Status)="Applicant Contacted") AND ((Tracking_Tbl.Tracking_Date)=DMax("Tracking_Date" ,"Tracking_Tbl","Application_ID=" & [Tracking_Tbl].[Application_ID] & " AND Office_ID=" & [Forms]![Office_Search_Frm]![Office_ID])));

[KCC47]

Okay, I finally got that to work.

I'm not so concerned with Application_ID 3 showing two records for office 3 because one is a receive and the other is a send; I can just set a new criteria for tracking_type to "receive."

Do you have another suggestion to address the other issue, because it will make this earch form useless. I'm seeing the same issue as you are. It is showing the max date for that application at that office regardless if there is a more recent max date at another office. Why is that? Is it something to do with the way I designed the tracking_tbl itself?

[June7]

Now I'm not sure what you want, getting confused.

My last suggestion is an attempt to synchronize the DMax criteria with the filter applied on the main form, which I forgot to consider in the first offering.

If you want to retrieve the record that has max date for an application regardless of the Office filter criteria applied to the main form, that is not logical for this form/subform.

Use another subform or a listbox that will display the max date record for the application independent of the office filter.

If you just want to show the max date and not the entire record, that can be done with DMax function in a textbox on the main form.

[KCC47]

After thinking this through again, I have realized DMax will work best as a criteria for the tracking_ID and not the tracking_date. The max tracking_ID for a application_ID is really what I'm trying to get at; the latest tracking status. Therefore, I've modified the DMax criteria accordingly for the tracking_ID, and it is working perfectly.

I'm attempting to add conditional formatting to a column on a subform. Do you have a resource I could use to find formulas/expressions for conditional formatting for dates?

[June7]

Conditional expressions in queries and textboxes use the IIf() function. Search Access Help for a start.