Solved Propagating multiple fields along a one-to-many relationship

[Duff-Man]

Hi all. I'm new to Access, so apologies if this is a rather basic question...

I'm building a database to handle contracts / inventory / stock etc. Each contract can contain several products, and I would like to propagate a few default values from the contract into the products.

I have a table "Contracts", with the "Contract ID" being the primary field. The "Products" table has its own primary field (product id), and a field "Associated Contract" to link it to the contract. The "Contract ID" has a one to many relationship with "Associated Contract". I can build a sub-form using this connection, which seems to work as expected.

I would like to additionally pass other values from the "Contracts" table to the associated records in "Products", as default values. For example, "unit of mass". In almost all cases, the unit of mass is consistent throughout the contract (e.g. all products in the contract are measured in kg). However, in some rare cases this can differ, so I would like to retain a field in the products table to allow this to be changed on an individual basis when needed. Similarly for currency, delivery location etc.

I'm unclear on how to go about this? The records themselves are already linked, so there shouldn't be any ambiguity, however I am only able to propagate the primary field?

Many thanks!

 

[arnelgp]

I would like to additionally pass other values from the "Contracts" table to the associated records in "Products", as default values.

you need Another table (Junction between contract and product).
you only need to use Query and not "propagate" information already in another table.

 

[Duff-Man]

Thanks for your help. I've been looking into it further, and can't seem to grasp what I need to do...

In this case, each product is unique, and is associated with only one order. Each order may have several products. It's a true one-to-many relationship, not a many-to-many, so the relationship *should* be direct, and one-way. I just want the value of a field in the parent record (orders) to appear as a default value in the associated child record (let's say the "currency" field; I want the currency set in the order to be applied to all products associated with that order by default).

I'm not sure how to go about this by using a query? I can't seem to establish a relationship in the database that will allow the query to operate?

Any advice would be appreciated. I've been googling like mad but not getting anywhere!

 

[plog]

I think you need a field in Contracts for [UnitOfMass] which will hold the most common unit of mass for a contract. Once that is set you can use a DLookup to get it and then use VBA to use it as the default for a combo box on the product form:

ComboBox.DefaultValue property (Access)

Office VBA reference topic

docs.microsoft.com

Or load it as the default for a text input. Then the user can override it if needed for a particular product.

 

[Duff-Man]

Thank you for the help.

I do have a field in Contracts for [UnitOfMass], and other fields that I want to propagate.

I'll look in to DLookup, and using VBA. As mentioned, I'm new to Access so it's a bit of a steep learning curve.

 

[plog]

Thinking twice about this, I don't think you need VBA, you can right click on the input, go to properties and set the Default value to the DLookup.

MS Access: DLookup Function

This MSAccess tutorial explains how to use the Access DLookup function with syntax and examples. The Microsoft Access DLookup function returns a value from an Access table (or domain).

www.techonthenet.com

That method should work regardless of input type.

 

[Pat Hartman]

You can either add the defaults to the Contracts table or create a separate table for them. When you add a new record, you can add code in the BeforeInsert event to copy the defaults from the Contract table to the fields to prefill them. I use the BeforeInsert event for things like this because I don't ever want to dirty a record before the user does. So, the BeforeInsert event runs once and it runs when the first character is typed into any control of the form for a new record so it is ideal for this use.

 

[Uncle Gizmo]

Video number 6 in my blog here:-

Move Info from One Table to Another – Nifty Access

www.niftyaccess.com

Maybe of interest.

 

[Duff-Man]

Hi guys,

I just wanted to say thank you so much for your help. I've solved the issue now, and I feel like I've got a much better handle on how things operate in Access. As a newbie to Access and databases in general (though not coding), it's been a bit of a steep learning curve. Seems to be quite a different way of thinking really.

Thank you plog for pointing me in the direction of DLookup. This is the solution I'm using at the moment, with the Beforeinsert event suggested by Pat Hartman. I also realise now that I need to learn more about SQL if I am to complete the project in an efficient and flexible way, so that's where I'm directing some of my attention.

For what it's worth, I found these links to be particularly beginner-friendly explanations of DLookup and ELookup (explaining the syntax explicitly etc.)

allenbrowne.com/casu-07.html

allenbrowne.com/ser-42.html


Thank you for the link to your blog videos Uncle Gizmo - I'll take a look at them tonight.

Thanks again guys. Really.

 

[Pat Hartman]

When you are retrieving multiple values from the same record, dLookup() is the least efficient way. Doesn't mean they won't work. They are used in a lot of examples because they are simple to understand.

If you are using a main form with a subform, you can just reference the RecordSource of the main form to pick up the default values.

In the subform's BeforeInsert event.

Me.Somefield = Me.Parent!Somefield