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.
7 2211 topher23 234
Recognized Expert New Member
I don't know if there's a "straightforwar d" 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.
nico5038 3,080
Recognized Expert Specialist
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.OpenFo rm "frm_subformMai n", , , "[tblproducts.pro ductid]= " & " '" & Me.lstquicksear ch.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.
nico5038 3,080
Recognized Expert Specialist
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
nico5038 3,080
Recognized Expert Specialist
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 Form_frmSingleRegistrant
f.Caption = "New Registrant"
f.cmdSave.Caption = "&Save New Registrant"
|
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 use code:
Name=cbBox.column(3) to get the value.
It works fine, but I have problem with performance. The source of the
combo box is a table from...
|
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 data in each record, which
includes the ID of the father and the mother (who also have records in the
table). One record per form.
I have a Tab...
|
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 Sales 1
Monthly Terms
1 Sales 2
Annual Terms
|
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 have a project number and an alpha number, I want to populate the subform (which is obviously connected to a table) based on the value that is in the...
| |
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 parameratized query using VBA. Then also, the Calendar Control doesn't have a click event, so I don't know how to program it to requery everytime they click 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 the data is changed on Combox1, I
want to populate data depending on the selection in Combox1.
So, ComboBox1 lists "OracleDB123". I want...
|
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 Access help instructions.
The amount appears properly in the unbound text box (derived from data in a subform within the same form), however I cannot...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |