An interesting subform problem

[Brian]

I have 2 tables in SQL: property and purchaser. The relationship is: a
property has one purchaser and a purchaser can have more than one property.
As far as I can tell this is a normalised relationship. For example:

tblProperty
PropertyID int identity(1,1) not null
PurchaserId int null
Address nvarchar(50) not null
...

tblPurchaser
PurchaserID int identity(1,1) not null
Name nvarchar(50) not null
...

Now there is a form for each table in Access that has an ODBC link to the
tables. These work fine and there is no code behind the forms. The property
form has the purchaser form embedded and linked through the PurchaserID.
Again this works fine and scrolling through the property records will show
the related purchaser.

However this is the problem: when the property form navigates to a new
record and data is entered then a property record is created with a null
PurchaserId, then when data is entered on the purchaser subform the
following error occurs: "you tried to assign the null value to a variable
that is not a variant data type". You can also get the error "you can't
assign a value to this object" if the name of the PurchaserId field on the
purchaser subform is "PurchaserId" (i.e. the same as the table column name).
The explanation is obvious - MS Access is trying to update the link between
the forms and fails. The error can be ignored but the records are not
automatically linked.

I have tried a number of things:
1. Automatically update property form with the PurchaserID. e.g.

Private Sub Form_AfterInsert()
Me.Parent.PurchaserId = PurchaserId
End Sub

This works fine and links the two forms and records correctly. However
the error still comes up.
Is there any way of trapping and ignoring the error?

2. Taking out the link between the parent and subform and manually linking
the form. e.g.

Private Sub Form_Load()
Me.RecordSource = "select * from tblPurchaser where PurchaserId = "
& Me.Parent.PurchaserId
End Sub

This works but doesn't keep the forms synchronised and still needs the
code from point 1 above to link the records. However it does get rid of the
error message.

It seems to me that MS Access should support this secenario. Obviously it
support the complementary relationship, for example, an order has many
items. Has anyone worked out any workarounds for this type of relationship
allowing new records to be inserted and linked automatically?

Brian

 

[Keith Wilby]

I have 2 tables in SQL: property and purchaser. The relationship is: a
property has one purchaser and a purchaser can have more than one property.
As far as I can tell this is a normalised relationship. For example:

tblProperty
PropertyID int identity(1,1) not null
PurchaserId int null
Address nvarchar(50) not null
...

tblPurchaser
PurchaserID int identity(1,1) not null
Name nvarchar(50) not null
...

Now there is a form for each table in Access that has an ODBC link to the
tables. These work fine and there is no code behind the forms. The
property form has the purchaser form embedded and linked through the
PurchaserID. Again this works fine and scrolling through the property
records will show the related purchaser.

However this is the problem: when the property form navigates to a new
record and data is entered then a property record is created with a null
PurchaserId, then when data is entered on the purchaser subform the
following error occurs: "you tried to assign the null value to a variable
that is not a variant data type". You can also get the error "you can't
assign a value to this object" if the name of the PurchaserId field on the
purchaser subform is "PurchaserId" (i.e. the same as the table column
name). The explanation is obvious - MS Access is trying to update the link
between the forms and fails. The error can be ignored but the records are
not automatically linked.

I have tried a number of things:
1. Automatically update property form with the PurchaserID. e.g.

Private Sub Form_AfterInsert()
Me.Parent.PurchaserId = PurchaserId
End Sub

This works fine and links the two forms and records correctly. However
the error still comes up.
Is there any way of trapping and ignoring the error?

2. Taking out the link between the parent and subform and manually linking
the form. e.g.

Private Sub Form_Load()
Me.RecordSource = "select * from tblPurchaser where PurchaserId = "
& Me.Parent.PurchaserId
End Sub

This works but doesn't keep the forms synchronised and still needs the
code from point 1 above to link the records. However it does get rid of
the error message.

It seems to me that MS Access should support this secenario. Obviously it
support the complementary relationship, for example, an order has many
items. Has anyone worked out any workarounds for this type of relationship
allowing new records to be inserted and linked automatically?

Brian

I think the problem is the ODBC connection - if they were native Access
tables then you would set up a 1:M relationship between the two tables in
the Relationship window on the PurchaserID field. If the form/subform data
links were correct then you would then get the FK populated for free. I've
just tried to set up such a relationship between two ODBC connected Oracle
tables and the referential integrity option is greyed out so it looks like
trapping the error is the way ahead.

Have you tried finding the error number in debug mode? If you can find its
number then you could use

If Err.Number = nnnn Then ...

Regards,
Keith.
www.keithwilby.com

 

[Brian]

Thanks for the information Keith,

I've now managed to trap the error (3162) with Form_Error and ignore it,
using code as discussed to create the link - a bit of a hack but it works.

Brian