I have my field names stored in a 2D array:(arrControls)
I want to loop through my list box controls and pull the selections for each.
arrControls(0,1) = [lst_Status]
arrControls(1,1) = "StatusID"
I need to pass this to a function to read the selections:
fItemSel("StatusID", [lst_Status])
Which would return (if the user selected id 2 and 3):
"StatusID In(2,3)"
When I don't put "" around the field name: [lst_Status]
It tries to pull the selection instead of the field name.
When I put quotes around it: "[lst_Status]"
It can't pass the text string to the function reading the list field selection (which is expecting a control or variant)
I have tried absolutely everything I can think of and don't know how to get past this.
How can you set a control = contents of an array field?
12 3164 ADezii 8,834
Recognized Expert Expert
How about posting all the relevant Code, so that we can see exactly what is going on?
Sorry, I was hoping that would make sense, so I only posted the concept that I'm stuck on. I'll put together a longer example. Thanks
OK, here is more detail. I'm trying to loop through all my list boxes (using a variable name that would pull from an array) and get a statment (strListBoxSelection) of all the selections. I'm stuck on getting the field name stored in the array to pass to the fItemSel function.
Thank you! -
Option Compare Database
-
-
Dim arrControls(0 To 1, 0 To 4)
-
Dim intColumn As Integer
-
Dim strListBoxSelection As String
-
-
Function PullListBoxSelections()
-
-
'0-ControlName
-
arrControls(0, 0) = [lst_Division]
-
arrControls(0, 1) = [lst_SubDivision]
-
arrControls(0, 2) = [lst_Department]
-
arrControls(0, 3) = [lst_Category]
-
arrControls(0, 4) = [lst_Item]
-
-
'1-FieldName
-
arrControls(1, 0) = "DivisionID"
-
arrControls(1, 1) = "SubDivisionID"
-
arrControls(1, 2) = "DepartmentID"
-
arrControls(1, 3) = "Category"
-
arrControls(1, 4) = "Item"
-
-
For intColumn = 0 To 4
-
strListBoxSelection = ""
-
PullListBoxSelections = ""
-
-
strListBoxSelection = Application.Run("fItemSel", arrControls(1, intColumn), arrControls(0, intColumn))
-
'strListBoxSelection = Application.Run("fItemSel", "DivisionID", [lst_Division])
-
-
If strListBoxSelection <> "" Then
-
If PullListBoxSelections = "" Then
-
PullListBoxSelections = "HAVING " & PullListBoxSelections
-
Else
-
PullListBoxSelections = PullListBoxSelections & " AND " & strListBoxSelection
-
End If
-
End If
-
Next
-
-
End Function
-
-
Public Function fItemSel(FieldName As String, ctrl As Control) As String
-
strSELECT = ""
-
For Each varItem In ctrl.ItemsSelected
-
If strSELECT = "" Then
-
strSELECT = ctrl.ItemData(varItem)
-
-
Else
-
strSELECT = strSELECT & "," & ctrl.ItemData(varItem)
-
-
End If
-
Next varItem
-
-
If strSELECT = "" Then
-
fItemSelAnd = ""
-
Else
-
fItemSelAnd = FieldName & " In(" & strSELECT & ")"
-
End If
-
End Function
ADezii 8,834
Recognized Expert Expert
Here is a little food for thought assuming Status_ID is the Bound Column for lstStatus: - Function Definition:
- Function fItemSel(strField As String, lst As ListBox) As Variant
-
Dim bytColCtr As Byte
-
Dim varItem As Variant
-
Dim strBuild As String
-
-
'Is there at least 1 Item Selected for the given Listbox? If
-
'not, then return a Null Value, then Exit
-
If lst.ItemsSelected.Count = 0 Then
-
fItemSel = Null
-
Exit Function
-
End If
-
-
'Assuming strField is the 'Bound' Column
-
For Each varItm In lst.ItemsSelected
-
strBuild = strBuild & lst.ItemData(varItm) & ","
-
Next varItm
-
-
fItemSel = """" & strField & " In(" & Left$(strBuild, Len(strBuild) - 1) & ")" & """"
-
End Function
- Sample Call to Function:
- Dim varRetVal As Variant
-
-
varRetVal = fItemSel("Status_ID", Me![lst_Status])
-
-
Debug.Print varRetVal
- Results/Output (with 6 Selections):
- "Status_ID In(10,9,8,7,6,5)"
- Simply feed to the Function each Bound Field Name as well as the List Box.
That's my back up plan, but I'm still interested in trying to pass that field name as a variable. In my super simple example ([lst_Status]) I only gave one list box, in my next example, I gave 5 list boxes, in the real database, I actually am shooting for 7 list boxes (and 4 combo boxes). Some call for IDs, some dates, some text fields (I only included the function code for the IDs but I have the others).
I'm wondering about storing in my array the control name, field name, select statement, group by statement, and order by statment.
When the user selects from a list box, it would pull the "where" and "having" statments from the previous list boxes, and update the rowsource on the future list boxes. So after the first selection, I would loop through the next 7-10 controls and update the rowsource. On the 5th list box (for example), I would pull the "where" and "having" statements from the first 5 and update the rowsource on the next 5. It's probably not efficient to pull the 1st list boxes "where" statement, after the 1st-5th etc additional list box selections, but I'm not sure where to store it otherwise.
So I'm stuck on passing my form field name stored in a array to my listbox reading function. I was wondering if there is something super easy that I'm missing. Like a way to change the data type or something?
Found it: - Set Ctrl = Me.Controls(arrControls(0, intColumn))
Me.Controls was the part I was missing. Brutal. - Set Ctrl = Me.Controls(arrControls(0, intColumn))
-
strListBoxSelection = Application.Run("fItemSel", arrControls(1, intColumn), Ctrl)
ADezii 8,834
Recognized Expert Expert
How about storing the Control/Field Combinations in Pairings within a Comma Delimited List, and eliminating the 2-Dimensional Array? - Dim strDelim As String
-
Dim varTest As Variant
-
Dim intCtr As Integer
-
-
strDelim = "[lst_Division],DivisionID,[lst_SubDivision],SubDivisionID,[lst_Department]," & _
-
"DepartmentID,[lst_Category],Category,[lst_Item],Item"
-
-
varTest = Split(strDelim, ",")
-
-
For intCtr = LBound(varTest) To UBound(varTest) Step 2
-
Debug.Print varTest(intCtr), varTest(intCtr + 1)
-
Next
OUTPUT: - [lst_Division] DivisionID
-
[lst_SubDivision] SubDivisionID
-
[lst_Department] DepartmentID
-
[lst_Category] Category
-
[lst_Item] Item
I did something very similar using the split function, but mine was 2D still because I have 70 strings. I combined the split function with a loop function to fill each spot in the array.
I used an array because I wanted to store not only lst_Division, and DivisionID, but also the SELECT, GROUP, and ORDER statements (and a couple other things) for each. About 7 columns total for 10 controls.
The other thing is that I wanted to loop through the controls using an id. (Which your example above would do in a 1D array, but mine would have 70 columns which is why I wouldn't do that, though it's a good idea and I'll use it in other places). Instead of having to list 10 functions after each "after update" of 10 controls, I ultimately wanted one procedure where I passed the column number of the control to read (start to end) and the column numbers to write (start to end). I did this: - Call UpdateControls(intReadStart, intReadEnd, intWriteStart, intWriteEnd)
So on the 4th (for example) control it would be: - Call UpdateControls(0,4,5,10)
This reads the selected criteria for controls 0-4 in my array (by passing the variable field name to my list box reading function), pulls the SQL statements for fields 5-10, inserts the WHERE statement from 0-4 and updates the rowsource. It's sweet.
My only complaint now (once I had the Me.Controls breakthrough) is that I like it so much I'm likely to use this type of form/functions on other projects so I would still like to perfect it. I have the criteria re-reading all the control selections with every selection from a new control which seems inefficient since it ultimately it should just save one ongoing "WHERE" string and add to it or remove from it for every new choice "after update". My original intention was "after update" to update the array with the selected criteria and store it there, but I'm embarressed to say I don't know how long those variables would be saved?
ADezii 8,834
Recognized Expert Expert
I don't know how long those variables would be saved?
That depends entirely on the Scope, namely the Context within which they are Declared: - Since it is Declared in a Local Procedure, it would have a very narrow Scope, namely varSomeVariable would exist only within the AfterUpdate() Event of SomeControl:
- Private Sub SomeControl_AfterUpdate()
-
Dim varSomeVariable as Variant
-
End Sub
- Since it is Declared Privately within the General Declarstion of a Form's Module, varSomeVariable would exist for the lifetime of the Form:
-
'General Declarations Section of a Form
-
Private varSomeVariable as Variant
- Since it is Declared Publically within the General Declarstion of a Standard Code Module, varSomeVariable would exist for the lifetime of Application:
-
'General Declarations Section of a Standard Code Module
-
Public varSomeVariable as Variant
- Hope this sheds some light on the Subject.
Thank you so much for the response, I think I sort of knew your #1 and #2, so I declared it in the general form rather than the procedure, but I didn't know #3 and in general wasn't confident in #2 still being there while other procedures were run (which as you explain, is the whole point). Thanks a ton, I will try adding a column to the array to store the controls selections (after update), then cycle through them to string together the WHERE statment and not have to keep reading the selections for the same conrols over and over.
In your expert opinion, do you think this sounds like a good idea to make the form work this way? I not only want it to work (which it does right now), but going forward, I would like to keep increasing the efficiency and professionalism of my code.
ADezii 8,834
Recognized Expert Expert
I not only want it to work (which it does right now), but going forward, I would like to keep increasing the efficiency and professionalism of my code.
Sounds like a desirable and professional approach to me, one which I'm sure no one here will argue with. Good Luck.
Thanks, and thanks again for the explanation/assurances sbout declaring my variables. That knocked out the last remaining hesitations I had. I just upated my form to read the array "on load", then it's stored going forward (before I was reading it too many times which didn't seem right), I also updated the "after update" for each control to write to a field in my array for that associated control (then loop through them to piece together the "where" statment, instead of reading each controls selection repeatedly on the click of every new control). Now I'm just looping though the 10 fields to pull the "where" statment, instead of looping through every line of every list box. Much better. Much faster! (I also added a sort on every field of every listbox). Thanks a ton! On to the next...
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: sleon |
last post by:
I have two drop down list boxes, category, and subcategory. The
subcategory list does not get populated until a selection has been made
from the "category" list.
When a selection is made from...
|
by: Hohn Upshew |
last post by:
I want to build a function having the name of the list box
in brackets like Check(ListBoxInvoices).May i have some help as to how
to do it ?
In my form i have several list boxes.I have command...
|
by: Galina |
last post by:
Hello
I have 6 dependent list boxes on my ASP page:
Faculty;
Lecturer;
Course;
Course occurrence;
Group;
Week commencing date.
When faculty is selected, lists of lecturers and...
|
by: B-Dog |
last post by:
I'm capturing the checked radio button to XML file using the name of the
radio button. I want to read my xml file to find which button was checked
on close and the check the appropriate button...
|
by: Col |
last post by:
Hi - I've never worked with list boxes before. Here's what I'd like to
do - have a list box (or some other control) that allows multiple
selection and stores all the values in one field (can be...
| |
by: Christina |
last post by:
Hi,
I've been looking at some code for dependent list boxes to adapt to a State
and City list. There will only be 2 states for the first list box, and 3
cities in the second list box. When the...
|
by: Rabbit |
last post by:
Cascading Combo/List Boxes
This tutorial is to guide you in the creation of Cascading combo/list boxes. That is when you have multiple combo/list boxes where the selection of an option in one...
|
by: ZaphodBBB |
last post by:
Hi (Windows XP Pro, MS Access 2003)
I have a form that has quite a number of list boxes on it. The list boxes only have 3 or 4 selections possible...Pass, Fail, Average, Not...
|
by: paragpdoke |
last post by:
Hello Everyone.
I'm new to XSL and couldn't get something to work.
Sample XML excerpt:
<root>
<ObjectTemplate>
<NodeType1></NodeType1>
<NodeType2></NodeType2>
</ObjectTemplate>
<Object>
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |