Strange one - reports opened, nothing to tell it to do that.

[Helen]

I cannot for the life of me work out what's going on. Here's the setup:
Form with multiple text boxes that just take numbers.
Also has multiple buttons to click on to show graphs.

Most text boxes have a before update snippet to run a sub that checks if the new number is more than 20% different than the last entry (just to help user catch entry errors). If so, msgbox to inform user with date of the previous entry. Just a couple of lines. The sub to do it just looks up the last entry, simple arithmetic calculation, then msgbox - exit.

There's nothing in either the BeforeUpdate procedure or the sub that checks the values to change the focus, or that has anything to do with graphs, but whenever data is entered (any field), the graph that the command button is Tab Stop 14 opens. If I do a step-by-step run-through, the graph doesn't open, but if I put a breakpoint in the Graph_Click event, that's where the debugger jumps to as soon as data is entered.

Is this weird, or what?
Any ideas? I'm flummoxed!

[alansidman]

Perhaps if you post your code, someone will be able to see what the issue is, otherwise, it is a guessing game.

Alan

[Helen]

Private Sub Calories_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Calories_BeforeUpdate

BodyDataBigChange "CALORIES", "Calories", Me.Date, Me.Calories

Exit_Calories_BeforeUpdate:

Exit Sub

Err_Calories_BeforeUpdate:

MsgBox "Form_ScalesF, Calories_BeforeUpdate, Error number: " & Err.Number & "" & vbNewLine & "" & Err.Description & ""
Resume Exit_Calories_BeforeUpdate

End Sub
-------------------------------------------------------------------------
Public Sub BodyDataBigChange(ByVal strNameField As String, ByVal strField As String, ByVal dtDate As Date, ByVal varNewValue As Variant)
On Error GoTo Err_BodyDataBigChange

Dim varPrevValue As Variant
Dim dtPrevDate As Date
Dim strCriteria As String
Dim strDtCriteria As String
Dim strValCriteria As String

If varNewValue = -1 Or IsNull(varNewValue) Then

GoTo Exit_BodyDataBigChange

End If

strDtCriteria = "Date < #" & Format(dtDate, "mm\/dd\/yyyy") & "#"
strValCriteria = "((not isnull(" & strField & ")) and (not isempty(" & strField & ")))"
strCriteria = "" & strDtCriteria & " and " & strValCriteria & ""

'find the date of the latest entry prior to the current entry
If IsNull(DMax("Date", "ScalesT", strCriteria)) Then

GoTo Exit_BodyDataBigChange

Else

dtPrevDate = DMax("Date", "ScalesT", strCriteria)

End If

'set the date criteria for getting the previous entry
strDtCriteria = "Date = #" & Format(dtPrevDate, "mm\/dd\/yyyy") & "#"
strCriteria = "" & strDtCriteria & " and " & strValCriteria & ""
varPrevValue = DLookup(strField, "ScalesT", strCriteria)

'check if the new entry is more than 20% different to the last one
'if the difference between the NewValue and PrevValue is > 20% then warn the user
If Abs((varPrevValue - varNewValue) / varPrevValue) > 0.2 Then

MsgBox "The value you have entered for " & strNameField & " is a change more than 20% of the previous value." & vbNewLine & "" & _
"The previous entry was for: " & FormatDateTime(dtPrevDate, vbLongDate) & "" & vbNewLine & "" & vbNewLine & "" & _
"You may have entered an incorrect value.", vbInformation, "DATA: Check entry"

End If

Exit_BodyDataBigChange:

Exit Sub

Err_BodyDataBigChange:

MsgBox "FitLogLib, BodyDataBigChange, Error number: " & Err.Number & ", " & vbNewLine & "" & Err.Description & ""
Resume Exit_BodyDataBigChange

End Sub
-----------------------------------------------------------------------
The aberrant action of the graph opening only happens if the BodyDataBigChange sub puts up the msgbox.

[Helen]

I've since changed the final variable in the BodyDataBigChange sub to a single and called it with Csng(me.calories) etc, for all the values on the form - no change, the graph continues to open.

