Hi Guys,
I feel this should be straightforward, but I'm struggling.
I have a listbox that displays the results of a filter/query - including the PK.
I need to show just those same filtered results in another listbox, where I can display the data in a different format- that is it is displayed with spaces etc between the concatenated values.
Thankyou!
R.
I've solved the same problem by adding a searchfield in the table without the spaces and other sprecial characters using a function like: -
Function fncCompact(strOms As String) As String
-
-
' Filter for 0-9 A-Z en a-z to pass to the result
-
' All other characters are "dropped"
-
-
Dim intI As Integer
-
-
fncCompact = ""
-
-
For intI = 1 To Len(strOms)
-
If Asc(Mid(strOms, intI, 1)) >= 48 And Asc(Mid(strOms, intI, 1)) <= 57 Or _
-
Asc(Mid(strOms, intI, 1)) >= 65 And Asc(Mid(strOms, intI, 1)) <= 90 Or _
-
Asc(Mid(strOms, intI, 1)) >= 97 And Asc(Mid(strOms, intI, 1)) <= 122 Then
-
fncCompact = fncCompact & Mid(strOms, intI, 1)
-
End If
-
Next
-
-
End Function
-
So the search is performed on this field, but the user will seen the "full" field with spaces.
Getting the idea ?
Nic;o)
(Nb the code is marked by a start tag being a [ and a ] with "code" in between. The end tag holds "/code" between the brackets)
7 2205
I don't know if there's a "straightforward" way to do this, but I would write a code procedure that would:
1. Iterate through the contents of the first text box, while
2. Adding each line, formatted the way you want it, to an unbound second text box, using the .AddItem method.
Although, depending on what you plan to do with the second list box, this may not be the easiest to work with.
When your filter is build by using a form and the popup filter, the results will be stored in the Me.Filter property of the form.
This can be used to construct the listbox query by adding " WHERE " & Me.filter to the original SELECT.
Would look like: -
Me.listbox.rowsource = "select * from tblX WHERE " & Me.Filter
-
Me.listbox.requiry
-
Nic;o)
Thanks for your help - haven't needed any for some time, but this Forum has been a godsend in the past.
Going to try your idea (Nic;o) right now...get back to you...
R.
Hello again.
I'm sorry, but the info I gave you to work on was woefully inadequate and even incorrect - I thought I could work around any differences. But I do have an idea that I'm sure will improve the design, if you can just help me with a bit of SQL (etc).
Okay, I have a listbox that updates its columns once a textbox's contents change (using recordsetclone.findfirst in the listboxes After Update event). The problem is that the results are shown with no spacing (so the user can type in search parameters without worrying about spaces - they'll be diff each time).
I was using a second listbox, but I think a subform might be better, to show the same results, but with spaces put back in. The subform then should show the records of the listbox contents, based upon Coulmn 0, the PK - and update on each After Update of the listbox. I have written out dozens of SQL lines, but I can't seem to get it right - here was my last version placed in the After Update of my listbox ('quickSearch);
<DoCmd.OpenForm "frm_subformMain", , , "[tblproducts.productid]= " & " '" & Me.lstquicksearch.Column(0) & "'">
Or, can I populate the subform based on a query using the listboxes PK column as criteria?
PS: How do you guys use tags to show code snippits the way you do?! Or even change the font?
R.
I've solved the same problem by adding a searchfield in the table without the spaces and other sprecial characters using a function like: -
Function fncCompact(strOms As String) As String
-
-
' Filter for 0-9 A-Z en a-z to pass to the result
-
' All other characters are "dropped"
-
-
Dim intI As Integer
-
-
fncCompact = ""
-
-
For intI = 1 To Len(strOms)
-
If Asc(Mid(strOms, intI, 1)) >= 48 And Asc(Mid(strOms, intI, 1)) <= 57 Or _
-
Asc(Mid(strOms, intI, 1)) >= 65 And Asc(Mid(strOms, intI, 1)) <= 90 Or _
-
Asc(Mid(strOms, intI, 1)) >= 97 And Asc(Mid(strOms, intI, 1)) <= 122 Then
-
fncCompact = fncCompact & Mid(strOms, intI, 1)
-
End If
-
Next
-
-
End Function
-
So the search is performed on this field, but the user will seen the "full" field with spaces.
Getting the idea ?
Nic;o)
(Nb the code is marked by a start tag being a [ and a ] with "code" in between. The end tag holds "/code" between the brackets)
Ha Ha...I recently DELETED a similar field (called 'handle') in my main products table on which I performed my searches, but since it was a value made up of other fields I figured it was bad form and tried to find a better way.
Your method is much more involved than mine however, and I'm gonna dig into it now.
Always a pleasure to see your thoughts Nic;o!
Thanks again,
R
Nice to know my thoughts are valued :-)
Success with your application !
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kyralessa |
last post by:
In Access 2000, I have a base form with a ListBox of conference
registrants. In the form's declarations section I include
Dim f as Form
To add a registrant I'm doing this:
Set f = New...
|
by: BK |
last post by:
Hi,
I have a problem. I need to auto populate fields based on a value
entered in combo box. Initially, I put all the required fields in the
combo box, and hide it (set to 0",0",0", ...), then...
|
by: Lyn |
last post by:
Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.
The parent form (frmMainForm) displays the...
|
by: ROO |
last post by:
Hi Everyone,
I have a database table that have 4 field( C1, C2,
M1, M2) on my form i have two combo box ComboC and ComboM
C1 C2 M1
M2
1 ...
|
by: gretchen |
last post by:
I'm a fairly new access user so my question may be simple but I still need help! I have a form and a subform. I want to run an append query and populate the subform based on values in the form. I...
|
by: jebtrillion |
last post by:
I want to fill a listbox that is on another form. I'm not sure how to declare it so the main form can see it. Help
|
by: jxt1303 |
last post by:
Hello,
I need to populate a listbox, based on the result from a parameratized query, which gets its parameter from the ActiveX Calendar control.
So first, I don't know how to execute a...
|
by: =?Utf-8?B?Q2hyaXM=?= |
last post by:
This actually stemmed from anther post I created.
But, I have two combo boxes. ComboBox1 is populated by a list of Systems
(DataSet from Oracle Query).
Now, I want to take Combobox2, and when...
|
by: Bigdaddrock |
last post by:
I have tried using the SetValue Macro to assign the value of a calculated control to another BOUND Control on the same form but have not been successful. I followed the exact format shown in MS...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
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,...
| |