Filter a query from a listbox on a form (1 Viewer)

David Ball

Registered User.
Local time
Today, 20:53
Joined
Aug 9, 2010
Messages
230
Hi,

I have a query, qryCC_Data, that I can open from a command button on a form using the code below in the command button’s on-click event.
Code:
Private Sub Command7_Click()

DoCmd.OpenQuery "qryCC_Data", , acReadOnly
End Sub
Code:

I also have a listbox on the form, List5, and would like to be able to select a value from the listbox and have it filter the query when I run it.
The name of the field in the qryCC_Data that corresponds to the value I would select from the listbox is Zone.
I have tried modifying my code to do this filter but can't get it working.
What does my code need to be?

Thanks very much
Dave
 

June7

AWF VIP
Local time
Today, 02:23
Joined
Mar 9, 2014
Messages
5,425
Then you probably need to use parameter in query that references the listbox. Otherwise, build a report and apply filter to report when opening.

Why do you want to open query? Are you the only user of this db?
 

David Ball

Registered User.
Local time
Today, 20:53
Joined
Aug 9, 2010
Messages
230
Yeah, I'm the only user. I crunch data in Access and then copy the result from my query and use it in other applications.

I got the query to filter but I have a calculated field in the query (two of the fields concatenated to create a new field) and everytime the query runs it deletes the calculated field. I have no idea why?
 

June7

AWF VIP
Local time
Today, 02:23
Joined
Mar 9, 2014
Messages
5,425
That makes no sense. The calculation is in table or in query?

If you want to provide db for analysis, follow instructions at bottom of my post.
 

David Ball

Registered User.
Local time
Today, 20:53
Joined
Aug 9, 2010
Messages
230
June7,

I have attached a stripped down version of the database.
If you open query, qryCC_Data_With_Expression, you will see the field (UniqueNo) I have created by concatenating two existing fields together. I need to do this to get a unique number that corresponds to a number in another application. (Keep in mind this is a very small sample of all the data and not many “ITR Linked Section Key” values are unique numbers in the full list).
If I create the same field in qryCC_Data, which is filtered by the listbox selection made on frmCompare, the field UniquNo disappears everytime I run the query.
Can I set this up so that I can have the field UniqueNo in qryCC_Data without it being deleted/removed everytime I run the query?
Thanks very much
Dave
 

Attachments

  • CCplus1 - Copy.zip
    31.4 KB · Views: 45

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:23
Joined
May 21, 2018
Messages
8,463
Change the following code
Code:
'strSQL = "SELECT * FROM tblCC_Data"
     strSQL = "SELECT Zone, [ITR CCNo], [ITR Linked Section Key], [ITR CCNo] & [ITR Linked Section Key] AS UniqueNo FROM tblCC_Data "
 

Users who are viewing this thread

Top Bottom