Solved

how to call a forms module function from within its sql property?

Posted on 2014-11-04
14
234 Views
Last Modified: 2014-11-10
Hi experts,
I have a public function declared in a forms module, and would like to use it as part of the record source sql of the form, how do I accomplish that?
FYI- I am able to use it as a controls source of the control of the form, however when trying to use it in sql with syntax Forms!FormName.FunctionName(Param1,Param2) I get a msg "Undefined function.."
0
Comment
Question by:bfuchs
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 40422650
The function must be in a standard module, not a form module (which is actually a class module).

When you call it, if:

1. You pass no arguments, it is called once at the start of the query
2. If you pass a field as an argument (and it doesn't matter which or if you even use the value in the function), it gets called for each row.
3. If you pass a field and the column has criteria on it, the function gets called 2x for each row.

In the query, you call it just like any VBA expression, like Date(), so it might look like this:

TotalAmount:CalculateTotalInvoice([InvoiceID])

Jim.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40422751
Hi Jim,
The problem is that I have the same function but with different needs for each form, therefore keeping them in the forms module would be in general more organized than having to store everything in the database standard modules.
Perhaps you know of a way around this,
Basically what I need is a way to filter a given form according to the results of a function, is it possible to accomplish that without having to include the function as part of the sql?
and what about if I use it in the forms module, in the on open event for example,  me.recordsource = "Select table1.*, MyFunction(table1.id) as Field1 from Table1", do you think that will work?
Thanks,
Ben
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 25 total points
ID: 40422801
If your BE is Jet/ACE, it doesn't much matter whether you use filters in a form or criteria in a query.  If the BE is SQL Server et al, then it matters very much.  For a "real" RDBMS, it is best to let the server do the heavy lifting and so you would always use criteria in the query.  That minimizes the number of rows, your process will request.  When you use filters, Access requests all the rows for your recordsource and then filters locally.

If you run the function once and filter on the result, then you can embed the form's recordSource in code and replace it each time you run the query.  The code would probably go in the click event of a button or in the afterUpdate event of some control.

Me.RecordSource = "Select ... From ... Where Somefield = " & YourFunc() & ";"
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40422880
Hi Pat,
If you run the function once and filter on the result, then you can embed the form's recordSource in code and replace it each time you run the query
What do you mean run it once? and also what  are you referring to by replace it each time?
let me give you some details of how I am planning to use,
I have a function Called EmpHave30HoursInSched(EmpID,DayStarted) as bollean. both parameters are fields of the forms record source.
now I want to be able to filter the form according to the results of this function.
Thanks,
Ben
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 425 total points
ID: 40424083
Hi Ben,

as far as I remember you are using a SQL Server as backend and therefore the only correct answer is: Create your function on the server as UDF or as stored procedure where you can also create a parameter for the filter value so that the server only returns the filtered record, like Pat recommended above.

What you are trying to is, calling a public function as part of the field list of the SELECT command, which is not possible if it is not a public function in a standard module.

If you only want to display the result of your function it is possible to create a textbox and use
=EmpHave30HoursInSched(EmpID,DayStarted)
which will call the public function in your form module. That's not very fast and another disadvantage is that it is also not usable to filter on this textbox.

If you can show us the code of this function it's possible to show a solution for a stored procedure or UDF on SQL Server (if this is still your backend).

Cheers,

Christian
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 50 total points
ID: 40424179
<<The problem is that I have the same function but with different needs for each form, therefore keeping them in the forms module would be in general more organized than having to store everything in the database standard modules.>>

  Agreed, but you can't call a function in a form or report module directly from a query.  Has to be in a standard module.    You could call a function in a standard module, then have it call the function in the form though.   But queries only understand how to get to procedures in standard modules.

<<Perhaps you know of a way around this,
Basically what I need is a way to filter a given form according to the results of a function, is it possible to accomplish that without having to include the function as part of the sql?>>

 The others have given you some thoughts, but I guess it boils down to what your doing in the function that determines the filtering.

Might be helpful to post an actual example of one of those if your still stuck on this.

Jim.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40424984
Hi Bitsqueezer,
How are you? long time didn't hear from you..-:)

@Bit, Pat
I am posting the code, actually there are 3 small functions in the form in question, all used in the same way.

Public Function EmpHave30HoursSched(empid As Long, Day As Date) As Boolean
    Dim db As Database, rs As Recordset, sSql As String
    Set db = CurrentDb
    sSql = "SELECT Sum(MyDateDiff([From], [To])) as ApptHours From PatientsEmployeesSchedule"
    sSql = sSql & " where EmployeeID = " & empid & " and Day between #" & FirstDayOfWeek(Day) & "# and #" & FirstDayOfWeek(Day) + 6 & "#"
    Set rs = db.OpenRecordset(sSql)
    If rs.EOF Then Exit Function
    rs.MoveFirst
    If rs(0) >= 1800 Then
        EmpHave30HoursSched = True
    Else
        EmpHave30HoursSched = False
    End If
    Set rs = Nothing
    Set db = Nothing
End Function

Public Function EmpHave4Weekends(empid As Long, Day As Date) As Boolean
    If tCount("*", "PatientsEmployeesSchedule", "EmployeeID = " & empid & " And Day between #" & FirstDayOfWeek(Day) - 1 & "# and #" & FirstDayOfWeek(Day) & "#") > 0 Then
        EmpHave4Weekends = True
    Else
        EmpHave4Weekends = False
    End If
End Function

Public Function EmpMissHoliday(empid As Long, Day As Date) As Boolean
    Dim v As Variant
    v = Nz(tLookup("HolidayDate", "tblHoliday", "HolidayDate between #" & FirstDayOfWeek(Day) & "# and #" & FirstDayOfWeek(Day) + 6 & "#"), 0)
    If v <> 0 Then
        If tCount("*", "PatientsEmployeesSchedule", "EmployeeID = " & empid & " And Day = #" & v & "#") > 0 Then
            EmpMissHoliday = False
        Else
            EmpMissHoliday = True
        End If
    Else
        EmpMissHoliday = False
    End If

End Function

Public Function FirstDayOfWeek(Day As Date) As Date
    FirstDayOfWeek = Day + 1 - WeekDay(Day)
End Function

Public Function MyDateDiff(Optional dFrom, Optional dTo) As Integer

    If IsMissing(dFrom) Or IsMissing(dTo) Then
        MyDateDiff = 0
    ElseIf dFrom = dTo Then
        MyDateDiff = 780
    Else
        MyDateDiff = Nz(DateDiff("N", dFrom, dTo))
        If MyDateDiff < 0 Then MyDateDiff = 1440 + MyDateDiff
    End If


    
End Function 

Open in new window


If you can show us the code of this function it's possible to show a solution for a stored procedure or UDF on SQL Server (if this is still your backend).
Yes, this would be the best option re performance, I would appreciate that.
btw, we upgraded from SQL 2005 to SQL 2008, if that matters.

Thanks,
Ben
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 425 total points
ID: 40425238
Hi Ben,

thanks, I'm fine, hope you, too...:-)

Here are your functions as T-SQL UDFs, partially untested of course as I do not have your tables.

CREATE FUNCTION dbo.fnMyDateDiff 
(
	@dFrom	AS	smalldatetime = NULL,
	@dTo	AS	smalldatetime = NULL
)
RETURNS smallint
AS
BEGIN
	DECLARE @Result smallint

	IF @dFrom IS NULL OR @dTo IS NULL
		SET @Result = 0;
	ELSE IF @dFrom = @dTo
		SET @Result = 780;
	ELSE
	BEGIN
		SET @Result = ISNULL(DATEDIFF(n,@dFrom,@dTo),0);
		IF @Result < 0 SET @Result = 1440 + @Result;
	END;
	RETURN @Result;
END
GO

CREATE FUNCTION dbo.fnEmpHave30HoursSched 
(
	@empid	AS	int,
	@Day	AS	smalldatetime
)
RETURNS bit
AS
BEGIN
	DECLARE @Result AS bit = 0;

	IF (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid 
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)) >= 1800
		SET @Result = True;

	RETURN @Result;
