Why oh why

[ryetee]

I have a form which when displayed shows all the items on the table (usually 1 or 2) for a particular key.

The following row is part filled in awaiting further input for the rest of the fields of that row.

I have a button (exit) which basically closes the form but it also validates the (potentially) partial field ultimate row. If the user hasn't added any data to the partially filled row then I delete that row with this code.....

If IsNull(Me.Field1) or IsNull(Me.field2) Then

....If Not Me.NewRecord Then
........ DoCmd.SetWarnings False
........ strSQL = "DELETE * FROM [Component Swaps] WHERE [ID] =" & [ID]
........DoCmd.RunSQL strSQL
........ Me.Refresh
........ DoCmd.SetWarnings Trye
.... End If

.... DoCmd.Close
.... Exit Sub
End If

This works. I have copied the exact code into the close event for the form but it seems like in that part of the code the previous row is being picked up.

So for example if I have 2 records on the table I display 3 rows (as 1 is created with partial data) thus

ID.....Prefilled Field......Field1......Field2
1......fromrow1............F1...........F2
2......fromrow2............F11..........F22
3......generatedrow3.....NULL........NULL

If the code goes through the exit event then Me.Field1 and Me.Field2 are Null and the record with the ID=3 gets deleted.
Going through effectively the same code for the close event Me.Field1 = "F11" and Me.Field2 = "F22" and the record doesn't get deleted (ID=2 as well)

Why oh why oh why!?!?!?!?!?

 

[Mihail]

Not very sure but pretty sure
This happen because the last record is not (yet) saved when the close event is applied.
So... force Access to save the record then run your code.

 

[ryetee]

Not very sure but pretty sure
This happen because the last record is not (yet) saved when the close event is applied.
So... force Access to save the record then run your code.

ok 2 questions
1.why does the click on the exit button event work with the same code
2. how do i force a save

 

[BlueIshDan]

could you step through the code by placing a breakpoint and pressing F8 to step through the lines. This may show you your answer as to how far it is getting. My guess is that it isn't even being called at all on your close event. Maybe it isn't linked correctly?

 

[ryetee]

could you step through the code by placing a breakpoint and pressing F8 to step through the lines. This may show you your answer as to how far it is getting. My guess is that it isn't even being called at all on your close event. Maybe it isn't linked correctly?

Got a breakpoint and that's why I know it's picking up previous record/row as I can see values of me.Field1 and me.field2 and ID.

 

[Mihail]

ok 2 questions
1.why does the click on the exit button event work with the same code
2. how do i force a save

1. Because Access automatically save the record.
2. DoCmd.RunCommand acCmdSaveRecord

 

[ryetee]

1. Because Access automatically save the record.
2. DoCmd.RunCommand acCmdSaveRecord

OK I'll give that a go but I think I'm going to disable the close option anyway as the user should only really navigate with the buttons I give him!