I'd even take suggestions about methods of finding what the problem is - bits of code to send me messages about what's going on ...

Still flummoxed here.

[June7]

I've never built a graph on a form, only reports, although have considered doing it so I am curious. Would you like to make the project available for analysis? If can't post attachment here, could upload to fileshare site such as box.net and post link to file. Be sure to Compact & Repair first.

[Helen]

June7 - I am not able to make the whole project available for analysis. It is also quite large - over a gig now. The graphs are simply displayed as a print-preview, opened with a command button on a click event. Nothing exceptional.

I've inserted messages to display the active control name on these events:
before update, after update, got focus, lost focus

and as soon as any number is entered that requires that the sub be called, the focus bounces to the name of the command button that opens the graph when clicked as immediately after the afterupdate event

[NoellaG]

Hi,

I'm afraid this can only be found out by stepping through the code of the entire project. But as I had a similar case in the past: is there any code in the form events, especially code that gets fired when refreshing the form (AfterUpdate, Oncurrent, ...)?

hope you find the error
NG

[Helen]

Thanks...
Nothing oncurrent, a few fields have simply me.refresh just prior to exiting the afterupdate event. This issue is only a recent phenomena after adding the check for possible data errors last week - I've been using the form daily for over a year.

I commented out the call to the checking sub but it still continues to happen - which is even MORE annoying! I'm beginning to suspect it's an Access bug somehow - like when the autonumbers went haywire. (long story, sure you've heard it, fixed it myself which I'm a bit proud of)

Anyway...
I've inserted messages all over the place telling me what the current focus is and then stepping through - it definitely (and inexplicably) jumps to just that one graph command button as if it was clicked (the on_click event fires) - without moving or clicking the mouse, just pushing the F8 key to step through the code.

I'm thinking of something like this - but I'm not sure how to do it:

There are only 2 ways the graph should open (it's always the same one)
1) The mouse is moved over the cmd button, then clicked.
a) mousemove event - checking process to see if the mouse is in fact over the button (HELP - maybe, might be ok for this)
b) then click event - checking process to see if the mouse was actually clicked. (HELP)

2) tab to the button
a) check what the focus was immediately prior to it shifting to the cmd button (HELP) - I know what it should have been - the tab-stops either side. I can compare the 'immediately prior' names to me.activecontrol.name but I don't how to get them.

I'm sighing with frustration, but it's now pushing 1am (Aussie here) so g'night & I hope the bits of (HELP) above are simple enough to explain.

The functions I need should do the job for me - I can probably manage to code them into a workable solution with a "cancel event" somehow.

[June7]

You could create a project of just the objects of issue, copy the tables, structure only, and put in a few dummy records for testing. Does the file size stay that big after Compact and Repair?

[Helen]