END
GO

CREATE FUNCTION dbo.fnEmpHave4Weekends
(
	@empid	AS	int,
	@Day	AS	smalldatetime
)
RETURNS bit
AS
BEGIN
	DECLARE @Result AS bit = 0;

	IF (SELECT COUNT(*)
		  FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day)-1 AND dbo.fnFirstDayOfWeek(@Day)) > 0
		SET @Result = True;

	RETURN @Result;
END
GO

CREATE FUNCTION dbo.fnEmpMissHoliday 
(
	@empid	AS	int,
	@Day	AS	smalldatetime
)
RETURNS bit
AS
BEGIN
	DECLARE @Result AS bit = 0;
	DECLARE @V		AS smalldatetime;
	
	SET @V = (SELECT HolidayDate
			    FROM tblHoliday
			   WHERE HolidayDate BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day) + 6);
	IF @V IS NOT NULL
	BEGIN
		IF (SELECT COUNT(*)
			  FROM PatientsEmployeesSchedule
			 WHERE EmployeeID = @empid 
			   AND [Day] = @V) <= 0
			SET @Result = 1;
	END;
	RETURN @Result;
END
GO
CREATE FUNCTION dbo.fnMyDateDiff 
(
	@dFrom	AS	smalldatetime = NULL,
	@dTo	AS	smalldatetime = NULL
)
RETURNS smallint
AS
BEGIN
	DECLARE @Result smallint

	IF @dFrom IS NULL OR @dTo IS NULL
		SET @Result = 0;
	ELSE IF @dFrom = @dTo
		SET @Result = 780;
	ELSE
	BEGIN
		SET @Result = ISNULL(DATEDIFF(n,@dFrom,@dTo),0);
		IF @Result < 0 SET @Result = 1440 + @Result;
	END;
	RETURN @Result;
