Solved Stop New Record From Saving When Subform Updates (1 Viewer)

LGDGlen

Member
Local time
Today, 22:11
Joined
Jun 29, 2021
Messages
153
Hi All

I have a form with 2 controls on it that are sourced from a table. The table is companies we work with and the controls determine which of the list to show depending on Yes/No fields on the record.

Control 1 - Suppliers
Control 2 - Freight Companies

The Companies table is defined as:
ID
Name
isSupplier
isFrieght

The form is to create a new consignment record and the user selects a supplier and the transport company for the record along with a few other items.

The subform is the list of companies that is present to allow the user to add new suppliers or freight companies whilst entering the consignment instead of:
  • Clicking on New Consignment button to open Consignment form
  • Clicking on Supplier dropdown
  • Finding the Supplier is not listed
  • Clicking Cancel
  • Going to companies maintenance area
  • Adding new company
  • Returning to consignments list
  • Clicking on New Consignment button to open Consignment form
  • Selecting new Supplier as it is now present in the list
So i have added VBA to the subform in AfterUpdate and AfterDelConfirm
  • Check whether the form is currently a subform on a parent form
  • Check which parent form it is currently on (as i'm planning on enabling this functionality in other areas for customer companies)
  • Requery the combo box for Suppliers on the parent form
  • Requery the combo box for Freight Companies on the parent form
What i want to stop is the creation of a new record when the requery happens as i want to guard against the user deciding they actually want to cancel the consignment entry and exit the form without writing anything

I think i need to check the parent dirty status and do "something" to stop the writing of the partially updated record but i just don't know whether thats the case as i have tried that and it seems not to do anything. Here is the VBA i have so far and whilst it kind of does what i want it also does the writing of a new consignment record when i don't want it to, the consignment record should only be written when the user clicks on the save button on the consignment form

Code:
Private Sub Form_AfterUpdate()
On Error GoTo Err_Exit
    If Len(Me.Parent.Name) > 0 Then
        If Me.Parent.Name = "form-Consignment" Then
            If Me.Parent.Dirty Then
                Me.Parent.Dirty = False
            End If
            Me.Parent.consignment_supplier_id.Requery
            Me.Parent.consignment_freight_info_company_id.Requery
        End If
    End If
Err_Exit:
End Sub

Happy to include anything else that would help (as in forms, table definitions etc etc) and if there is a better way to achieve this i'd appreciate pointers for that

Kind regards

Glen
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:11
Joined
Feb 19, 2013
Messages
13,566
Not quite sure what you are trying to achieve - Are you saying that you have a new consignment which requires a new supplier but you don't want that supplier to be created if the user decides to abort creating the consignment? Or are you saying you don't want the consignment created if partially completed when the user goes to the supplier subform?

As far as your code is concerned, think you need to use the before update event, the after update event is too late. EDIT: just realised the code is in the subform, so ignore. However the consignment is saved when the user moves to the subform, so think it needs to be in the main form before update event. And the subform linkchild and master properties should be blank.

For the former, I don't think your solution will work. You would need to populate your subform with an ADO disconnected recordset, allow the user to enter a new supplier, but don't update the underlying supplier table until the consignment is saved.

For the latter - I would look at using a supplier combo or listbox and use the not in list event to create the new supplier

but just an (untested) suggestion.

Code:
In the main form have a boolean called bSave
set it to false in the main form current event
in the main form beforeupdate event

me.dirty=bSave
bSave=false

in the save button click event

bSave=true

in the subform afterupdate event
Me.Parent.consignment_supplier_id.Requery
Me.Parent.consignment_freight_info_company_id.Requery
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:11
Joined
Feb 19, 2002
Messages
33,711
Main forms always save if they are dirty when focus shifts to the subform. That is just the way it is and exactly the way the rest of us want it to work. The rational is that parent records MUST exist before you can add child records. That's just the way all RDBMS' work. Not just Access.

As CJ pointed out, the AfterUpdate event is too late. Think of it as closing the barn door after all the horses have escaped. If you want to stop a record from being saved, you have to do it in the Form's BeforeUpdate event. That event has a Cancel argument. Setting Cancel = True, cancels the save. It doesn't clear the record though so if you want to also clear the record, you need to use Me.Undo.

I don't understand your process. It seems like you are conflating two processes which should be separate. or at least one if them is the main process and the other needs to be handled as a segue to a new form to add the new data and a return to the original form with the new supplier added to the list. This is done by using the NotInList event of a combo.
 

LGDGlen

Member
Local time
Today, 22:11
Joined
Jun 29, 2021
Messages
153
Hi All

thanks for the updates, i didn't realise that the main form would save if focus goes to the subform, that makes sense now you've described it and it certainly explains what is happening.

The reason for this process is that we get new suppliers once in a while send an email to say they are supplying us product. We create the consignment from the email and there are combo boxes with suppliers listed. The user would open a new consignment and then realise that they need to add the supplier and then have to cancel the new consignment move to the maintenance and add the supplier, then go back and redo the consignment so they could select the supplier from the combobox

tbh i wasn't keen on adding non linked sub forms to a form but was trying to make maintenance context sensitive so depending on the form you open you could unhide the maintenance section to allow you to modify tables that supply information to the form. For an example, we start using a new currency, or add a new supplier or product, instead being half way through doing something and having to cancel the update to go to the specific maintenance form/forms add the new information and then go back to what you were doing takes the user out of their input process that would slow things down

if you had context sensitive maintenance tables available as sub forms on the form and shown when user wants them (done with a check box to display the maintenance tables) it would allow the user to maintain their process whilst also updating the relevant tables. As an example i set up the following for consignments and consignment products:

1633075993077.png


So if the user finds the supplier or freight company are new they could add them in the subform table view of the companies maintenance table

1633075739651.png

The tabbed area has context sensitive maintenance tables which i would allow the user to add new products, varieties, country of origin or per types (which can be kg, cases, or something else) but if ..... for some reason.... the user decides not to complete the form and hits cancel the record is not written to the database.

Hope this makes sense in what i am trying to achieve, and again i really don't want to over complicate things, so i would rather say to the users

"I understand what it is that you want to have but to have this it would take a substantial amount development and the amount of effort far outweighs the benefit, so you'll need to just deal with the fact that there may be infrequent times when you'll need to cancel your data entry to do the maintenance you need to do."

Than try to create a cludge of code to force a square peg into a round hole
 

LGDGlen

Member
Local time
Today, 22:11
Joined
Jun 29, 2021
Messages
153
decided to tell the users they needed to update the maintenance prior to creating a new consignment and if they found they didn't they'd have to cancel entry do the maintenance and go back, too much hassle with the subform having focus causing the main form to write, thank you all who responded for your help
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:11
Joined
Feb 19, 2002
Messages
33,711
As I said in my previous post, this problem is solved by using the NotInList event of a combo. If the item you want isn't there, Access opens the form you specified so you can add the new supplier. When you close the pop up form, the new Supplier is added to the list for you to select.
 

Users who are viewing this thread

Top Bottom