Here's what's happening in step-through:
Form opens with focus on Date (topmost field of form, Tab Index 1)
• Tab once to move focus to next field = Calories (text box control, Tab Index 2)
• break) Calories_GotFocus fires (does nothing except a msg to say it's fired - for debug)
-----------------
• Type 1200 in calories field
• Hit enter key
• Calories_BeforeUpdate fires (one liner, if not -1 then BodyDataBigChange runs. end sub)
• Sub BodyDataBigChange fires (this is given in a previous post)
- focus on entering is Calories (good)
- focus on exiting is Calories (good)
• exit Sub BodyDataBigChange
• back to exit Calories_BeforeUpdate
• Calories_AfterUpdate fires, focus is Calories (good)
(see below for code - not much)
• exit Calories_AfterUpdate (good) - the process SHOULD end here!!!
then continuing to step through, this happens.....
• AgeR_GotFocus fires (what?!?!) Tab Index 40

NOTE: If I'm stepping through the code, the process ends withe Age_GotFocus event - the graph doesn't come up.
IF, however, I clear all the break points & just rely on messages to myself, then the next thing to happen is that the AgeR_Click even fires & the graph is displayed.
--------------------------------------------------------------------
Private Sub Calories_AfterUpdate()
On Error GoTo Err_Calories_AfterUpdate

'oktodel debug message
MsgBox "calories entering afterupdate" & vbNewLine & "" & Me.ActiveControl.Name & ""

'automatic unit conversion, kCalTokJ and kJTokCal are global constants
If Me.Calories = -1 Then
Me.Calories = ""
ElseIf IsNull(Me.Kilojoules) And Not IsNull(Me.Calories) Then
Me.Kilojoules = Me.Calories * kCalTokJ
ElseIf IsNull(Me.Calories) And Not IsNull(Me.Kilojoules) Then
Me.Calories = Me.Kilojoules * kJTokCal
End If

'oktodel debug message
MsgBox "calories leaving afterupdate" & vbNewLine & "" & Me.ActiveControl.Name & ""

Exit_Calories_AfterUpdate:
DoCmd.SetWarnings True
Exit Sub

Err_Calories_AfterUpdate:
MsgBox "Form_ScalesF Calories_AfterUpdate Error number: " & Err.Number & "" & vbNewLine & "" & Err.Description & ""
Resume Exit_Calories_AfterUpdate

End Sub
----------------------------------------------------------------------

so... it looks like I need to put code into the AgeR_Got Focus event like:
if the users tabs to the AgeR cmd button (tab stop 40) then that's ok & the previous active control name will be the tab stops 39 or 41, so IF the previous control names are NOT 39 or 41 THEN reset the focus to whatever it was prior to coming to AgeR_GotFocus

AgeR_GotFocus....
if not (screen.previouscontrol.name = NameOfControlAtTabStop39 or screen.previouscontrol.name = NameOfControlAtTabStop41) then
screen.NameOfPreviousControl.SetFocus
endif

not sure how to go about doing something similar if the user changes the focus to AgeR with their mouse by genuinely clicking on it, or how to go about cancelling the AgeR_Click event if the mouse was NOT over the button just prior to the Click event.

[RuralGuy]

PMFJI but I would try *importing* everything into a fresh new db and see if the problem follows. http://www.btabdevelopment.com/ts/impnew You might just be fighting some corruption.

[Helen]

Thanks.
I think I'll try that before putting code to deal with it, as it DOES seem strange - so could well be corruption somehow.

I'll get back with the result!

ETA - didn't solve the problem -though the look of it all changed a bit, but that's another question.

[Helen]

Solved my own problem - with a patch
___________________________________
Option Compare Database

Public Type Point

lngX As Long
lngY As Long

End Type

Public Declare Function GetCursorPos Lib "user32" (lpPoint As Point) As Long
___________________________________
Private Sub AgeR_GotFocus()
On Error GoTo Err_AgeR_GotFocus

Dim blnTabbedIn As Boolean
Dim blnMouseOk As Boolean
Dim ptMouseLoc As Point
Dim strSetFocus As String

blnTabbedIn = False
blnMouseOk = False
strSetFocus = Screen.PreviousControl.Name

GetCursorPos ptMouseLoc

If Screen.PreviousControl.Name = "BMRkJR" Or Screen.PreviousControl.Name = "ChestR" Then

blnTabbedIn = True

End If

If (277 <= ptMouseLoc.lngX And ptMouseLoc.lngX <= 293) And (472 <= ptMouseLoc.lngY And ptMouseLoc.lngY <= 486) Then

blnMouseOk = True

End If

If blnMouseOk Or blnTabbedIn Then

Me.AgeR.ForeColor = vbRed
GoTo Exit_AgeR_GotFocus

Else

DoCmd.GoToControl strSetFocus

End If

Exit_AgeR_GotFocus:

Exit Sub

Err_AgeR_GotFocus:

MsgBox "Form_ScalesF, AgeR_GotFocus, Error number: " & Err.Number & "" & vbNewLine & "" & Err.Description & ""
Resume Exit_AgeR_GotFocus

End Sub
______________________________________

[RuralGuy]

Excellent, and thanks for posting your solution.

[June7]

Okay, solved, but just what was causing the problem and how does this get around it?
Never mind, now I see your thoughts in earlier post on how you think might solve this which apparently is what you implemented.