Saving a record with VBA using 'on click' event - order of events

[stevet]

For reasons best not gone into, i need to capture lots of data from a laser micrometer and manipulate it.

All well and good and no problems but i'm stuck on something really simple!!! At certain points i want to capture a set of data (relating to a specific coil), mark the record for that sample as complete and then increase the coil number by 1.

My code currently 'checks my complete box' after the 'coil number' count has increased by 1 when i click the command button but i need the following:

Individual records relating to coil 1 to be saved


Coil 1 completes, click the command button
Last record relating to coil 1 annotated 'complete'
Coil number increases by 1
Individual records relating to coil 2 to be saved.

Code is currently (with relevant command button highlighted bold and red:

Public Sub Command0_Click()Me.Check33 = True
DoCmd.OpenQuery ("Reject Coil Query")
Me.Coil_Number = 0
Me.Ignore = True
Me.Check29 = False


End Sub


Private Sub Command27_Click()
Me.Ignore = True
Me.Check29 = False
End Sub


Private Sub Command58_Click()
Me.Coil_Number = DMax("[Coil number]", "Data Table", "[Forms]![Start Up Form]![LOT No]") + 1
Me.Ignore = False
Me.Check29 = True
Me.Check33 = False
End Sub


Private Sub Command58_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Me.Complete = True
End Sub


Private Sub Command58_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
DoCmd.RunCommand acCmdSaveRecord
End Sub


Private Sub Form_Current()
Me.TimerInterval = 1000
End Sub
Private Sub Form_Timer()


DoCmd.GoToRecord , , acNewRec
Me.Complete = False
Randomize
Me.X = 2.81 + Rnd() * 0.08
Me.Y = 2.81 + Rnd() * 0.08
Me.Text38 = (Me.X + Me.Y) / 2
Me.Text46 = (Me.Text36 / (Abs(Me.X - Me.Y))) / 100
Text61 = Round(3 * DStDev("(X+Y)/2", "Data Table", "[Lot Number] = '" & Me.Lot_Number & "' AND [Coil Number]= " & Me.Coil_Number & ""), 4)
If Me.Check29 = False Then
Me.Ignore = True
Else
Me.Ignore = False
End If
If Me.Check33 = True Then
Me.Coil_Number = 0
End If
If Me.Text38 < (Me.Text36 - Me.Text1) Then
    If Me.Ignore = False Then
    DoCmd.OpenForm ("Reject Pop Up")
    End If
    End If
If Me.Text38 > (Me.Text36 + Me.Text40) Then
    If Me.Ignore = False Then
    DoCmd.OpenForm ("Reject Pop Up")
    End If
    End If
DoCmd.OpenQuery ("Delete 3 points data Table")
DoCmd.OpenQuery ("3 Data Points")
Me.Parent.Chart0.Requery
End Sub

Appreciate my code is awful but i'm pretty much making this up ass i go along!!!

Thanks for any help

[rpeare]

I'm assuming this is a bound form. Do you have a sample version of this database we could see (just enough of your table/form structure to replicate this problem with enough data to show the problem).

instead of changing the value in coil number on the form I think I'd be inclined to change it with a SQL statement. Secondly, there should be no reason you need 3 different 'click' events for the same button you can throw a 'debug.print 1' through 'debug.print 3' in each one of those click events to see in which order they execute it may not be executing in the order you think it is.

[stevet]

Hi, thanks for your reply. I realised after I posted I should prob have included a zip of the dB file, but by then I was already home for the weekend and didn’t have access to the latest version of my (bad) code. I’ll try and post it tomorrow (this is my first time ever posting on a forum).

To to try and answer your questions:

yes, it’s a bound form to the only table that actually stores data and in principle it’s very simple. But it’s one of 2 sub forms within a main form, just to slightly complicate matters - basically i need the “operator” to be aware of 3 datasets at the same time.

Appreciate your point about writing to the table with sql, rather than writing to the form which then writes to the table. This makes sense and is defo something I’ll look to do. I’m very logical in my thought so create things step by step without seeing the obvious better solution.

The on click events appear to sequence in the way I’ve planned (I.e I press and hold left mouse, complete box is checked but nothing else happens until I release it) but I can’t be sure what’s happening in real time in the background.

Anywsy, thanks again and I’ll post my file tomorrow.