Question: Master Form with Multiple Subforms Problem

[KCC47]

I am new to this forum, but I am very glad to be here.

I'm needing some help with the design of my database. The primary table has a total of six relationships with other tables: three one to many, and three many to many joined with junction tables.

Where I'm struggling is the creation of my master form. The three one to many relationships will be represented with combo boxes; all the records will be created on their own forms. But the three other many to many tables are causing issues for my main form. I want to use subforms where the user will create the records for these tables as they complete the master form.

I think my main issue is the how to link the records created in the subform to the primary table while in the master form.

I know my master form should be based on a query, but I'm also having trouble designing the correct query for it.

I'm willing to provide a copy of my DB if needed.



I'd appreciate any assistance you can provide.

[June7]

Form/subform arrangement for properly structured db should be quite simple. Follow instructions at bottom of my post to provide db. I will be able to review it after Feb 22.

[KCC47]

I've attached a copy of my Db for you to review, June7.

I appreciate your time reviewing my Db.

[June7]

Forms can be bound to tables or queries.

Explain your data some more. Can a rejected application be related to: Multiple applicants? Multiple vehicles? Multiple reasons? Shouldn't an application (rejected or otherwise) be for a single vehicle/applicant?

[KCC47]

A single rejected application can have more than one applicant, and a single application can have multiple reasons for rejection. It is not absolutely necessary to use a single rejected application for more than one vehicle, but it would be useful.

I want to develop the database for users to produce customized letters/reports.

I've struggled on creating a master form for users to record: vehicle info, applicant(s) info, and rejection reason(s).

[June7]

The master form should be set up with a subform for each of the junction tables. A combobox on each subform for selecting vehicle, applicant, reason. If vehicle or applicant or reason not in the combobox list then use the NotInList event to open a form to add the new record then requery the combobox and new record will be available as an item in the combobox.

Here is one tutorial http://www.blueclaw-db.com/access_no...ed_example.htm

[KCC47]

Well it is reassuring that I at least have got the design of my tables correct.

And that notinlist event sounds like the ticket. I'll test it when I get the chance on Monday.

One thing I'm struggling with is whether or not to make the master form bound to a table or a query. How will I know which is best?

[June7]

If you want to filter the form's recordset (such as to exclude 'closed' records) or define sort order, a query is appropriate. However, the form has properties that can define filter and sort order so that is even possible with table as RecordSource. Consider that the form properties can be modified with code so the limitation to exclude 'closed' records might be superseded. However, if this restriction is a hard-code parameter in the query, then it will persist even if code sets the form filter property to further filter the form's RecordSet.

[KCC47]

I'm trying to implement what you suggested, but I'm having issues understanding how to use this code in my Db. I'm having difficulty understanding what field names, form names, control names from my Db should replace the example code. I have limited experience with VB code; I can't recognize what I'm supposed to change in the code for my use.

[June7]

What exactly are you stuck on? Let's deal with one thing at a time.

1. set up the form/subform arrangement

2. set up comboboxes on subforms to select vehicle, applicant, reason

3. figure out code for the NotInList event

[KCC47]

That's a good idea.

I've created a main form and a subform. The main form is based on the Rejected_Application_Tbl, and the subform has a record source of the Vehicle_Junction_Tbl (I'm taking these subforms and comboboxes one at a time).

When I setup the combobox on the subform, the wizard presents me with three options: A.) I want the combo box to look up the values in a table or query. B.) I will type in the values that I want. C.) Find a record on my form based on the value I selected in my combo box.

No sure which to select, but If I had to guess it would be A.

[June7]

Did you test adding records via the form/subform setup?

Go with A. The values will come from the vehicle, applicant, reason tables.

[KCC47]

Yes, I have tested adding records, and it works. That is, if I add a record directly into the Vehicle_Tbl, I can select it in the subform of the main form. That links the records.

If I attempt to add a new record on the main form in the Vehicle_Junction_SubFrm, the following message appears: "You cannot add or change a record because a related record is required in table 'Vehicle_Tbl'."

Is the message that should be appearing at this point since we have not added the VB code for the subform?

If so, I'm ready for assistance with the VB code.

[June7]

No, you should be able to add records without code. Forms must not be set up properly. Here is a tutorial about form/subform http://office.microsoft.com/en-us/ac...010098674.aspx

The comboboxes NotInList event code is an unrelated issue.

[KCC47]

I believe I have followed these instructions, but it continues to show that message.

Would you mind taking a look at my DB again?