END
GO

Open in new window


You maybe must adjust the names and the schema "dbo" if you want another one. The code is compatible with SQL Server >=2008.

By the way, it is a really bad idea to open recordsets in a function which Access should call in every row of a query, especially if you use "CurrentDb" each time. This is a performance killer. Set the db variable on module level or create a standard module function like this:

Private prv_db As DAO.Database
Public Function fnDB() As DAO.Database
    If prv_db Is Nothing Then Set prv_db = CurrentDb
    Set fnDB = prv_db
End Function

Open in new window


This will return always the same prv_db object which you can use everywhere. The same with the recordset, open it once when the form loads and then only search in the recordset, close it when the form closes.
But in your function even that is not needed as you only need to use DSum to get the result.

Moreover you should strongly avoid any variable or field name which conflicty with keywords, "Day" is really no good name.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40425491
Hi Bitsqueezer,
I am also fine, thanks..
First I am glad you provided the SQL functions, as I am not so familiar with t-sql  language, but believe this is the right way to go in my situation.
Just give me some time to test & will keep you posted.
Set the db variable on module level or create a standard module function ...
 The same with the recordset, open it once when the form loads and then only search in the recordset..
I was using it as control source of some check boxes on the form, something like ControlSource = "EmpHave30HoursSched(empid, Day)"
how exactly would that work for me?
the request to be able to filter the form accordingly I just got it, and then is when I posted this question, as this would require the functions to be part of the sql..

thanks,
Ben
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 425 total points
ID: 40425609
Hi Ben,

it's always a good idea to implement all calculations inside of the SQL command instead of using an expression on a control. Expressions are calculated as last and so you always see a delay between listing the record and calculating the expressions which doesn't look good.

In your case, as you use SQL Server, it's easy now with the UDFs, simply insert that into your SELECT command like:

SELECT a,b,c,(Select dbo.YourFunction(a,b)) As FunctionResult
FROM...

Open in new window


SQL Server keeps the result editable as it knows that the function's return value is read only. You can then bind the result of the function to a control on a form and it will be displayed immediately. If you change something in the frontend which affects parameters used in the UDF it will be recalculated.

In the example above you simply don't need a recordset as it can be done with one single DSum function, but in similar cases where you i.e. search through a recordset or anything like this, declare the recordset variable on module level and open it in Form_Load, then keep it open until the form closes. On this way you don't need to create a connection each time, reload the recordset each time, and so on. That costs a lot of performance and especially if many users are online an unnecessary network load.

"CurrentDb" is the most wrong-used thing in Access: This is not the reference to your current database workspace, it is a function which copies all the workspace objects of Access (the "current database workspace") and returns the result as a DAO.Database object. As your table definitions and so on can also be changed at runtime this makes sure that you always have the most actual definitions in the DAO.Database object, but in most cases you don't need that. So the function above creates this object once and use it the whole time the application is running. If you ever need to refresh that you can recreate the private object "prv_db" (in the example) with a new call to CurrentDb, but don't call that function each and every time, especially not in a continous form where such function would be called once for each row you display or in any kind of loop.
Another advantage of using such a function is that you don't need to declare a "db" variable each time you need access to the workspace object, you can simply use the function. In this example it would be "Set rs = fnDB().OpenRecordset...."

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40427207
Hi Bitsqueezer,

Thanks again for all those nice write-ups, will take some time until I get to apply them into the system, but I will definitely get to it, wait & see...

Just wondering after I create those UDF's, how do I get to use them in Access? as there is no way I can link Access (2000, 2003) to a UDF or SP in sql, the only way I guess is using a pass-through query, in this case, if I want to assign the results to a control in a form (that is no bound to the PT query), control source = "MyFunction(Param1, Param2)" will not work?

Thanks,
Ben
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 425 total points
ID: 40427815
Hi Ben,

that's why I wrote the sample SELECT command above which shows you how to use the UDF in a SELECT command.
You can of course use a Pass-Through-Query for that but a PT-Query is always read only. If that's enough for your form then it's OK. But if you want to have an editable recordset for your form then simply insert that into a view which can be linked like a table in Access. As I wrote above, only the result of the column which selects the UDF is read-only then, the rest is writeable.

After you have bound the form to this view you can simply bind the desired control to this column, it will be read-only of course, but the result is displayed immediately without delay (like with an expression).

Cheers,

Christian
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 40431708
Thanks to all experts participating in this thread.

@Bitsqueezer,
Your functions (with minor modifications) worked like a charm, great work!!
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40432488
I'm glad that I could help you...:-)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now