UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Call A Public Function From A Form Event Property Line, Access 2016    
 
   
tina t
post Sep 27 2019, 09:51 PM
Post#1



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


using A2016 64-bit in Win7Pro (soon to be Win10).

hi guys, back again. i'm trying to do just what the topic title says. public function in standard module, as

CODE
Public Function isNow()

    TempVars!tvNow = Now

End Function

variable declared at top of standard module as

Public tvNow As TempVar

calling the function from form event property lines on OnMouseUp event property and OnKeyUp event property, in the forms' Property sheets, as

=isNow()

KeyPreview is set to Yes in each form.

the error doesn't occur with every keystroke or mouse click, or in the same controls or areas on any form; it seems to be random, but is frequent. and it occurs in every form i've tested (i added the property line calls to a lot of forms last night!). the .accdb has been compacted/repaired, decompiled and recompiled, and compacted/repaired again. here's the error msg i'm seeing:

Attached File  Capture1.JPG ( 28.43K )Number of downloads: 9


the call from mouse up produces the same error msg, only citing the OnMouseUp in the text, of course.

so am i doing something really stupid and obvious here? or is there a known bug that i didn't find in my google search? or...? all ideas and suggestions are appreciated!

tia
tina
This post has been edited by tina t: Sep 27 2019, 09:58 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
isladogs
post Sep 28 2019, 01:54 AM
Post#2


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


Perhaps I'm missing something obvious here?

I see no reason for tempvars or a UDF when you can just use =Now() as the control source or as its default value.
Similarly why are those events being used?
If you want the displayed time to be updated each second, use a timer event with interval 1000.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
projecttoday
post Sep 28 2019, 02:22 AM
Post#3


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


A random, intermittent error can be difficult to solve. Well, you seem to be throwing Access a curve or two. Whether or not that's the cause of the error is hard to say. The purpose of a function is to return a value. Your function doesn't return a value. If you want the computer to do some stuff, use a subroutine. Your function also doesn't have a type defined. I looked at Microsoft's documentation on Function and they don't say what type a function defaults to. Did I miss this somewhere? Anyway, I think what you need is your own event subroutine instead of a function. There's an example here: MouseDown.

--------------------
Robert Crouser
Go to the top of the page
 
June7
post Sep 28 2019, 02:55 AM
Post#4



Posts: 968
Joined: 25-January 16



An event property can be set to a function that doesn't return a value. I have done this instead of creating event procedure code. I build one function that multiple buttons can call. Example:
CODE
Private Function HandleButtonClick(intButton As Integer)
Dim strLabNum As String
strLabNum = Nz(Me.ctrSampleList!LABNUM, "")
Me.tbxLABNUM.SetFocus
Select Case intButton
    Case 1 'Login sample
        strLabNum = NewSample
        Me.tbxLABNUM = strLabNum
        DoCmd.OpenForm "Sample", acNormal, , "LabNum='" & strLabNum & "'", , , strLabNum & "," & "Login"
    Case 2 'Transfer sample data
        strLabNum = NewSample
        DoCmd.OpenForm "Transfer", acNormal, , "LabNum='" & strLabNum & "'"
    Case 3 'Print workcard
        PrintWorkcard
    Case 4 'Batch print workcards
        DoCmd.OpenForm "MultipleCards", acNormal, "", "", , acDialog, "Workcards"
    Case 5 'Logout sample
        LogoutSample
    Case 6 'Edit sample info
        DoCmd.OpenForm "Sample", acNormal, , "LabNum='" & strLabNum & "'", , , strLabNum & ",Edit" & IIf(Not IsNull(Me.ctrSampleList!DATEOUT), "Report", "")
    Case 7 'Enter test data
        EnterTestData
    Case 8 'Delete the selected Query
        If Form_Menu!Permissions = "admin" Or Form_Menu.cbxUser = Me.lbxQueries.Column(1) Then
            CurrentDb.Execute "DELETE * FROM AdvancedQueries WHERE QueryName='" & Me.lbxQueries & "'"
        Else
            MsgBox "Only Administrator or original builder may delete custom query.", , "InvalidAction"
        End If
        Me.lbxQueries.Requery
End Select
Me.tbxLABNUM.SelStart = 6
Me.tbxDate = Null
End Function

However, I have only done this with Click event: =HandleButtonClick(1).

I just tested form MouseUp and it doesn't execute - no error message, nothing happens.

This post has been edited by June7: Sep 28 2019, 03:09 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
arnelgp
post Sep 28 2019, 03:02 AM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


hello, you do not declare a Tempvars as Public.
it is already public you just add the
variable in Tempvars collection:
CODE
Public Function isNow()

    If IsNull(Tempvars("tvNow")) Then _
            Tempvars.Add "tvNow", Now()

    isNow = ([TempVars]![tvNow] = Now)

