Subform Undo code not working.

[cath_hopes]

Hi there!

My form is based on a Booking table. This form has a subform based on a Booking Status table. The booking table has a one to many relationship with the Booking Status table.

I've coded a 'Close' button on my main form which asks the user whether they wish to save changes or not. If the answer is 'No' and the main form is a new record then Forms![Booking Form].Undo works fine and removes any new Booking and associated Booking Status records. If the main form is at an existing record then it undoes any main form changes but not any booking status subform changes.

On the main form code I've tried Me.Booking_Status.Form.Undo and Forms![Booking StatusForm].Undo but it doesn't seem to do anything.

Can anyone suggest any other code that might work or where I'm going wrong?

Thank you heaps in advance!
Catherine

 

[boblarson]

Catherine:

the problem lies in that once you move the subform the main form record has already been saved. You would need a delete query to delete the existing main form record.

 

[cath_hopes]

Bob,

Its not the main form record that I have a problem with - its not being able to undo any subform changes thats my issue. What do you mean by delete query?

Catherine

 

[boblarson]

Actually, the close button on the main form is the issue as once you set the focus back to the main form the changes will automatically be saved to the subform. You would need something like:

"Delete * From YourSubformTableNameHere Where YourIDFieldNameHere = Me!YourIDFieldNameThatLinksTheSubformWithTheMainForm"

(that is air code...not exactly written the way the delete query was but you can create it in the QBE grid, save it and then run it from the button code).

 

[cath_hopes]

Ok, I understand now how the subform record becomes saved.
Are you saying also that changes to a subform record therefore cannot be undone, only the record can be deleted (using your air code)?
Thanks again

 

[boblarson]

Are you saying also that changes to a subform record therefore cannot be undone, only the record can be deleted (using your air code)?

Once it has been saved, yes...

 

[Rich]

You could always put the delete button on the subform

 

[cath_hopes]

I'm struggling to get my Delete Query to work. I want to delete records from the Booking Status table where the Booking Status Date is blank. The Booking Status Date field is a Date/Time data type.
Under the criteria of the QBE I've tried = " " and Year(Booking Status Date) <2006. This gives the SQL:

DELETE [Booking Status].[Booking Status Date], *
FROM [Booking Status]
WHERE ((([Booking Status].[Booking Status Date])=" "));

or

DELETE [Booking Status].[Booking Status Date], *
FROM [Booking Status]
WHERE ((Year([Booking Status].[Booking Status Date])<2006));

But neither works. Can you advise please?
Thanks!

 

[boblarson]

If a date field is blank then it is NULL:

DELETE [Booking Status].[Booking Status Date], *
FROM [Booking Status]
WHERE ((([Booking Status].[Booking Status Date]) Is Null));

 

[cath_hopes]

Thanks for your help. I've made some design changes to accomodate the subform undo issues and used the Delete coding that you suggested. It almost works ok now, apart form one field that I want updated straight after the delete sql runs.
The odd thing is my update code works fine if I step through the code in debug mode but leaves the field blank when debug is not running. The following code is invoked immediately after a Booking Status (subform)record is deleted:

If myFlag = "changeLBS" Then
Me.Latest_Booking_Status.Value = Me.Booking_Status.Form![Booking Status].Value
End If

DoCmd.Close acForm, "Job Form", acSavePrompt

Basically, a field on the Booking table needs updating (Me.Latest_Booking_Status.Value).
The strange thing is if my first breakpoint is on the DoCmd.Close, and I check the values for everything within the IF statement, they are all correct except Me.Latest_Booking_Status.Value which is blank.

Have I missed something obvious?
Thanks again for your help,
Catherine

 

[boblarson]

... The following code is invoked immediately after a Booking Status (subform)record is deleted:

If myFlag = "changeLBS" Then
Me.Latest_Booking_Status.Value = Me.Booking_Status.Form![Booking Status].Value
End If

DoCmd.Close acForm, "Job Form", acSavePrompt

Basically, a field on the Booking table needs updating (Me.Latest_Booking_Status.Value).
The strange thing is if my first breakpoint is on the DoCmd.Close, and I check the values for everything within the IF statement, they are all correct except Me.Latest_Booking_Status.Value which is blank.

Have I missed something obvious?

Yes, you can't reference a value on the subform if the records is deleted. If you need that value, put the part that does the booking status BEFORE the subform delete code.

 

[cath_hopes]

I need this code to be after the delete because the value I want to pick up is on the subform record previous to the one deleted.

 

[boblarson]

Then you're going to have to either store the previous value somewhere or, if there is some order that you can know the last one entered prior to the deleted one, you could use that in a DMAX or similar function.