Not in list questions

[Sammie]

Two questions follow this NotInList procedure shown here:
'Ask the user whether to add a value to the list
Dim strMessage As String

strMessage = "Are you sure you want to add '" & NewData
& "' to the list of products?'"

If Confirm(strMessage) Then
'Open the frmProductsInvoiced form and add the
NewData value.
strProduct = NewData
DoCmd.OpenForm "frmProductsInvoiced", , , ,
acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!product = Null
Me!product.Dropdown
End If
End Sub
Q #1: I have two orders applications in my database: one
for purchases and one for sales. I create purchase orders
and invoices with two similar forms (one created by copying
the other and changing names & code). Both forms use a
productID lookup to their corresponding table:
ProductsPurchased and ProductsInvoiced (for sales). I
would like the productID in these two tables to have a one
to one relationship so they match. When my NotInList event
fires, it won't let me add a poduct because there is no
corresponding record in the other table. Is there a way to
add the product to both tables in the same procedure so I
won't get the error?
Q. #2: If I cancel the procedure (decide not to add the
product) in the Confirm section, the line "Me!product =
Null" produces the following error:
"Run-time error '3162': You tried to assign the null value
to a variable that is not a Variant data type." Why do I
get this error?
Thanks in advance for the help.
Sammie

 

[Wayne Morgan]

1) In the Relationships window, right click the link between the two tables
and choose Properties. Check the Enforce Referential Integrity box and check
the Cascade Update and Cascade Delete boxes.

2) Rather than Me!Product=Null, try Me!Product.Undo to undo the change the
user made and you're wanting to discard.

 

[Sammie]

1) In the Relationships window, right click the link between the two tables
and choose Properties. Check the Enforce Referential Integrity box and check
the Cascade Update and Cascade Delete boxes.

2) Rather than Me!Product=Null, try Me!Product.Undo to undo the change the
user made and you're wanting to discard.

#1: I can enforce referential integrity and cascade delete, but it will
not allow cascade update, I think because ProductID fields in both
tables are auto-number. Am I correct and if so, is there a work-around?

#2: Works! Thanks.
Sammie

 

[Wayne Morgan]

How can they be 1-to-1 if they are autonumber in both tables? There is no
guarantee that the autonumbers will remain in sequence. In the "child" table
of the 1-to-1 relationship, set the field to Number, Long Integer. You
should be able to make this change without losing any data since Long
Integer is the data type of an autonumber field. Try this on a copy of the
file first to see if it works for you, going back to autonumber isn't
pretty.

It is possible to do this without the Cascade Update, but as mentioned
earlier, still not with the autonumber field because there is no guarantee
that they will remain synchronized between the tables.