End Function

This post has been edited by arnelgp: Sep 28 2019, 03:02 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
projecttoday
post Sep 28 2019, 03:06 AM
Post#6


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


June7, your code indicates that Access functions are somewhat flexible. Okay. But the question is do you think it's worth tina's while to try an event procedure since, as you say, the code doesn't work?
Another possibility is the tempvar. This is something relatively new and it could be throwing it off. You could try substituting a global variable.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Sep 28 2019, 03:07 AM
Post#7


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


Just saw arnelgp's post. Note he assigns the function a value. As I mentioned, if that doesn't work you could try a global variable and see what that does.

--------------------
Robert Crouser
Go to the top of the page
 
June7
post Sep 28 2019, 03:22 AM
Post#8



Posts: 968
Joined: 25-January 16



Problem with global or public variables is they lose value during run-time error. Can be a headache in debugging. In spite of that, I have used a few. TempVars don't lose value. However, I've never used TempVars.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
projecttoday
post Sep 28 2019, 03:51 AM
Post#9


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


The question is, does the tempvar cause the problem? It could be eliminated temporarily for testing. I've never use tempvars either. If neither tempvars nor global vars works then the alternative is writing to a table.

For what it's worth I think functions should be used in a situation that calls for a function. Otherwise, use a subroutine. I realize I'm whistling in the dark here.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Sep 28 2019, 04:06 AM
Post#10


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


A couple of other points. You point about debugging is good but remember when you step through the code you can see the global variable. I would definitely give a global variable a try if arnelgp's code doesn't work.

Also, is there any reason to use a tempvar if a global variable is sufficient?

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Sep 28 2019, 04:36 AM
Post#11


UtterAccess VIP
Posts: 11,689
Joined: 6-December 03
From: Telegraph Hill


>> calling the function from form event property lines on OnMouseUp event property and OnKeyUp event property, in the forms' Property sheets <<

This suggests that the function is called from multiple forms.

Check each property where it is called and ensure it is called with trailing brackets.

ie: =isNow()

and not: =isNow

Unlike VBA, the brackets are mandatory when used in an expression.

I suspect the debate over TempVar vs Global Variable is a red herring.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Sep 28 2019, 05:52 AM
Post#12


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


Actually, Arnel is on the right track, IMO.

The Tempvars Collection is not part of the standard Access VBA variable collection.

What Arnel did was, perhaps, redundant, but accurate. Except for the case where you want to create a Function that can be used in the event property line on the property sheet, which is the task here, Tempvars don't need to be wrapped in a function that way.

You do not declare Tempvars as public because they are public. One you set the value of a Tempvar anywhere in any module, that value persists. One argument for using Tempvars instead of global variables, in fact, is that their values persist even in the event of an error that would normally reset a variable declared in a standard module.
This post has been edited by GroverParkGeorge: Sep 28 2019, 06:06 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 28 2019, 06:04 AM
Post#13


UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA


Here's the functional equivalent for initializing Tempvars as opposed to declaring Global Variables.

CODE
Public Function TempVarInit()
  
     TempVars.Add Name:="strVariableOne", Value:=VBNullString
     TempVars.Add Name:="strLongIntOne", Value:=0
     TempVars.Add Name:="dtDateFieldOne, Value:=Null


End Function


Adding the tempvar to the tempvar collection initializes it with a default value, which I prefer to set explicitly so it is predictable.

When you CHANGE the value of any Tempvar:

CODE
      TempVars.strVariableOne = Me.txtNewValueSetinThisControl


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Sep 28 2019, 06:51 AM
Post#14


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


Personally I don't use TempVars either.
I find the only time that global variables lose their value is during initial development work.
Therefore its a useful indicator of an underlying issue which using TempVars would 'mask'.
I prefer to find the cause and deal with it

However I think that discussion about the merits of tempvars vs global variables or between using subs/functions are both beside the point in this case.

In post #2, I wrote:
QUOTE
I see no reason for tempvars or a UDF when you can just use =Now() as the control source or as its default value.
Similarly why are those (mouse up /key up) events being used?


So far nobody has responded to either of those points & I remain unconvinced about the need for any of this.
If anyone can see a good reason in this case, please do let me know

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Sep 28 2019, 06:56 AM
Post#15


UtterAccess VIP
Posts: 11,689
Joined: 6-December 03
From: Telegraph Hill


Perhaps another form wants to report the last accessed time of another form - its ControlSource can be set to the tempvar ?

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Sep 28 2019, 08:25 AM
Post#16


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


It looks like she wants to record the time a mouse was last clicked or moved.

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Sep 28 2019, 10:19 AM
Post#17


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


