How to update a form with combo box data but not add record to combo source table

[Tonyb123]

Hope someone can help,

I have a split form with a combo box on the main form "frmCompany" that populates the forms Company Name and Company Branch and Company ID text box's. The Main and sub form has a Master/Child CompanyID relationship.
The data on the Sub Form saves to "tblProject"
This works well.
The Problem is... Every time I use "save and new" or "save and close" button on the main form, the "frmCompany's" Table "tblCompany" updates with a duplicate Company Name, Branch and ID.
Tried various event snippets, snapshots settings, and cross tab query without success.


To sum up I want to save the new record to the Project table and leave the Company table data unchanged.

Many thanks

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[Welshgasman]

Save and New I can uderstand.
Form should be bound, so no Save needed.

As mentioned upload your DB or at least the code behine the buttons. Please post the code within code tags, using the # icon.

This is one of these 'How long should my bit of string be?' when we have no idea what the string is for.

[Gicu]

From what you describe I think the issue is related to your combo; it looks like you use the combo to search for the company in the main form then add a record in the projects subform. The combo needs to be unbound and have code behind the AfterUpdate event to find and load the desired company. That code is usually produced by the Add Combo wizard if you select the option "Find record on the form":
https://learn.microsoft.com/en-us/of...-box-selection
https://support.microsoft.com/en-us/...b-b0f71a90c329
Cheers,

[Tonyb123]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

Many Thanks, Tony b

[Tonyb123]

Hi Vlad, Thankyou for your response.
Your description of the functionality is correct. I replaced the combo box with the "Find record on form" type. Moved the vba event for adding the company names to the main form Felds to "on change" event (After update has a Macro). Unfortunately the company table still gets a duplicated record on save. I hope what I am trying to do is possible with code or a restructure?

Regards Tonyb.

[Welshgasman]

You have Data Entry set to Yes?

That means you create a new record each time.

The worst named property of an Access Form.

[June7]

Don't use code to set values of BOUND textboxes on main form. These should be for inputs by user for a new company record.

Form DataEntry property is set to Yes. This means only new records can be input. That's what "data entry" means. Cannot view nor edit existing.

[Tonyb123]

You have Data Entry set to Yes?

That means you create a new record each time.

The worst named property of an Access Form.

Hi Welshgasman,
I agree, "worst named property" Changed to NO and all good now.

Thankyou for your response on this.

[moke123]

You should get rid of the lookup fields in your table.