To make changes to this field, first save the record - HELP!!! (without needing code if possible)

[mistletoegal]

Hi, I'm a returning amateur trying to construct a form with a datasheet subform. I have successfully added two cascading combo boxes to the subform to narrow selection of items but the third (constructed the same as far as I can tell) gives error message 'to make changes to this field, first save the record'. I have not used any code - just the property boxes and expression builder. I have checked my relationships, am requerying after update, checked there are no default values and checked my column counts (the suggestions I've found so far)... I'm not a programmer so don't really want to take the VBA route if it can be helped. I get the feeling I've missed something basic, but googling this error just sends me to pages of coding which I don't really want to get my head round - so an 'Access for dummies' approach would be appreciated

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[mistletoegal]

If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks - at present there is a mountain of confidential data as it is my company's customer and invoice details. Will faff about a bit longer but may have to resort to copying & deleting data and submitting for help but not sure there would be any data left! I'd have to add a load of dummy stuff - is this not a common error? :/

[June7]

Not uncommon but tracking down cause is so dependent on form structure, properties, code. There is no one correction that could be solution for every case. The effort you already went through in searching for cause demonstrates that.

Deleting phone numbers, emails, addresses, SSN data should sanitize the db. Either delete the fields or run UPDATE query to set Null.

[mistletoegal]

I think I've left enough data to replicate problem.

I've created form events1 with a subform. In subform, 'Type' is fine and feeds 'Subtype', 'Subtype' is ok and feeds 'ItemID' - but its when trying to enter this that problem occurs.... really appreciate any help

[June7]

A form can do data entry/edit for only one table.

The controls bound to fields of Contacts table should not be editable - set them Locked Yes and TabStop No.

There are two comboboxes for Item. Eliminate the one bound to Item field of Items table (convert to a textbox).

The comboboxes should not be bound to fields of Items table. This will change the data in Items. The subform should be for data entry to EventItems table only. Same as Contact fields, the Item fields should not be editable. Why do these comboboxes have date format? Remove this.

Why even include Items table in the subform RecordSource? The other Item info can be in the combobox RowSource and that will make them available to the form for calculations.

Rename the comboboxes to: cbxType, cbxSubType, cbxItem

Row Source for cbxSubType:SELECT ItemSubType.SubType FROM ItemSubType WHERE (((ItemSubType.Type)=[cbxType]));

RowSource for cbxItem: SELECT ItemID, Item, Price FROM Items WHERE (((Items.SubType)=[cbxSubType]));
ColumnCount: 3
ColumnWidths: 1.0";2.0";1.0"

Adjust the requery expressions for the new names. cbxType does not need to be requeried. Only cbxSubType and cbxItem need to be requeried. Suggest using the GotFocus event of those two comboboxes. If you use the AfterUpdate event, then the Requery should be on the next dependent box, not on itself.


Type field is not needed in Items table.

Autonumber fields are designated as primary key in ItemType, ItemSubType, and Items tables but those values not used as foreign key. Those autonumber fields serve no purpose. Set the Type, SubType, ItemID fields as primary key in each respective table.

I always name subform container different from the object it holds, like: ctrItems.

Advise no spaces and special characters/punctuation (underscore is exception) in naming convention. Might want to remove the space from [Event Items] and [M Rate] and [Discount Amount] as well as from query names. Why end a field name with underscore?

Also advise not to set lookups in table.

[mistletoegal]

Wow thanks for speedy response! This should keep me going for a while

[June7]

I have been editing the post, think I am now done. Should probably review it again.