Question: Master Form with Multiple Subforms Problem

[June7]

I don't see any form/subform setup that involves vehicle tables.



Master/Child links properties are not correct for the subform container on Dealer_Rejected_Application_Frm. What field is Dealer_LkUp? Change this to Dealer_ID.

If you want comboboxes with code for NotInList event in subform, the subform container will have to use a form instead of table as SourceObject.

[KCC47]

I don't see any form/subform setup that involves vehicle tables.

This subform is labeled Vehcle_Junction_SubFrm, and its record source is the Vehicle_Junction_Tbl. I though you said my subforms for vehicle, applicant, and rejection reason was to be sourced on their respective junction table? So the subform should have a record source of not the junction tables?

If you want comboboxes with code for NotInList event in subform, the subform container will have to use a form instead of table as SourceObject.

Per the tutorial you gave me, I dragged my Vehicle_Junction_SubFrm from the navigation pane onto the the Rejected_Application_Frm (Master form) in layout view. Was this not correct?

[June7]

Maybe you provided the wrong db. Because the Vehicle_Junction_Subform is not placed on any parent form.

The Rejected_Application_Form does not have a subform container on it.

The only form/subform I see is the Dealer_Rejected_Application_Frm/Table.Rejected_Application_Tbl combination.

[KCC47]

That's certainly possible. I haven't figured out how to delete previous copies of the database from the forum's attachment folder.

I've redone the main form and placed the Vehicle_Junction_Subfrm on the main form. It should now be visible in all form views.

[June7]

The only way I know to remove files from forum is to create a subfolder in the attachment manager, move files into that folder, then delete the folder. However, if files are attached to post, they might not be deletable. Can edit posts to remove attachments.

Would be nice to have some records for testing.

Okay, steps 1 and 2 seem to be completed. Form/subform arrangements work and combobox works. Now implement the NotInList code. Specifically what do you not understand about this? If the link I provided in post 6 is not clear enough there are other tutorials on the topic.

In the NotInList property of the combobox, select [Event Procedure]. Click the ellipses (...) to open the VBA editor to the procedure. Type code.
Here is another example http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[burrina]

Rejection_Application_Frm is not related to Vehicle_Junction_Subfrm correctly? Master/Child Relationship is incorrect.

[June7]

burrina, I think the Master/Child links are correct. PK field ID from Rejected_Application_Tbl links to FK field Rejected_Application_ID from Vehicle_Junction_Tbl.

[burrina]

I could not confirm this since I could not manage to view the relationships for the forms in design mode, my bad! curious why in design mode you can't see the subform? Another strange one. This time however, I bow out. Just came off a whopper of a similar situation and am mentally exhausted. It's just you and him. Thanks though for pointing that out. KCC47, Good Luck with your project!

[June7]

Odd, I see the subform just fine.

[KCC47]

I've attached my Db with my attempt at adding the code, but I am struggling. If I copy and paste the code from your examples, I understand I need to replace some of the code with names of forms/controls from my Db. I simply don't know which ones I'm supposed to use. You can see my attempt in the Db.

[June7]

Call Vehicle_Not_Found(Me.Vehicle_ID_ComboBox)

Then replace acct_name_entry with Vehicle_ID_CombobBox


Also need to set the LimitToList property to Yes.


Now I remember why I struggled with this event in the past. The examples referenced don't show how to requery the combobox and retain the new input. This example deals with that http://support.microsoft.com/kb/197526. Seems the acDataErrAdded parameter handles requery of combobox and retaining the input. This simplified code seems to work in your db

CODE IN THE Vehicle_Junction_SubFrm

Private Sub Vehicle_ID_ComboBox_NotInList(newdata As String, Response As Integer)
If MsgBox("Do you want to add this vehicle?", vbYesNo, "Add vehicle?") = vbNo Then
    Response = acDataErrContinue
    DoCmd.GoToControl "Vehicle_ID_ComboBox"
    Me.Vehicle_ID_ComboBox = Null
Else
    DoCmd.OpenForm "Vehicle_Add_Frm", , , , acFormAdd, acDialog, newdata
    Response = acDataErrAdded
End If
exit_it:
End Sub

CODE IN THE Vehicle_Add_Frm

Private Sub Form_Current()
If IsNull(Me.VIN) Then Me.VIN = Me.OpenArgs
End Sub

Thanks for forcing me to get better understanding of NotInList. Think I can go fix some old code now.

[KCC47]

In what control for the Vehicle_Add_Frm does the second bit of code go? The VIN, in which event?

[June7]

It doesn't go in any control. It is the form's Current event, as indicated by the sub declaration.

[KCC47]

Okay, the vehicle subform is working perfectly on my form. Thank you!


The rejection reason is rather simple since I don't intend users to be able to add their own rejection reasons at will.

The applicant subform poses a particular challenge in my opinion. The vehicle one was simpler because it was just the vehicle identification number (VIN), but the applicant subform consists of a person's last name, first name, middle initial, and suffix. I've attempted at adapting the process you outlined for it, but it doesn't seem to want to work. Suggestions?

[June7]

Yes, names can be aggravating. Since my databases don't concern people data, never had to deal with that.

Possible approach would be to concatenate the name parts into a single column of the combobox:

SELECT ID, Last_Name_Company_Name & IIf(Not IsNull(First_Name), ", " & First_Name, "") & IIf(Not IsNull(MI, " " & MI,"") AS AppName FROM Applicant_Tbl;

Then code in the Applicant_Add_Frm would parse the name parts.

Private Sub Form_Current()
Dim aryNames As Variant
If Not IsNull(Me.OpenArgs)
   aryNames = Split(Me.OpenArgs)
   If InStr(aryNames(0), ",") > 0 Then aryNames(0) = Left(aryNames(0), InStr(aryNames(0), ",") - 1)
   Me.Last_Name_Company_Name = aryNames(0)
   If UBound(aryNames) > 0 Then Me.First_Name = aryNames(1)
   If UBound(aryNames) > 1 Then Me.MI = aryNames(2)
End If
End Sub

Rats, this falls apart for multi-part last names like von Richter or Van Der Beek. Might have to live with just opening the applicant form to a new record and user has to input the name parts instead of trying to pass name to the opening form.