B
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
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