Multiple items subform showing records selected in main form

[schwabe]

Hello all,

I'm trying to set up a rather complicated form.

In the main form, I have two cascading CombobBoxes. The first one lets the user choose a customer, the second one the projects between me and the customer.

Now, I'd like to add a subform giving a list of Invoices with fields Date, Name and Amount. The list should only show the invoices related to the chosen customer and project.

Moreover, I'd like to be able to add a new record in this list and this record should also make use of the choice of the project and customer in the main field.

How do I do that?

[RuralGuy]

Take it one step at a time. Where are you having a problem?

[schwabe]

Thanks for your reply.

I have set up the two combo boxes and they work just fine.

Then, I have set up the subform based on a query, where I have applied filters, e.g. Like [Forms]![frmAccount].[cboCust] &"*"
The subform shows the correct reords, I can edit records.
However, I can't add records.

Benjamin

[RuralGuy]

Instead of using filters, let the LinkMaster/ChildFields properties of the SubForm control do the filtering for you. Your SubForm RecordSource should be generic and include all records.

[schwabe]

This should be possible. Do I have to give Project and Customer in the subform again?

[RuralGuy]

Your LinkMaster/ChildFields property can contain more than one field.

[schwabe]

Another question, can I still use the query as data source?

[RuralGuy]

Not your original query because it will probably not be updateable. Again, it should be generic and select all records.

[schwabe]

Okay, I seem to come closer now to what I want.
However, I have problems with this definition:

Me!sfrmTL.LinkChildFields = "qryTL.lnkOEM, qryTL.txtProjectName"
Me!sfrmTL.LinkMasterFields = "qryTL.lnkOEM, qryTL.txtProjectName"

When I run the related ComboBox the first time, I get fields asking for the fields name values...

[RuralGuy]

You do not inclute a table name with the field name. Why are you using code? I would put it is the properties in design view. It is static isn't it?

[schwabe]

I get an error saying: "Can't built a link between unbound forms."

[RuralGuy]

Ahh...Haa!! Why are the form unbound?

[schwabe]

There is no reason, I started with a blank one and, up to now, I never needed to add a record source...

I guess, I have to take a step back and think about how to set up the master form properly (that was the unbounded, the subforms where all bounded to a query)

[RuralGuy]

You can set a control as a LinkMasterField if you want. You just need to use the correct syntax. http://access.mvps.org/access/forms/frm0031.htm

[schwabe]

Hi Rural Guy,

so, I have been able to set up the structure you suggested.

In the main fraim I have the Project, Customer and Contact Name displayed with TextBoxes.
In the subform I find for each Project the corresponding order timeline and I can step through all projects individually by using the record selectors at the bottom of the form. So far, so good

What I'd like to be able to do next, is to select the project in the mainform with a combobox and all the other information should update automatically. How do I do this?