Call VBA Looping Function from Query (1 Viewer)

GDTRFB · Oct 31, 2018

Hi all,

Question: is it possible to call a VBA function which loops through the records from within the same query?

I have a simple parent-child table relationship where I’ve created a VBA function (e.g. SomeFunction) to loop through the child table to:

1. Identify child values associated with the parent foreign key (FK)
2. Assign them to variables within the function
3. Perform calculation on the variable

Then it executes another loop iteration to the next FK.

Something like….

Child PK ParentFK ChildField
1 2 Value1
2 2 Value2
3 3 Value1
4 3 Value2

Loop values are assigned variables (e.g. Var A = Value 1, Var B = Value 2), then the function performs a calculation such as:

SomeFunction = A + B

Then the loop starts a new iteration.

SomeFunction() is called from a query column. But….

The query only returns the last calculated value since the values are overwritten as it completes each loop iteration.

Question: Is it possible to assign each loop iteration’s value to a row in the query associated with the FK?

Many thanks for your input.

pbaldy · Oct 31, 2018

I don't know about looping the same query, but generally in order for a function to return a distinct value for each record you have to pass something to it, like:

SomeFunction(ParentFK)

even if that value isn't used in the function.

The_Doc_Man · Oct 31, 2018

Anything is possible but the question is always what you actually want to implement.

Doing what you describe using VBA has the potential to lose a race with a tortoise because of the number of VBA steps involved for each parent entry.

You MIGHT want to consider (if it is possible) that you could create a JOIN between parent and child and then create an aggregate query to do the computation (if it is a simple sum, for example). Then do a GROUP BY on the FK so that the aggregates are formed for each FK separately. IF this is possible, it would be far faster than using VBA to do it.

Now, if the nature of the computation isn't compatible with any of the standard aggregates, then sure, you could do something along the lines of what you said. But remember that VBA is INTERPRETIVE, not compiled, and therefore is a LOT slower than queries. So speed will become an issue.

IF you are going to do this, the function will have to be declared as Public in a general module because otherwise, SQL won't be able to find it.

arnelgp · Nov 1, 2018

what are you trying to accomplish? a running Total for the Child table.
you can do it in a query without the need of a Function:

SELECT child.childPK, child.parentPK, child.Value, (SELECT SUM(T1.child.Value) FROM child AS T1 WHERE T1.parentPK=child.parentPK AND T1.childPK <= child.childPK) As RunningSum FROM child ORDER BY child.parentPK, child.childPK;

MajP · Nov 1, 2018

The answer to your question is definitely yes. One very common example is a function that concatenates the child records for a PK. In your example however, you would have to pass to the function the ParentID and Child ID. The function loops the child records for the parent ID and "kicks out" after reaching the child ID.

However, as pointed out do this in SQL.

GDTRFB · Nov 1, 2018

hi everyone, thanks for all the input.

with respect to SQL, as many of you mentioned, SQL would be preferable but unfortunately the calculation logic I need to perform is beyond what SQL aggregate functions (e.g. running sum) and subqueries can do. I showed a simple sum, but in reality I need to perform custom calculations for different FK's.

as far as passing the ParentID and ChildID to the function.....this seems interesting.
I tried passing the FK to the function (e.g. SomeFunction(FK)) and it created an infinite loop.

can someone provide a little more instruction on how to do try this?

Thanks again

GDTRFB · Nov 1, 2018

to help put this into context, here's an example of one the loops. thanks

Code:

[COLOR=black][FONT="Verdana"]    i = rs!ParentFK                         'set ParentFK[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]    VarA = rs!ChildField1                ‘assign variable ordinal position[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"] [/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]Do Until rs.EOF                             ‘loop through child records with same ParentFK[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]    rs.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        Do While rs!ParentFK = i[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]            VarB = rs!ChildField1[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]                rs.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]            VarC = rs!ChildField1[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]                rs.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]            VarD = rs!ChildField1[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        [/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        '*%%%%%[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        SomeFunction = (VarA /5 - VarB) + (VarC * VarD /2)            [/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        [/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        [/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        rs.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        i = rs!ParentFK           ‘assign new ParentFK        [/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        VarA = rs!ChildField1   ‘assign variable ordinal position[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        rs.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]        Loop[/FONT][/COLOR]
[COLOR=black][FONT="Verdana"] [/FONT][/COLOR]
[COLOR=black][FONT="Verdana"]Loop[/FONT][/COLOR]

MajP · Nov 1, 2018

Code:

SELECT 
 TableChildren.Child_ID, 
 TableChildren.Parent_ID, 
 TableChildren.ChildName, 
 TableChildren.ChildAge, 
 GetYoungerSiblings([Parent_ID],[Child_ID]) AS YoungerSiblings
FROM TableChildren
ORDER BY 
 TableChildren.Parent_ID, 
 TableChildren.ChildAge;

Code:

Public Function GetYoungerSiblings(ParentID As Long, ChildID As Long) As String
  Dim strSql As String
  Dim rs As DAO.Recordset
  Dim strOut As String
  strSql = "Select * from TableChildren where Parent_ID = " & ParentID & " Order By ChildAge"
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    If rs!child_id = ChildID Then Exit Do
    If strOut = "" Then
      strOut = rs!childName
    Else
      strOut = strOut & "; " & rs!childName
    End If
    rs.MoveNext
  GetYoungerSiblings = strOut
  Loop
End Function

Code:

Child_ID	Parent_ID	ChildName	ChildAge	YoungerSiblings
2	1	Sara	2	
3	1	Steve	4	Sara
4	1	John	6	Sara; Steve
1	1	Mike	12	Sara; Steve; John
5	2	Alex	2	
7	2	Nina	6	Alex
6	2	Tyler	15	Alex; Nina
8	2	Drue	18	Alex; Nina; Tyler

MajP · Nov 1, 2018

Not sure I get your example. Can you provide more details of what you have table wise and what you want? Does this have to be a function to return to the query or can it write the results some where. These loopings that return back to a query can be very resource intensive.

MajP · Nov 1, 2018

Also this is a bad idea

Code:

Do While rs!ParentFK = i

You should be a recordset that only returns the parent records for i. Which means you may have two loops. One function loops the dinstinct parent ID, and passes that to another function to create a resordset based on that ID and loop the children.

GDTRFB · Nov 1, 2018

Thanks MajP. You description and examples are very helpful.

In a nutshell, i'd like to create a function, called from a query, that assigns child field values to variables from which I can perform custom calculations within the function send the results to the query.

I thought about sending the results of the function to assigned columns in a temporary table, but wanted to see if I can do this within a query.

my table structure if very similar to your example above.

MajP · Nov 1, 2018

Writing to a temp table or even the original is often easier. If the amount of records is large and the calculations are expensive you will be better off persisting this information. These types of queries can quickly become unusable. Often you wait a long time for them to produce and when you scroll the datasheet it starts requerying and then you are locked back up.

MajP · Nov 1, 2018

Is this really what you are doing, or just an example

Code:

(VarA /5 - VarB) + (VarC * VarD /2)

It seems to me that every parent has four children, and regardless of which child record is passed it takes the first child value / 5 - secondChild value + third child value * 4th child /2. This definitely will not change for each child.

I am taking a wild guess and that is not what you want to do. I asume you want to move to assigned child and look forward from there to the following three children if they exist and return that calculation.

GDTRFB · Nov 12, 2018

Thanks MajP for your help. I re-thought my approach based on your example and ended up going with a temporary table.

Thanks again

Call VBA Looping Function from Query (1 Viewer)

GDTRFB

Registered User.

pbaldy

Wino Moderator

The_Doc_Man

Immoderate Moderator

arnelgp

..forever waiting... waiting for jellybean!

MajP

You've got your good things, and you've got mine.

GDTRFB

Registered User.

GDTRFB

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

GDTRFB

Registered User.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

GDTRFB

Registered User.

Similar threads

Users who are viewing this thread