Whilst either of the last two replies are possible reasons for noting the date/time, using the built in function =Now() would seem to work perfectly well

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Sep 28 2019, 10:32 AM
Post#18


UtterAccess VIP
Posts: 11,689
Joined: 6-December 03
From: Telegraph Hill


Say I have Form1, Form2 and Form3.

On Form1 I have a control which shows the last mouse action time on Form2 and Form3.

I could get the mouse actions on those 2 forms to write directly to the control on Form1. But I would also have to check whether it was open first etc etc.

Or I could set the ControlSource ot the control on Form1 to a function returning the value of a global variable, and just have the mouse actions on Form2 and Form3 update the variable.

Then, Form1 would pick up the value when it was open and it wouldn't matter when it was closed.

Or, I could use a TempVar which means I don't even have to create a function to return the value of the global variable since it has that functionality built in, and also the bonus that the value will remain when that unhandled exception I was too lazy to test for and discover occurs!

wink.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Sep 28 2019, 10:52 AM
Post#19



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


thanks everyone for your responses. to clarify my purpose: my FE db provides the user interfaces for a lot of small "utilities", mostly looking things up on lists, etc. it's loaded to 50-60 PCs around the building, and is used intermittently throughout the day. over the years, we've had the problem of the db being left open on various machines, locking open some BE dbs and preventing various actions from being completed maintenance-wise.

so, about 10 years ago, i added some code to track use of the FE db; if it sits idle for x minutes, it shuts itself down. in the intervening years, i've added additional forms and not always remembered to add the "in use" code, leaving me with inconsistent results of course. so now, in converting from A97 to A2016, i decided to clean up the db and standardize handling of the "in use" issue across all forms. so, one variable that tracks last use of any form in the db, and one function - so that it can be called from the event property line, which you can't do with a sub AFAIK - to update that variable.

CODE
Public Function TempVarInit()
  
     TempVars.Add Name:="strVariableOne", Value:=VBNullString
     TempVars.Add Name:="strLongIntOne", Value:=0
     TempVars.Add Name:="dtDateFieldOne, Value:=Null

End Function

thanks, George, for the code above. coming straight from A97 to A2016, i've been (still am, mostly) woefully ignorant of many things in regards to things .accdb. i've been cobbling together what information i can get from the internet while trying to complete my portion of the conversion project that's underway, as quickly as possible. my tempvars have been working as i declared them, but i'd rather do things the "right" way, especially when i'm going down a dark and lonely road and somebody turns on a streetlight here and there! ;)

so i'll change my variable declarations and see what happens. nobody mentioned seeing any mistakes in the way i'm calling the function from the form property lines, so i'll assume that continues to be good. and ditto the function itself; good to know i haven't had such a stupid attack that i've forgotten how to write a simple function!

QUOTE
Check each property where it is called and ensure it is called with trailing brackets.

ie: =isNow()

and not: =isNow

thanks, David, i used the following code to add the function call to almost all of my forms, so yes the function calls are consistent. but now i'm wondering if i managed to corrupt those forms in the process. again, the code was cobbled together from two or three websites.

CODE
Public Sub isUpdateFormsDesign()

    Dim frm As Form, str As String
    Dim i As Integer
    
    For i = 0 To CurrentProject.AllForms.Count - 1
        str = CurrentProject.AllForms(i).Name
        If Left(str, 5) = "frm00" Then
            ' do nothing, skip this form
        ElseIf InStr(str, " ") > 0 Then
            ' do nothing, skip this form
        Else
            DoCmd.OpenForm frm.Name, acDesign
            DoCmd.OpenForm CurrentProject.AllForms(str).Name, acDesign
            Set frm = Forms(str)
            frm.KeyPreview = True
            frm.OnKeyUp = "=isNow()"
            frm.OnMouseUp = "=isNow()"
            DoCmd.Close acForm, frm.Name, acSaveYes
        End If
        Set frm = Nothing
    Next i
    
    MsgBox "done"

End Sub

thank you all again for your time and responses. i'll post back the results after I change my Tempvars declarations and try again.

tina

EDIT: wow, just looked at my posted code again, and saw that i'm calling the OpenForm twice. duh. i wonder if that screwed up the forms, right there. i'm going to copy my backup and run the code again, with the procedure fixed. i'll post back with results, but if anyone has comments or suggestions in the meantime, i'll be happy to see them. :)
This post has been edited by tina t: Sep 28 2019, 11:23 AM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
arnelgp
post Sep 28 2019, 11:34 AM
Post#20



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you might want to hide the form while editing.
this will prevent of instant flashing (showing/disappearing)
of the form in access MDIClient window.
CODE
DoCmd.OpenForm frm.Name, acDesign,,,,acHidden

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 04:31 AM