Passing a Control to a Function from a JET Query

"I'd like to be able to pass a control to a function from a JET Query."

Do you mean a control Object?  Or a control name ?

One issue is here:

Function GetControlName(ctl As Control) As String

Your argument is an Object but from the query you are passing a string "Form1", etc

SELECT CustomerID, CustomerName, GetControlName(GetFormControl("Form1","txtBox"))

I'm not looking to get the control's name, I'm only interested in passing the control to a function from a JET query.

The above was just an oversimplified illustration of passing off a control and getting something back.  The actual function I'm using is a recursive function that receives a control and returns a string but short as it was not hard to imagine that people would think it was the function that might be the problem not the fact that JET isn't letting me hand off the control as a parameter, (hence these really basic functions are provided to illustrate the point and I'd hoped not mislead people into thinking I had any interest in getting the name of a control.

If you look carefully you'll notice that GetFormControl("Form1","txtBox") correctly hands off two strings and returns an object which is then given to GetControlName() which expects to receive an object and return a string which JET should be able to display in a column.

The issue is that JET doesn't want to let me hand off a control inside a function, not even if the object is being returned by one of the functions inside a function.  Put another way, since I couldn't hand off a control directly I was hoping that GetFormControl() would allow me to snag the control indirectly and hand it off anyway.  Again, I didn't expect it to work but then I didn't expect to get a different error either.

Restated, the question is how to write a query in JET that hands of a control as one of it's parameters without having JET choke in the process.

Rephrasing my last sentence...
Restated, the question is how to write a query in JET that hands of a control to afunction as one of it's parameters without having JET choke in the process.

ok ... just to be clear:

1) You want to pass the name of a control (not a control object) to a function, right ?

and

2) Return what?  The value of the control?  And ... you also need to pass the Form name that the control is on?

mx

ok ... I get the same error.  I KNOW I've done this before.  Humm.

I want to pass the control to the function.  The function will return a string.

ok ... this combination works = returns what I have in the Text box:

Public Function GetFormControl(strForm As String, strControl As String)
     GetFormControl = Forms(strForm)(strControl)
End Function


SELECT Table1.FIELD1, GetFormControl("frmTest","Text3") AS Expr1
FROM Table1;

mx

