Cant Update Field

[Marty B]

Hi All,


I have seen several posts similar to this one but not exactly. I have been scratching my head all day. I have two tables linked in a one to many relationship on the Field "ShopOrderNo" I have a program that allows the user to select a record based on this field. If it exists, I wish to edit the other fields in the "Many" table I am using a select query to access the data. I however can not get it to work. When the VBA code attempts to enter data from the form into the fields, I get a "Cant Update Field" error. I have attached the project. I removed all objects from the project except the vital ones to demonstrate the problem. I haven't done any Access programming since Access 97 and my mind is a bit rusty. I have looked into using Update Queries and Append Queries but dont think they fit what I need to do. Anyone who can help will be my new Best Pal.

Thanks very much in advance.

Marty

[June7]

Use form/subform arrangement. No VBA required. Main form bound to tblShopOrders, subform bound to tblOrderdata. Then bind textboxes to fields of respective recordsources. Data entered into bound controls passes directly to fields of table. Data/record committed to table when form closes, move to another record, or code forces save.

Access Help has guidelines on building form/subform.

[Marty B]

Use form/subform arrangement. No VBA required. Main form bound to tblShopOrders, subform bound to tblOrderdata. Then bind textboxes to fields of respective recordsources. Data entered into bound controls passes directly to fields of table. Data/record committed to table when form closes, move to another record, or code forces save.

Access Help has guidelines on building form/subform.

I will look closer into that. Thanks for the suggestion. Do you know why I am unable to update the field in the query? The test application that I attached is only a part of the overall project. It would really help to know what i am doing wrong there. Thanks again for your help.

Marty

[June7]

Actually, the query is allowing edit of existing records. The problem is with new records because there is no existing pk/fk link. Hence the need for form/subform. The Master/Child links properties of the subform container will synchronize the keys.

The form has just 3 unbound textboxes. Why unbound controls?

[Marty B]

These controls are actually populated with a barcode scanner and I want to do lots of different kinds of error checking of the text that is placed into them before I store them. (check format of the part number and lot number etc.) What is pk/fk link? Sorry for my ignorance.

[June7]

Pk/fk - primary key and foreign key, the ShopOrderNo of the two tables. It is primary key in ShopOrders and foreign key in OrderData.

You can have the controls bound and still do the validation, just don't commit the entries or the record if errors. But whichever way works best for you.

[Marty B]

I will definitely look into your first suggestion. It is becoming apparent that I don't know what I am doing. Thanks again. I will let you know what I come up with. You have been a big help.

Marty

[Marty B]

Pk/fk - primary key and foreign key, the ShopOrderNo of the two tables. It is primary key in ShopOrders and foreign key in OrderData.

You can have the controls bound and still do the validation, just don't commit the entries or the record if errors. But whichever way works best for you.

I have reworked things and am now using a form and subform as you suggested and am using bound controls. I would now like to populate some of the fields programiticaly (date for example). I am doing this in VBA. When I do this, it automaticaly saves the record. How can I populate the form without actually saving the record?

Thanks again for your help.

Marty

[June7]

The record is initiated but not necessarily committed. Up to a point, the entry can be undone and record discarded (aborted). However, by using autonumber field as pk this can result in gaps in sequence. Normally this should not be a concern.

Would have to use unbound controls and VBA to save all entries to table if you don't want record to show in table at all until 'Save' command executed.

[Marty B]

Thanks for the quick reply. That's what I was afraid of. I can do that. Thanks for the confirmation.

Marty

[Marty B]

I just wanted to say thanks to June7 for your help. I am making great progress now that you have gotten me over this hurdle. Have a great Weekend.

Marty