Updatable subform (1 Viewer)

[paul young]

From a form I am working on, I get this message: 'You tried to assign the Null value to a variable that isn't a Variant data type.'

The form is 'FunctionBooking2' and the above message appears when I try to select a item from a combo box in the menu selection.

This suggests that the subform is not updatable. Is there something wrong with the relationships? Can anyone out there help - to fit the problem or something like that?

Thanks.

Paul

 

[adam_fleck]

Problem in Menu Selection Table

The MenuSelection table needs to be restructured (quite difficult because relationships have been defined).

It should be restructured something like this:

ms_ID Autonmumber (primary key)
Booking ID Number (foreign key)
Item ID Number
Number of Item ID Number

Booking ID is a so-called "foreign key" that is used to link with booking ID in the function booking table (where booking ID is the "native" primary key). In the table MenuSelection, Booking Id tells Access which booking this particular menu item is connected with, and also allows you to have Many menuSelections associated with One booking, but you probably know that already.

To resturcture you'll need to delete all existing data, then go to the Relationships window and break the link on Booking Id between the FunctionBooking and MenuSelection tables.

After that, you'll be able to redesign the MenuSelection table as suggested above, then re-establish the link you just broke.

After that, it should work, or at least it does for me.

Good luck.

 

[paul young]

Foreign key

Thanks for the reply.

How can I get a primary and foreign keys in same table? I am allowed only one such.

Regards.

Paul

 

[Pat Hartman]

A primary key is a unique identifier for each table row. A foreign key is a "pointer" to the primary key of another table. It may be indexed but it is rarely unique as it is the many-side of the relationship. The usual example is the CustomerID in the Orders table is a foreign key to the Customer table. The foreign key of the Orders table is joined to the primary key of the Customer table in a query to return a recordset that includes the customer information for an order. If you were to define CustomerID as unique in the Order table, you would restrict the table to only ONE order per customer. Not exactly a desirable situation.