Help with Tracking Database Form

[KCC47]

I'm not sure if it is something I have done, but the values I enter in the Applicant_Junction_SubFrm and Vehicle_Junction_SubFrm ComboBoxes on the Master form dissappear after I reload the record. It isn't storing these anymore.

[June7]

For new record on main form, have to enter data into fields on main form. Not seeing any other than Status. New record of main form is not getting committed to table so there is no Application_ID for the subforms to capture. Application form opens to a new record but without data entry record is not initiated and therefore no record created.

The Status field in table is set with DefaultValue. This causes the field to show "Awaiting Processing" for new record but this is not initiating record. Have to select item from combobox list to inititate record. The alternative is code to immediately save the new record when form opens.

How do you expect the VIN search box to work? The vehicle data is not on the main form.

[KCC47]

That makes sense I suppose. Although I can easily move the Dealer_ID_ComboBox ahead of the subforms to correct the issue, I'd like to consider adding the code you mention.

Although I definitely don't understand this concept of searching for a value contained on a subform, I need a way to search for applications by applicant name, applications by dealer name, applications by vehicle information, and applications by office.

I've attempted to do this using the Applicant_Frm with the Applicant_Frm_Applicant_Junction_SubFrm and the Applicant_Search_Frm. Naturally, this isn't working for me.

[June7]

Making the Dealer box the first control and assigning it TabOrder 0 should help. Also, could have the subforms only enabled when Dealer field is populated.

If you want to do searches of data from more than one table, need a query that joins tables. This form is not appropriate for search by VIN or Office.

[KCC47]

I'm guessing I need to post a question about that in the query forum. I definitely need to see into that.

[June7]

Here is one technique for filtering records http://datapigtechnologies.com/flash...tomfilter.html

However, that will not be applicable to this data entry form because of the VIN 'many' table that would have to be included in query.

[KCC47]

In an attempt to address ongoing issues with this database, I have conceded on a number of fronts:

1.) I have changed the relationship between the Application_Tbl and the Vehicle_Tbl to one to many, with the vehicle being on the one side and the application being on the many side.

2.) I have changed the relationship between the Application_Tbl and the Applicant_Tbl to one to many, with the application being on the one side and the applicant being on the many side.

This ends the many to many relationship I had in this database. Will/should this address the ongoing issue I had with no ability to use effective search functions?

Would you please check the relationships I have created for the applicant table? I think it is somewhat awkward that way I have it, but a single application can have more than one applicant. Although an applicant can have more than one application, it is not realistic to expect the end users in this situation to check for an existing account/applicant before creating new records. With this kind of relationship, how will I add new applicants from the master form?

I apologize that I am just now realizing that many to many relationships may be technically correct but impractical.

[June7]

What is the master form?

Each application can have more than one applicant? If the same person comes back for another vehicle will just enter another record in Applicant_Tbl? This means a form bound to Applicant_Tbl with a combobox to select an application OR a form bound to Applications_Tbl and a subform to enter applicants.

[KCC47]

Application_Frm is the master form.

Yes, each application can have more than one applicant, i.e. husband and wife. I don't think its realistic for the end user to search to make sure there is not an existing record for a person before they enter a new record. What happens if we have a name come up for which there is already an entry, but they are not the same person? How would you know? You can't prevent the end user from erroneously selecting an applicant that isn't actually assocated with the application.

I'm going to have to opt for a form bound to Application_Tbl and a subform to enter applicants. After entering new applicant's names, how does one link the Application_ID with the Applicant_ID? It seems like I remember taking an extra step to link the parent and child fields.

[June7]

Yes, it would be some effort to verify returning applicant - address, SSN, DOB, same stuff needed for a credit check.

The master/child links properties of the subform container control will synchronize the related records. Initiate subform record and the Application_ID pk will automatically save into the corresponding fk field in Applicants_Tbl.

[KCC47]

Okay, I have that functioning well.

My primary issue now is a search form. The majority of searches are going to by by VIN, so I have created a preliminary form called Vehicle_Search_Frm. I think I have created an unbound combobox at the top of the form to enter a VIN, but this doesn't appear to be functioning.

After finding the vehicle, I intend to be able to select a matching application in a subform. I need to figure out how to use a hyperlink to open the Application_Frm on that record.

[June7]

Need code behind the combobox to filter form or move to desired record.

Here is example of one method to filter form: http://datapigtechnologies.com/flash...tomfilter.html

Filter the main form and the subform will synchronize. Can a VIN be associated with more than one application?

Hyperlinks open web pages or files, not forms or reports in Access.

[KCC47]

I've got that working, I believe.

Yes, a VIN can be associated with more than one application. It would be rare, but certainly possible as vehicles are sold and registered again.

The attached copy of the db has some sample data.

I have three questions:

1.) I have added a subform to the Vehicle_Search_Frm that shows the status, dealer, and applicant name of the application. If you have two applicants for one application, it shows the application twice in this subform. Is there a way to prevent this?

2.) Also to this Vehicle_Search_Frm, I have made the application_ID in the subform appear as a hyperlink that opens the application_frm. But it automatically opens this form onto a new record. How does one have it open only to the matching record?

3.) In the application_frm, I have made the tracking_ID a hyperlink that opens the tracking_entry_frm. Users will need this to edit tracking information as needed. It does open the correct form, but it opens each of the tracking records for the application; I have to cycle through each one. Shouldn't it be filtering those to show the one with the matching tracking_ID?

4.) Using the example on datapig, I have created a applicatoin_search_frm, but it is completely non-functioning. It is using the applicant_search_qry.

[June7]

1. Not if you want to be able to do data entry/edit in the subform.

2. Form goes to new record because of code in its Open event.

3. The WHERE CONDITION in the macro has criteria for Application_ID, not Tracking_ID

4. The Datapig tutorial assumes every criteria field will have data for every record. In your table the MI and Suffix fields don't always have data and will be Null. This filter can't handle Null and records with Null aren't retrieved. Change the MI and Suffix fields to expressions and apply criteria to those constructed fields:
MidIn: Nz([MI],"")
Suf: Nz([Suffix],"")
Using textboxes instead of comboboxes for input. If you want the search to find the input anywhere in the value string, need wildcard at beginning and end:
Like "*" & [Forms]![Applicant_Search_Frm].[Query_First_Name] & "*"

[KCC47]

1. I have no intention for end users to be able to do data entry on this subform, so is there a solution in that case?

2. I had gathered that would be the case, is there a way to overcome it? Would creating a copy of the Application_Frm without that open event do the trick?

3. Fixed

4. I'm afraid I don't understand your instructions. In what object am I doing all of this, the Applicant_Search_Qry and/or the Applicant_Tbl?