one-to-one with autonum

[Harry Shmedlap]

Background to question:

Here are 4 Tables:
1) ITEMS
IID (PK, autonum)

2) COMPONENTS
CID (PK, number)
CName

3) Assemblies
AID (PK, number)
AName

4) XREF
CID
AID

ITEMS is the only place where the unique IDs are generated. (This is done to allow any new table (e.g. PDF_SPEC) to be joined to one source ITEMS). When a new component is defined, it is assigned an ITEMS!IID automatically and that is copied to COMPONENTS!CID. Similarily for ASSEMBLIES. This way there is no component that has the same ID as an assembly.

XREF allows building the assemblies out of components.

Relationships:
ITEMS is one-to-one with COMPONENTS and it is also one-to-one with ASSEMBLIES, while XREF is infinite-to-one with each of them.

Question 1: How do I do the query such that when I add a component (or assembly) the new IID is generated and copied to COMPONENTS (or ASSEMBLIES)? I tried this and get the message: "Index or primary key cannot contain a Null value".

help!

 

[Smart]

If I understand correctly

If the component you are adding does not have a corresponding Item then you will have to enter the new Item followed by the component and then the assembly etc.
trying to add a component with out specifiying which Item it belongs to will indeed produce your error

 

[Harry Shmedlap]

You are right. But I don't want to do so much work at the table level.
Instead I built a form for adding assemblies and added the following code
to do the following:
a) Open ITEMS and add a record.
b) Paste that new record's ID to this form
c) Close ITEMS and refresh the form so that ASSEMBLIES will get written.
Here is the code:
--------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate

' Open the current database and link to the ITEMS table:
Dim Dbs As Database, ITEMS As Recordset
Set Dbs = CurrentDb
Set ITEMS = Dbs.OpenRecordset("ITEMS", dbOpenDynaset) 'DB_OPEN_TABLE)

With ITEMS
.AddNew
!ItemType = 1 ' Type = Assembly
.Update
' The Update reset the record pointer to the beginning so...
.Bookmark = .LastModified ' Make new record current
End With

Me!AssemblyID = ITEMS!ItemID
ITEMS.Close
Me.Refresh

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox Err.Description
Resume Exit_BeforeUpdate

End Sub
--------------------------------------------------------------------
All is ok - the tables get updated. However there are two problems:
1) I get the message: "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field".

2) After the the above message the form jumps to a new (blank) record.
So even though I can go back and see the new assembly, I want
the data to remain on-screen.

Any suggestions?

 

[Harry Shmedlap]

hmmm... waiting for an answer gave me time to figure it out myself:
it was the refresh.
I removed it and everything is fine!

Now the only question is how to get the delete button to delete all
associated records.

 

[Pat Hartman]

You are making this harder than it needs to be. The component and assembly tables are redundant. All components and assemblies are items so they don't need to exist as independent tables. Since you have only a two-level bill and all items are either a component or an assembly, use a code in the item table to identify which is which. Then the XREF table will link the items table to itself instead of the components table to the assemblies table.

 

[Harry Shmedlap]

1) I tried that but I couldn't figure out how to do the form (assemblies) and the subform (components per assembly). How do I go about setting it up?

2) If I understand your "two-level bill" then my applicatoin is deeper: every assembly can itself be a subassembly of something higher up in the product tree. Is your method still acceptable?

3) I have an object oriented background so I always start off with the most basic entity at the top and work down. This way I push off modifications as far down the hiearchy tree as possible, for simplifying backward compatibility with revisions. Here the basic entity (ITEMS) just assigns the ID. I don't think I could get simpler/more general than that.

Thanks for your input.

 

[Pat Hartman]

1. The forms would be based on queries. As long as the master/child links for the subform are properly set, the mainform will keep the subform synchronized.
2. The cross reference file that you are using is the typical method used by BOM applications to represent a structure with infinite depth. To traverse the hierearchy, you can write a recursive VBA sub that follows the parent/child or child/parent chain or you can write a query. The query is limited to a fixed depth so it ultimately less useful but certainly easier to start with. Search the archives here for other posts on the topic.
3. You can continue with your present method but even then the component and assembly tables are redundent.

 

[Harry Shmedlap]

The recursive stuff shouldn't be a problem. There's a great example using a TreeView from Microsoft.

But I still can't seem to get the form right.
I'm sending you the sample file separately (our firewall prevents uploads to internet). Perhaps you could look at it and tell me what i'm doing wrong.
Once I understand how to do the query/form then I should be fine.

Thanks Pat.

 

[Pat Hartman]

Take a look at this many-to-many example. It relates two separate tables but the concept is the same as when you relate one table to itself.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany