Auto-Creating Records in VBA

[Greyowlsl]

Hello,

I have 3 tables:
Product
Part
Allocation

The Allocation table has 4 fields:
Pkey (Autonumber)
Product_Index (Index from product table)
Part_Index (Index from part table)
Quantity (Manually entered)

When I create a new part (with new part index) I need a record created in the allocation table for each unique product, and the part index field also autopopulated with the new part index eg.
Product1..........Part39
Product2..........Part39
Product3..........Part39

The quantity can then be manually entered.

How can i do this, and/or is there a better way to do this?

Thanks for your time

Kind Regards,
Leon

 

[Galaxiom]

Typically, one would not have a record in the Allocation table unless a there was a non-zero number of the parts to be included in the Product.

If you still want to do it, use a Insert query.

First create a query with Cartesian Product from Products and Parts. (Put both tables in without a join and add the ProductID and PartID fields.) This will create a record for every Product and Part combination.

Now create an Outer join from that query to the Allocation table on both PartID and ProductID.
Use the criteria:

WHERE Allocation.PartID Is Null

This will Insert a record wherever there is no matching combination in the Allocation table. This will apply to all existing Products and Parts.

If you want only want somes PartIDs, add them to the criteria of the Cartesian Product query.

 

[Greyowlsl]

Hi Galaxiom,

Thanks for your reply!

The first query worked, however im still unsure how the outer join query works. Here i have an example, where the outcome is just a large number of completely blank records... or maybe this is the right outcome? question is then how do i populate it?


Thanks,
Leon

 

[Galaxiom]

A whole lot of blank records doesn't sound right. Datasheet view should show you the Cartesian Product that are not already in the Allocation table.

Quick demo attached.

 

[Greyowlsl]

Unfortunately I'm running access 97, and it cant run your example.

However, I ran the query with the outer joins and null (as shown in the picture), but i also added the fields from the SubQry, so now i can see all unique record combinations that are not in the allocation table (which is a good news).


If the part is null in the allocation table then there will be no other data in those rows in the allocation table, so i can see why running the query would only show blanks when displaying only the allocation table fields. The part that im still stumpt on is how to auto populate the partid and productid into the their respective fields in the allocation table. I could manually do it but I'd rather it be an automatic process.

Thanks,
Leon

PS: should we move this thread to the Queries forum?

 

[Galaxiom]

INSERT INTO Allocations ( ProductID, PartID )
SELECT Cartesian.ProductID, Cartesian.PartID
FROM Cartesian 
LEFT JOIN Allocations 
     ON (Cartesian.ProductID = Allocations.ProductID) 
        AND (Cartesian.PartID = Allocations.PartID)
WHERE (((Allocations.ProductID) Is Null));

 

[Greyowlsl]

Used and tested the SQL code and works perfectly!

Thanks so much for you help Galaxiom!

Kind Regards,
Leon