?
Solved

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

Posted on 2014-11-04
14
Medium Priority
?
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 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 4

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 38

Assisted Solution

by:PatHartman
PatHartman earned 100 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 4

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 1700 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 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 4

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
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 1700 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 4

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 1700 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 4

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 1700 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 4

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 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