Unfortunately that doesn't actually work.  By removing the SET clause we're no longer returning an Object but in this case the default property of the text box (.Value).  True, the function will return but it has not passed a control from the query nor has the query received a control back from the function.  It simply received two strings to identify the name of the form and control and then returned the value of the text box (true, it runs but it does so because we're not introducing the control as a parameter).

Perhaps it may make it more clear to actually post the code for which this is intended.

The function below receives a list box as it's only required parameter.  It then returns a comma delimited list of all values selected in the list box from column 0, or if a different column is defined then it pulls them from a different column.  It will also delimit each value with a character like an appostrophe if you want and so instruct the function to do so.

It's most useful for getting a comma delimited list of values selected without a whole bunch of code.  Works great from VBA but it must receive the list box as its parameter to work properly.

It's one of many functions I have that work in this meaner so simply coming up with a work around to provide what this function does doesn't really address the problem, I need JET to pass an object to a function so that the function can do it's job and return something back to JET that it can use (like a number or a string)

Function ListBoxIn(lst As Object, Optional lngCol As Long = 0, Optional strDelim As String = "", Optional lngItem As Long) As String
    If lngItem = -1 Then lngItem = lst.ItemsSelected.Count
    If lngItem Then ListBoxIn = ListBoxIn(lst, lngCol, strDelim, lngItem - 1) & ListBoxIn & IIf(lngItem - 1, ",", "") & strDelim & lst.Column(lngCol, (lngItem - 1)) & strDelim
End Function

Select allOpen in new window

"I need JET to pass an object to a function so that the function "

I may be missing something, but I'm not seeing how JET is going to pass an Object to a function (eg lst As Object ).  Where would JET get the object reference?  Like, you can pass just the Form object ([Form] or Me) around etc via code, but not from a query.

What is the output of the query ultimately supposed to generate ?

mx

In my haste I posted the wrong version of the function above.  Reposted below.  Also included is a simple .mdb that illustrates why it is used.

With a little imagination you can quickly see why this might come in quite handy used inside a query's WHERE clause using IN()....

Function ListBoxIn(lst As Object, Optional lngCol As Long = 0, Optional strDelim As String = "", Optional lngItem As Long = -1) As String
    If lngItem = -1 Then lngItem = lst.ItemsSelected.Count
    If lngItem Then ListBoxIn = ListBoxIn(lst, lngCol, strDelim, lngItem - 1) & ListBoxIn & IIf(lngItem - 1, ",", "") & strDelim & lst.Column(lngCol, lst.ItemsSelected(lngItem - 1)) & strDelim
End Function

Select allOpen in new window

ListBoxSample.mdb
ListBoxIn.PNG

So, for example the idea is to return a string of say ID's to be used for example in an IN clause

IN (3,7,22,77,102 .....)

So ... from the query, you need to pass exactly what?  The name of the form and the list box the form is on?  And then the function grabs the selected ID's in the list box, and concatenates a string and returns that to be used in an IN clause ... for example ?

mx

Attached you'll find the sample application reposted with a query in it named "qrySample"  The SQL is posted below.

The object reference would come from a form already open.  It could be on the other hand a list box on the same form that's query looks something like the one you see below, allowing the query to be updated to only items selected without having to do anything more than requery the list box.

How many times have we seen people want to have a list of items a person can select and then have another list box that includes only the items that were selected.  Now the 2nd list box wouldn't need any change to the query, a simple requery would do but then that only works if JET will receive an object as a parameter being passed to a function.

SELECT tblX10.X10
FROM tblX10
WHERE (tblX10.X10) In (ListBoxIn([Forms]![frmListBox]![List1]));

Select allOpen in new window

ListBoxSample.mdb

Regarding Post: 22081705    You're starting to get the idea.  See the attached .mdb above, it has the query illustrating the point included.

I have the mdb ... and I see what you are trying go do.  Let me mess with it.

mx

One last recap.  One fix and then general cleanup of the sample app attached.

SELECT [tblX10].[X10]
FROM tblX10
WHERE ([tblX10].[X10]) In (ListBoxIn([Forms]![frmListBox]![lstBox]));

Select allOpen in new window

ListBoxSample.mdb
ListBoxIn.PNG

IF I use this:

SELECT tblX10.X10
FROM tblX10
WHERE (((tblX10.X10) In (ListBoxIn("frmListBox","lstBox"))));


and this:

Function ListBoxIn(frmName As String, ctlName As String, Optional lngCol As Long = 0, Optional strDelim As String = "", Optional lngItem As Long = -1) As String
   
    Dim lst As Object
    Set lst = Forms(frmName)(ctlName)

End Function

You can call the function ... BUT ... if you add back in the two lines of code that get the string, you get a bunch of Compile errors - ByRef argument Type Mismatch on the 2nd IF statement - 'lst'

Try that and you will see what I mean.  If we can get around that, I may work.

The above shouldn't cause compile errors but it doesn't address the question either.  The objective here is to pass an object to a function from within a query.  The proposal above passes the Form Name (A String), a Control Name (A String) and then creates an object within VBA that is an object (a list box in this case).  Little difficulty continuing that code down the line to provide for the comma delimited lists, token values encapsulated with pound signs or apostrophes and the like but that defeats the whole objective.

AS previously posted... ID:22081332 this is but one of many procedures that receive an object variable for a parameter.  While we can always write code to avoid this it involves rewriting a bunch of code that already works really well.

At the end of the day we keep coming back to the same issue, Can JET pass an object to a function as a parameter, or by some other means can JET be coerced into doing so by some other means which is what GetFormControl() was all about, handing off strings to a function that brings back a reference to the object we were interested in to hand off to the function GetControlName().

JET seems happy to look at an object if the object returns a value but when it's still left at the object level it's seems most unhappy, even if it is not expected to do anything other than hand off a pointer to that object to another function for processing.

"The above shouldn't cause compile errors "

But is does .... when you add in the two IF statements.  Wonder why that is?

CallByName() is a nice theory, unfortunately it didn't work either.  If it would work I'd expect the syntax to be something like the code snippet below.

From VBA CallByName does return the object just as it should from which you can act on it as if you'd set your object variable normally or passed it as such.  From JET, same problem, it just doesn't like it.  I can't help but think the fact we have two totally different errors is telling us something but what?

SELECT tblX10.X10
FROM tblX10
WHERE ((tblX10.X10) In (ListBoxIn(CallByName([Forms]![frmListBox],"lstBox",2))));

Select allOpen in new window

UnDefinedFunction-CallByName-.PNG

Re: >> But is does .... when you add in the two IF statements.  Wonder why that is?

Can you post the code that was failing for you?  I might be able to offer insight as to why it failed and who knows, perhaps it will have some insight of its own to offer.

Sure: Basically your original IF statements, and my mods to the query. If you try to compile this, it gives the ByRef error I listed above ... and highlights 'lst' in the 2nd IF statement  >>  ListBoxIn(lst,
                                                                                                                          ---------------^^

SELECT tblX10.X10
FROM tblX10
WHERE (((tblX10.X10) In (ListBoxIn("frmListBox","lstBox"))));

Function ListBoxIn(frmName As String, ctlName As String, Optional lngCol As Long = 0, Optional strDelim As String = "", Optional lngItem As Long = -1) As String
   
    Dim lst As Object
    Set lst = Forms(frmName)(ctlName)
    If lngItem = -1 Then lngItem = lst.ItemsSelected.Count
    If lngItem Then ListBoxIn = ListBoxIn(lst, lngCol, strDelim, lngItem - 1) & ListBoxIn & IIf(lngItem - 1, ",", "") & strDelim & lst.Column(lngCol, lst.ItemsSelected(lngItem - 1)) & strDelim

End Function

I've highlighted and set in bold the point upon which the compile error occurs.  It is correct to fail there.  lst is an object variable as declared in the first and second lines of code.  ListBoxIn() is a function that receives a String not an object as its first parameter.  Thus when trying to call the function again it fails because the data type is no longer the same and it is on the 4th line of code as sited where the 2nd If statement is found.  For what its worth the only reason the first If exists is to allow the user to be lazy and not provide the number of items contained within the list box.  Absent that information -1 is the default which tells the function that it needs to look and see just how many items were in fact selected.
Function ListBoxIn(frmName As String, ctlName As String, Optional lngCol AsLong = 0, Optional strDelim As String = "", Optional lngItem As Long= -1) As String
    Dim lst As Object
    Set lst = Forms(frmName)(ctlName)
    If lngItem = -1 Then lngItem = lst.ItemsSelected.Count
    If lngItem Then ListBoxIn = ListBoxIn(lst, lngCol, strDelim, lngItem - 1) &ListBoxIn & IIf(lngItem - 1, ",", "") & strDelim& lst.Column(lngCol, lst.ItemsSelected(lngItem - 1)) & strDelim
End Function

In any case, the code is failing for two reasons, 1 the first parameter passed is an object when the function expects it to be a string and 2nd because there are only 4 parameters being paassed to a function that has 5 (the 2nd one seems to have been skipped).

I see where you're going here, you're trying to avoid passing the object from JET to the function and using the function to extrapolate the object on its own.  Perhaps JET just can't do it but if that's the case then that would be the answer.  I require no assistance in how to get VBA to render my objects for me if JET will not pass them but what an inconvenience.  It's a little shocking in fact to think that JET will not allow me to simply pass an object to a function as it does any other value.  I'm not looking for JET to eat the object for lunch, just hand it off to the function and let the function do the work.

"For what its worth the only reason the first If exists is to allow the user to be lazy and not provide the number of items contained within the list box"

I would say that is a Feature!  Because, lets assume that the # of items in the list will be 'dynamic' in some way.

Yes, I see that the repetitive call using LST would be a problem.  I was multiplexing between meetings and kinda hurried with that.  What's annoying is that I am positive I have done this in the past.  For sure, I have read control names (Forms.xxx.yyy) out of a table using VBA along with the Eval() function to do cool stuff..

Anyway, Marcus will have the answer/reasons ... not to worry!

mx

The lngItem paremter if defined by the user would usually make reference to the object  lstBoxName.ItemsSelected.Count so the number ouldn't be off but in doing it this way the user simply doesn't have to put this extra information in.

I eagerly awate what Marcus has to say.

"I eagerly awate what Marcus has to say."
It WILL be definitive !!  If it can be done, he ( or Leigh Purvis) will know how).

Standing by .... .... ....

mx

I've been using a) for years.  My entire 'report engine' / report setup is based (among other things) programmable multiple ... multi-select list boxes ... which use a class module to fill simple temp tables, which are then used in the main query that drives a given report ... and yes, it IS FAST.

I had a feeling that even if this did work, it would probably be slow. BUT ... the idea of a couple lines of code to get a list in simple situations would be pretty cool.  And still, Rick's function DOES do that ... so, all is certainly not lost.

As usual ... thank you Marcus for the detailed explanation. This Q will now NOT destruct in 30 seconds, lol.

mx

THANKS harfang SO MUCH for your input.

Certainly it's a disappointment to find JET can not fathom objects or even hand them off to functions but this doesn't come as a complete surprise.  I've never observed JET process anything that didn't ultimately get resolved into a value making the object proposition dubious perhaps but as you can see, I had reasons that meant I had to try.  Wouldn't have been the first time to find someone else knew how to do something I suspected could not be done, hence the question, but I'm pefectly content to accept that it can not be especially when the rational is so consistent with all the facts available.

At this point I'd say the question has now been asked an answered so I'll keep it open awhile longer in case someone things they have something more to contributed to the topic.  Other than that it's time to give credit where credit is due.  Thanks again to MX for trying so hard and harfang for getting to the bottom of it.

Rick

Oh, by the way, there's another version of this function that does everything in 1 line instead of 2.

The first one can be found at...
https://www.experts-exchange.com/questions/23579815/For-Experts-Obfuscated-Access-Contest.html?cid=238&anchorAnswerId=2208510#a2208510

harfang was kind enough to dress it up a bit and posted a tailored version of the same at...
https://www.experts-exchange.com/questions/23579815/For-Experts-Obfuscated-Access-Contest.html?cid=238&anchorAnswerId=22083485#a22083485

Just when you thought it was neat to do it in a couple lines of code + 2 lines for function declarations, how it can be done it 1 alone.

There is also a sample application that illustrates the procedure at the first link....
https://www.experts-exchange.com/questions/23579815/For-Experts-Obfuscated-Access-Contest.html?cid=238&anchorAnswerId=2208510#a2208510

Function ListBoxIn(lst As Object, lngCol As Long, strDelim1 As String, strDelim2 As String, lngItem As Long): On Error Resume Next:  ListBoxIn = ListBoxIn(lst, lngCol, strDelim1, strDelim2, (lngItem - 1)) & ListBoxIn & IIf(lngItem - 1, strDelim1, "") & strDelim2 & lst.Column(lngCol, lst.ItemsSelected(lngItem - 1)) & strDelim2: End Function
 
Function ListBoxIn(R, i, c, k, ²): On Error Resume Next: ListBoxIn = ListBoxIn(R, i, c, k, (² - 1)) & IIf(² - 1, c, "") & k & R.Column(i, R.ItemsSelected(² - 1)) & k: End Function

Select allOpen in new window

Looks like my first link was missing a number.  Here it is again.
https://www.experts-exchange.com/questions/23579815/For-Experts-Obfuscated-Access-Contest.html?cid=238&anchorAnswerId=22082510#a22082510

Special thanks to harfang for an excellent answer to the question!!!  Equally appreciate MX's tenacious efforts to find the solution!!!   Great Job Everyone!!!