?
Solved

Open a query or report and fiilter the same based on a value from a list

Posted on 2010-01-06
49
Medium Priority
?
548 Views
Last Modified: 2013-11-28
Currently queries and reports are opened with [Enter Specialty] criteria in the query. The individual then types in their information to filter the query/report to display contents based on their input (1 of 9 options).

Is it possible to autofilter the information based on picking one option from the list of 9 when opening the query or report?
0
Comment
Question by:andymacf
  • 25
  • 24
49 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26192632
Are you asking how to replace the inline query criteria entry box with a form listbox?

>>Is it possible to autofilter the information based on picking one option from the list of 9 when opening the query or report?

Sure, can we see the SQL for the Query?

J

0
 
LVL 7

Author Comment

by:andymacf
ID: 26194286
Please see attached SQL below.  The field that is linked to another table is 'spName'.  I created the query [1 Examiner Select Query] to pull together various elements from different tables and to make it easier to populate a lot of queries and reports based on the information therein. I would like to remove the criteria [Enter specialty] and replace this with a list so that on selecting a particular specialty it would filter the list accordingly.  I hope this makes sense.

Andy
SELECT DISTINCTROW [1 Examiner Select Query].spName, [1 Examiner Select Query].pTitle, [1 Examiner Select Query].pInitial, [1 Examiner Select Query].pKnownAs, [1 Examiner Select Query].pLastName, [1 Examiner Select Query].pCurrentPost, [1 Examiner Select Query].preferredAddress, [1 Examiner Select Query].cdAddress1, [1 Examiner Select Query].cdAddress2, [1 Examiner Select Query].cdAddress3, [1 Examiner Select Query].cdAddress4, [1 Examiner Select Query].cdCity, [1 Examiner Select Query].cdRegion, [1 Examiner Select Query].cdPostCode, [1 Examiner Select Query].WrittenPaperCommitteeMember, [1 Examiner Select Query].ysnBoard
FROM [1 Examiner Select Query]
WHERE ((([1 Examiner Select Query].spName)=[enter specialty]) AND (([1 Examiner Select Query].preferredAddress)=1) AND (([1 Examiner Select Query].ysnBoard)=1))
ORDER BY [1 Examiner Select Query].pLastName;

Open in new window

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26194557
The only way to replace it with a "List" is to create an interface (Form) where your user may select FROM a list, then the value selected is applied to the Query.

Is that what you mean?
J
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 7

Author Comment

by:andymacf
ID: 26194614
Yes, that would be feasible.  Where do i need to put the controls i.e. how do i link to the form when they click to open their query or report and how do i then link the value they choose to filter the query or report?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26194887
How do they open their reports today?

Queries can be opened from the database window, If you want to give them the ability to use a criteria form, then you will have to teach them to open queries using this new form.

There are a couple of ways to do this. You can use the selection to change the Query SQL directly, or you can refer to the value on the form from the Query.

How's your coding?

J

0
 
LVL 7

Author Comment

by:andymacf
ID: 26194947
To open a report they have a switchboard with option groups and a button, they select an option then click the button to open the report or a different button for a query, they are then asked to 'Enter Specialty'. Some of the specialties are long, 'Trauma & Orthopaedic Surgery' for example and this is why I would like to make it automatic.

My coding is reasonable but not brilliant.  I am happy to try anything if I can get a working solution
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26195874
ok, So somewhere between the time they open the app, and hit a button to query or report, you'll need to interject your Filter form.

There are a couple of ways to do what you're asking...and if you want to read up here on EE feel free. One method is to use the Open Report command itself to pass criteria to the report when it opens.

1) Use the Open Report Method
DoCmd.OpenReport stDocName, acPreview,FilterName, WhereCondition,WindowMode,OpeningArguements

2) Set the actual RecordSource up before the report opens
dim qdf as querydef
dim sSQL as string
set qdf = currentdb.querydefs("YourQueryName")
sSQL = "Select * from Table1 Where ID = " & forms!yourform.yourlistitem
qdf.SQL = sSQL
qdf.close
set qdf = Nothing

3) Query Grid
Select * from Table1 Where ID = " & forms!yourform.yourlistitem & ";"

So as you can see, there are options, but you're going to have to at least modify the Button clicks for these reports to include opening your new form up and forcing the user to make a selection in your list.

Don't you love choices?
J



0
 
LVL 7

Author Comment

by:andymacf
ID: 26202171
Hi J,

Thank you for your comments so far.  I have attached the code i have used in my option groups for one of my buttons which, in this instance, runs my queries.  Some, but not all, of these options are asked the Specialty question when they open their respective query.  Can you advise if I can add the relevant code in where the Case is statement exists thus only opening the form where required and what format would this need to take?
Private Sub Command469_Click()
On Error GoTo Err_Command469_Click

Select Case [ExamQueries]

    Case Is = 1
        DoCmd.OpenQuery "Examiners - Current exam list by specialty", acNormal, acEdit
        
    Case Is = 2
        DoCmd.OpenQuery "Examiners - Dietary requirements - by exam code", acNormal, acEdit
        
    Case Is = 3
        DoCmd.OpenQuery "Examiners - email address by specialty", acNormal, acEdit
        
    Case Is = 4
        DoCmd.OpenQuery "Examiners - envelope", acNormal, acEdit
        
    Case Is = 5
        DoCmd.OpenQuery "Examiners - Equal Opps by Specialty - check not rec'd", acNormal, acEdit
        
    Case Is = 6
        DoCmd.OpenQuery "Examiners - Equal Opps by Specialty - 4 monitoring receipt", acNormal, acEdit
        
    Case Is = 7
        DoCmd.OpenQuery "Examiners - exam history", acNormal, acEdit
        
    Case Is = 8
        DoCmd.OpenQuery "Examiners - FRCS by Specialty", acNormal, acEdit
        
    Case Is = 9
        DoCmd.OpenQuery "Examiners - Invite to FUTURE exam for reminder ltrs - R 13", acNormal, acEdit
        
    Case Is = 10
        DoCmd.OpenQuery "Examiner - Merge letter (exam code)", acNormal, acEdit
        
    Case Is = 11
        DoCmd.OpenQuery "Examiners - OSCE number", acNormal, acEdit
        
    Case Is = 12
        DoCmd.OpenQuery "Examiners - ", acNormal, acEdit
        
    Case Is = 13
        DoCmd.OpenQuery "Examiners - single label - R 20", acNormal, acEdit
        
    Case Is = 14
        DoCmd.OpenQuery "Examiners - Sub-specialty", acNormal, acEdit
    
    Case Is = 15
        DoCmd.OpenQuery "Panel of Examiners - R 23 & 29", acNormal, acEdit
        
' If you don't choose a default value, this generates a message box if the user clicks a button without making a selection
    Case Else
        MsgBox "Select Query", vbExclamation, "Queries"
        
End Select

Exit_Command469_Click:
    Exit Sub

Err_Command469_Click:
    MsgBox Err.Description
    Resume Exit_Command469_Click
    
End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26205658
Sure, and I think the Case layout is very clean for what you're trying to do.

If you create a small form with either a combo box or a list box with your criteria items in it and a button. We can take it a long ways.

so in your code you would do something like so
Set a Form Level Variable
Option Explicit
Dim lFilterItem as long
Dim qdf as Querydef
Dim sSQL as String

Select Case [ExamQueries]
 
    Case Is = 1
        lFilterItem = 0
        DoCmd.OpenForm "YourFilterForm", , , , , acDialog
        'When the Filter Form is opened, you present the user with your listbox and an OK button and a Cancel button.
        'The user will select an item on the list which you will set lFilterItem's value to. The User hits OK at which time you close the form.
        'If the user selects Cancel, then simply close the Filter Form.
         'Now that the variable is populated (or not), you can use it dynamically in your Query this way.

        If nz(lFilterItem,0) <> 0 Then
             Set qdf = CurrentDB.QueryDefs("Examiners - Current exam list by specialty")
             sSQL = "Select blah blah blah from blah "
             sSQL = sSQL & " Where x = " & lFilterItem
             qdf.SQL = sSQL
             qdf.Close
             Set qdf = Nothing
             DoCmd.OpenQuery "Examiners - Current exam list by specialty", acNormal, acEdit
         Else
             Msgbox"You didn't select anything, so I'm not going to open this query"
         End IF


   Case Is = 2

etc..

J
     
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26205710
Just to emphasize, the variable that you create called  lFilterItem  needs to be a Public variable, and not a form level variable as I incorrectly stated. The Variable has to be available for both your Filter form and your Option Group form. So in a code module, you would want to set it as public

Public lFilterItem as Long

J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26209092
Dear Jeff,

It is all becoming clear now, however how do i store the value i select in my list. When i design my list box should i 'save the value for later use' and, if so, do i allocate a value to each of the options in my list thus providing a variable for 'IFilterItem'.

Andy
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26211439
>>>do i allocate a value to each of the options in my list thus providing a variable for 'IFilterItem'.

You don't really need to store the value, that's what the Variable is for. So use the After Update event of the listbox to assign the value to the variable.

You can create additional variables if you need them depending on what you need to store for the QueryDef value, for example if you need to put a String value into the variable, or a date.

Its a very flexible design and you can configure it as needed.
J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26214133
Yes, I understand but how do i allocate the value to the list items and what do i put in the AfterUpdate event?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26222840
If it is a fixed list, you can simply type them into the RowSource of the listbox and use Value List as the list type. If you have them in a table somewhere, you can use the wizard to create the listbox pointing to that table.

In the after update event all you have to do is assign the lFilterItem  variable.

Example:

lFilterItem  = me.mylistbox

You're almost there!
J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26273935
Sorry, but I just cannot seem to get past the goalpost. I have attached what I have done so far, hopefully I am heading in the right direction. I have populated the code you sent with the relevant form names etc and also attached a screenshot of the form with properties showing. My problem is I don't understand the 'me.mylistbox' bit. Thanks for your patience.

Set a Form Level Variable
Option Explicit
Public lFilterItem as long
Dim qdf as Querydef
Dim sSQL as String

Select Case [ExamQueries]
 
    Case Is = 1
        lFilterItem = 0
        DoCmd.OpenForm "Specialty Choose", , , , , acDialog
        'When the Filter Form is opened, you present the user with your listbox and an OK button and a Cancel button.
        'The user will select an item on the list which you will set lFilterItem's value to. The User hits OK at which time you close the form.
        'If the user selects Cancel, then simply close the Filter Form.
         'Now that the variable is populated (or not), you can use it dynamically in your Query this way.

        If nz(lFilterItem,0) <> 0 Then
             Set qdf = CurrentDB.QueryDefs("Examiners - Current exam list by specialty")
             sSQL = "Select [spName] from Forms![Specialty Choose] "
             sSQL = sSQL & " Where x = " & lFilterItem
             qdf.SQL = sSQL
             qdf.Close
             Set qdf = Nothing
             DoCmd.OpenQuery "Examiners - Current exam list by specialty", acNormal, acEdit
         Else
             Msgbox"You didn't select anything, so I'm not going to open this query"
         End IF

Code-for-Spec-2.doc
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26288541
Have you created a small form where you put the values in some kind of a list or combo box for your users to choose? ie. This would replace the need to type into the Input boxes you used to have in your query.

If you have, then I am refering to that list when I say listbox.

You don't need anything in the After Update event of your listbox.

You only need code on your OK and Cancel buttons.

OK:
lFilterItem  = me.mylistbox
me.close  'As in close the pop-up form

Cancel:
me.close  'As in close the pop-up form


This line in the code above handles the case when you hit cancel and didn't set the Filter Item to anything
>>>If nz(lFilterItem,0) <> 0 Then


>>> sSQL = "Select [spName] from Forms![Specialty Choose] "
             sSQL = sSQL & " Where x = " & lFilterItem

The SQL should look like this

sSQL = "SELECT DISTINCTROW [1 Examiner Select Query].spName, [1 Examiner Select Query].pTitle, [1 Examiner Select Query].pInitial, "
sSQL = sSQL & " [1 Examiner Select Query].pKnownAs, [1 Examiner Select Query].pLastName, [1 Examiner Select Query].pCurrentPost, "
sSQL = sSQL & " [1 Examiner Select Query].preferredAddress, [1 Examiner Select Query].cdAddress1, [1 Examiner Select Query].cdAddress2, "
sSQL = sSQL & " [1 Examiner Select Query].cdAddress3, [1 Examiner Select Query].cdAddress4, [1 Examiner Select Query].cdCity, "
sSQL = sSQL & " [1 Examiner Select Query].cdRegion, [1 Examiner Select Query].cdPostCode, [1 Examiner Select Query].WrittenPaperCommitteeMember, "
sSQL = sSQL & " [1 Examiner Select Query].ysnBoard "
sSQL = sSQL & " FROM [1 Examiner Select Query] "
sSQL = sSQL & " WHERE ((([1 Examiner Select Query].spName)= '" & lFilterItem & "') "
sSQL = sSQL & " AND (([1 Examiner Select Query].preferredAddress)=1) "
sSQL = sSQL & " AND (([1 Examiner Select Query].ysnBoard)=1)) "
sSQL = sSQL & " ORDER BY [1 Examiner Select Query].pLastName"

Hope that helps.
J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26293843
Please see the attached document containing the code for my two buttons.  Can you please assist with the setting up of these buttons?  Also, how does the code know which option in the list someone has selected?

Regards
Screenshot-button-code.doc
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26294715
this is all the code you need in those buttons

OK:
lFilterItem  = me.mylistbox
docmd.close  'As in close the pop-up form

Cancel:
docmd.close  'As in close the pop-up form


Assumed Steps:

1) User selects the query he wants to run from your main menu
2) The pop-up form opens showing the user a listbox
3) the user selects an item in the listbox
4) the user selects OK
5) the pop-up form closes and the query runs

J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26303754
Hi Jeff,

Sorry for delay, been busy with other things.

I have tried to run the code you sent through. It is opening my Specialty list form correctly but no matter what i select i get the error message, "You didn't select anything.....", so there is some form of disconnect between me making my choice and it populating the information required to pass to the query or report.  I just feel that i have not allowed for a selection to be made as it seems that the cursor is remaining at the top of the list on my new form.

    Case Is = 1
        IFilterItem = 0
        DoCmd.OpenForm "Specialty Choose", , , , , acDialog
        'When the Filter Form is opened, you present the user with your listbox and an OK button and a Cancel button.
        'The user will select an item on the list which you will set lFilterItem's value to. The User hits OK at which time you close the form.
        'If the user selects Cancel, then simply close the Filter Form.
         'Now that the variable is populated (or not), you can use it dynamically in your Query this way.

        If Nz(IFilterItem, 0) <> 0 Then
             Set qdf = CurrentDb.QueryDefs("Examiners - Current exam list by specialty")
             sSQL = "Select [spName] from Forms![Specialty Choose] "
             sSQL = sSQL & " Where x = " & IFilterItem
             qdf.SQL = sSQL
             qdf.Close
             Set qdf = Nothing
             DoCmd.OpenQuery "Examiners - Current exam list by specialty", acNormal, acEdit
         Else
             MsgBox "You didn't select anything, so I'm not going to open this query"
         End If
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26306499
ok, can you select something in your listbox? how many columns did you put in your listbox and what is the Bound column?

Where did you put the declaration for the lFilterItem variable?

J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26306764
No, i cannot select anything in my listbox, one column.

Where do i out the declaration and what format should it take?

Andrew
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26309663
First things first...you need to recreate your listbox. If you can't select anything in it, then something is wrong.  Is it based on values in a table or did you type them all in?

If you create it with the wizard, when it asks you, tell it to save the value for later.

If you typed them all in, make sure that your Rowsource Type is List and not Query

Where to put the declaration >>> see this ID:26205710

J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26323399
I have re-created the list as requested. Unfortunately, i cannot access the link you suggested for the declaration.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26337784
Scroll up, its in the comments above.

"
Just to emphasize, the variable that you create called  lFilterItem  needs to be a Public variable, and not a form level variable as I incorrectly stated. The Variable has to be available for both your Filter form and your Option Group form. So in a code module, you would want to set it as public

Public lFilterItem as Long
"
0
 
LVL 7

Author Comment

by:andymacf
ID: 26342244
I have added the relevant statements to my code.  However, 'The Variable has to be available for both your Filter form and your Option Group form', where do i need to place the variable in my filter form and option group in order to get it to work
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26370487
Hi,

there are 3 kinds of modules. there are object modules, those are the ones that are behind forms and reports. There are code modules, which house functions and subs that are built to centrally store re-usable code. Then there are class modules, which house various Constructs and class type code.

The place to keep a Public or Global variable is in the Code Module.

When you open your VBA window, Go to View/Project Explorer

When it opens, it may be expanded, however you should notice that the sections that I described above are there.

If there aren't any Code modules there already, then go back to the menu and select Insert/Module

At the top of the new module, you will see the statement "Option Compare Database"

If there isn't a second line in this module that says "Option Explicit", then add it.

Underneath that is where you can begin to add your public variables.

when you save, give it  a name that is NOT a name of any of your subs or functions already in the App. ie. Mod_PublicVariables.

Now, when you set the variable from anywhere in the app, it will store the value to be used anywhere else in your app.

Hope this helps.
J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26374386
Dear J,

Thank you for the clarification of modules.  I have created the module as you suggested (see attached). I have also attached screenshots of the module for my queries and reports and a screenshot of my form filter buttons.  I believe I am just missing a link between these 3 areas adn hope that you can guide to the end of this process.

Thanks
Andymacf
Mod-PublicVariables.jpg
Query-module-linked-to-option-li.jpg
Form-filter-buttons.jpg
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26394599
It looks like your problem is still in the case statement.

You are still referring to Forms![Specialty Chose] and that's what we were avoiding by using the popup form

If what you're selecting in your listbox is the name of a table, then that's what you would put into the public variable.

And since it is NOT a numeric value you want, then you need to change the type from Long to String so you can use it like below


If Nz(IFilterItem, 0) <> 0 Then
             Set qdf = CurrentDb.QueryDefs("Examiners - Current exam list by specialty")
             sSQL = "Select [spName] from [" & IFilterItem & "]"
             qdf.SQL = sSQL
             qdf.Close
             Set qdf = Nothing
             DoCmd.OpenQuery "Examiners - Current exam list by specialty", acNormal, acEdit
         Else
             MsgBox "You didn't select anything, so I'm not going to open this query"
         End If[]
0
 
LVL 7

Author Comment

by:andymacf
ID: 26407014
I am getting confused now as to where key statements should go in relation to the relevant queries etc.  As I mentioned at the beginning my coding is not brilliant.  If I could just recap.  

I have my existing queries with my case statements, the code as displayed in your previous comment should be inserted at the point where i open my query or report. I am happy with this bit

I have created a form which has a list box displaying all entries from the query 'Specialty', thus I get 9 values when i open this form. Also, if I run my original query with the code placed at the relevant point in my case statements, it opens the form. However, I cannot select a value from the 9 displayed values and thus cannot link back to the case statement in my original query.

Then you mentioned that I should create a new module, Mod_public variables, I assume that these are available to the system globally as they are contained within it.

I think I am at the point where my skillset is being stretched to the max and I am floundering to achieve success.  Apologies for this.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26409827
ok,
I need to know now exactly what is in the listbox.  Paste the list here.

I also need to know that you understand what your query does. Based on your confusion, I'm guessing that either I have misunderstood where to plug your Filter in, or what the contents of the filter does to the query.

If I go back up to the top and grab one of your queries....

SELECT DISTINCTROW [1 Examiner Select Query].spName, [1 Examiner Select Query].pTitle, [1 Examiner Select Query].pInitial, [1 Examiner Select Query].pKnownAs, [1 Examiner Select Query].pLastName, [1 Examiner Select Query].pCurrentPost, [1 Examiner Select Query].preferredAddress, [1 Examiner Select Query].cdAddress1, [1 Examiner Select Query].cdAddress2, [1 Examiner Select Query].cdAddress3, [1 Examiner Select Query].cdAddress4, [1 Examiner Select Query].cdCity, [1 Examiner Select Query].cdRegion, [1 Examiner Select Query].cdPostCode, [1 Examiner Select Query].WrittenPaperCommitteeMember, [1 Examiner Select Query].ysnBoard
FROM [1 Examiner Select Query]
WHERE ((([1 Examiner Select Query].spName)=[enter specialty]) AND (([1 Examiner Select Query].preferredAddress)=1) AND (([1 Examiner Select Query].ysnBoard)=1))
ORDER BY [1 Examiner Select Query].pLastName;

The place where you have [Enter Specialty] is what I'm assuming is in your listbox.

You said you built your listbox using the wizard...so I assume you typed the values in.

That being the case, I can only assume its a string of some kind. String being text and not numbers.


Here's what the code would look like in total. Forgive the spacing.

Set qdf = CurrentDb.QueryDefs("Examiners - Current exam list by specialty")
             sSQL = "SELECT DISTINCTROW [1 Examiner Select Query].spName, [1 Examiner Select Query].pTitle, [1 Examiner Select Query].pInitial, [1 Examiner Select Query].pKnownAs, [1 Examiner Select Query].pLastName, [1 Examiner Select Query].pCurrentPost, [1 Examiner Select Query].preferredAddress, [1 Examiner Select Query].cdAddress1, [1 Examiner Select Query].cdAddress2, [1 Examiner Select Query].cdAddress3, [1 Examiner Select Query].cdAddress4, [1 Examiner Select Query].cdCity, [1 Examiner Select Query].cdRegion, [1 Examiner Select Query].cdPostCode, [1 Examiner Select Query].WrittenPaperCommitteeMember, [1 Examiner Select Query].ysnBoard
FROM [1 Examiner Select Query]
WHERE ((([1 Examiner Select Query].spName)= '" & sFilterItem & "' ) AND (([1 Examiner Select Query].preferredAddress)=1) AND (([1 Examiner Select Query].ysnBoard)=1))
ORDER BY [1 Examiner Select Query].pLastName"

             qdf.SQL = sSQL
             qdf.Close
             Set qdf = Nothing
             DoCmd.OpenQuery "Examiners - Current exam list by specialty", acNormal, acEdit

Notice I've changed the FilterItem variable based on the assumption that it is Text. That said, the declaration for the variable and its name should change wherever it is found to sFilterItem  

It should be declared as
Public sFilterItem as String

J

0
 
LVL 7

Author Comment

by:andymacf
ID: 26412464
I think the problem is lying in the design of my popup form as I cannot seem to select any values from it. I created my form and within it I placed a listbox which links to the query called 'Specialty', this information stems from a table in my database called 'dbo-Specialty' and whilst I can see my 9 specialties, should i type the list contents elsewhere, like 'row value' in order to be able to select a value.

Are there other settings in the properties of the form that I need to set so that it recognises the fact it is a popup form.
0
 
LVL 7

Author Comment

by:andymacf
ID: 26417355
Further to my last, I have attached 3 images of the list box form, properties and code for your perusal.

Regards
Andy
List-Box-Code.jpg
List-Box-Props.jpg
List-Box.jpg
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26420039
>>>I think the problem is lying in the design of my popup form as I cannot seem to select any values from it
does this mean that when you click on an entry in the box, it doesn't highlight it?

Did you square away the CASE 1 statement as I described?

Your listbox should NOT have both a Control Source AND a RowSource when you have it designed as a Value List.

Perhaps you should recreate it using the wizard.

J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26426100
I have recreated my listbox and, when given the option, I chose to type the values in and also remember them for later use. My list box is called 'List1'. I attach screenshots as follows:

1. List Box Data & 2. List Box Event
3. Popup form properties (showing the source as 'Specialty' which is my query) & 4. Popup form button code
5. Public variable code
6. Case statement (amended) - Can I just clarify that I do not need to paste the information into the case statement as shown above in ID: 26409827?
7. Attempt to run the code - this gives an 'Invalid use of null' and when I select 'debug', it presents this error.

Again it seems I cannot select an item in the list.  Do we need to create a link between sFilterItem and List1 or have we already done this?
List-Box-Data.jpg
List-Box-Event.jpg
Popup-form-properties.jpg
Popup-button-code.jpg
Public-Variable-code.jpg
Case-statement.jpg
Invalid-use-of-null-error.jpg
0
 
LVL 7

Author Comment

by:andymacf
ID: 26426111
I also forgot to mention that in the form properties of the popup form, AfterUpdate event is set sFilterItem=me.list1.

Regards
Andy
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26428850
With your listbox, Create an On_Click event for it.

In the On_click event do this:

Private Sub List1_Click()
      MsgBox "I selected " & me.list1 & " from my listbox"
End Sub

and see if the value is being evaluated.

If this works, and you see your value, then move the Set Variable statement here
Private Sub List1_Click()
      sFilterItem =  me.list1
End Sub

Then you can change the code on the OK button to just validating that something is in the variable. Like this

if sFilterItem = "" then
MsgBox"Nothing Selected"
exit sub
end if

something like that.
J

0
 
LVL 7

Author Comment

by:andymacf
ID: 26429587
I can confirm that this value is not being evaluated i.e. the MsgBox did not display.  I will have a further look at the list box. Does my popup form have to be linked to the Specialty query or can it just be a form which has a listbox containing the values that originally would have been typed into [Enter Specialty]?
0
 
LVL 7

Author Comment

by:andymacf
ID: 26432172
I have sorted the listbox, it was disabled on the top form to allow edits, that is why I coudl not select anything. The sFilterItem is now populating. Hurrah!!

Do I need to replace everything after

  sSQL = "Select [spName] from [" & sFilterItem & "]"

with

  sSQL = "SELECT DISTINCTROW [1 Examiner Select Query].spName, [1 Examiner Select Query].pTitle, [1 Examiner Select Query].pInitial, [1 Examiner Select Query].pKnownAs, [1 Examiner Select Query].pLastName, [1 Examiner Select Query].pCurrentPost, [1 Examiner Select Query].preferredAddress, [1 Examiner Select Query].cdAddress1, [1 Examiner Select Query].cdAddress2, [1 Examiner Select Query].cdAddress3, [1 Examiner Select Query].cdAddress4, [1 Examiner Select Query].cdCity, [1 Examiner Select Query].cdRegion, [1 Examiner Select Query].cdPostCode, [1 Examiner Select Query].WrittenPaperCommitteeMember, [1 Examiner Select Query].ysnBoard
FROM [1 Examiner Select Query]
WHERE ((([1 Examiner Select Query].spName)= '" & sFilterItem & "' ) AND (([1 Examiner Select Query].preferredAddress)=1) AND (([1 Examiner Select Query].ysnBoard)=1))
ORDER BY [1 Examiner Select Query].pLastName"


or do I replace the SQL for the actual query I am trying to run with this SQL

I also get the attached error message when I now run the case statement
Error-box.jpg
0
 
LVL 7

Author Comment

by:andymacf
ID: 26432438
And when I now run it, it replaces the contents of the query I was trying to run with the attached
Replace-Query.jpg
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26437967
this bit wont work    >>> sSQL = "Select [spName] from [" & sFilterItem & "]"

to test, try this instead

sSQL = "SELECT * FROM [1 Examiner Select Query]
WHERE ((([1 Examiner Select Query].spName)= '" & sFilterItem & "' ) AND (([1 Examiner Select Query].preferredAddress)=1) AND (([1 Examiner Select Query].ysnBoard)=1)) "

Remember, the filter is simply a spName that you don't have to type. So it belongs in the Where Clause
J


0
 
LVL 7

Author Comment

by:andymacf
ID: 26438353
Do I need to place this in my code in the case statement or does it go in the SQL for the actual query I am trying to run?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26454556
In the case statement.  This method is known as dynamic SQL. You're using a variable to complete the SQL that gets put into the Query.

You should be close to resolving this?

Do you understand what it is that we're doing? Do you understand the use of the variable and where it goes in your SQL string?
0
 
LVL 7

Author Comment

by:andymacf
ID: 26455587
I have tried this in my case statement and I get a compile error with the 'WHERE' statement.

I think I am almost clear what we are trying to do.  Since we have got the string to populate, it is certainly clearer.  Your patience is commendable by the way :)

Also do i just delete the [Enter Specialty] in the original query, or do i have to replace it with something else.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26459754
It is probably a Parenthesis issue.

Do you know how to use the Immediate Window to examine your variables?
A little trick is to put a breakpoint in your code right after where your sSQL gets set.
Go to the immediate window and type in
?sSQL
and hit return.
it will return the string inside the variable.
Now you can copy that string, and open a new query in SQL view, and paste it in.
Now you can switch to Design view in the Query Window and Access will show you where any errors in your SQL may be.
Just a neat little trick you might find handy.

To answer your second question ...what do you see in this SQL?

sSQL = "SELECT DISTINCTROW [1 Examiner Select Query].spName, [1 Examiner Select Query].pTitle, [1 Examiner Select Query].pInitial, [1 Examiner Select Query].pKnownAs, [1 Examiner Select Query].pLastName, [1 Examiner Select Query].pCurrentPost, [1 Examiner Select Query].preferredAddress, [1 Examiner Select Query].cdAddress1, [1 Examiner Select Query].cdAddress2, [1 Examiner Select Query].cdAddress3, [1 Examiner Select Query].cdAddress4, [1 Examiner Select Query].cdCity, [1 Examiner Select Query].cdRegion, [1 Examiner Select Query].cdPostCode, [1 Examiner Select Query].WrittenPaperCommitteeMember, [1 Examiner Select Query].ysnBoard
FROM [1 Examiner Select Query]
WHERE [1 Examiner Select Query].spName = '" & sFilterItem & "'  AND [1 Examiner Select Query].preferredAddress=1 AND [1 Examiner Select Query].ysnBoard=1
ORDER BY [1 Examiner Select Query].pLastName"

[Enter Specialty]  has been replaced with  '" & sFilterItem & "'

Enjoy!
J

0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26459823
>>>Also do i just delete the [Enter Specialty] in the original query, or do i have to replace it with something else.

The truth is you could set your original Query to

SELECT 1 FROM 2

And it wouldn't matter.

When you use the QueryDef object, you're basically opening the Query that you name, and filling it with what you tell it to put into it.

I guess is you wanted to, you could literally create just 1 query in your database, and re-use it over and over like we are in your case statements.

As long as your code is written the way we are writing it, doesn't matter what's in the "Original" query.

J
0
 
LVL 7

Author Comment

by:andymacf
ID: 26466086
I think we have success, I need to do some minor tweaking of other queries but on the whole I think we have cracked it.  Thanks again for your efforts.

With respect to running reports using the same criteria, would the attached snippet suffice for what is needed?

Andy
If Nz(sFilterItem, 0) <> 0 Then
             Set qdf = CurrentDb.QueryDefs("Examiners - HOE Examiner Assessment") 'the name of the underlying query
                sSQL = "SELECT dbo_Examiner.strExaminerAssess, dbo_Person.pTitle, dbo_Person.pInitial, dbo_Person.pLastName, dbo_Specialty.spName, dbo_Examiner.strExamCode, dbo_Examiner.strDateInductionCourseAttended, dbo_ContactDetails.preferredAddress, dbo_Examiner.strExaminerAssess1
FROM dbo_Specialty INNER JOIN ((dbo_ContactDetails INNER JOIN dbo_Examiner ON dbo_ContactDetails.personID = dbo_Examiner.exID) INNER JOIN dbo_Person ON dbo_ContactDetails.personID = dbo_Person.pID) ON dbo_Specialty.spID = dbo_Examiner.specialty
WHERE ((dbo_specialty.spName) = '" & sFilterItem & "') AND ((dbo_ContactDetails.preferredAddress)=1)) ORDER BY dbo_person.pLastName"
             qdf.SQL = sSQL
             qdf.Close
             Set qdf = Nothing
             DoCmd.OpenReport "Examiners - Assessment", acViewPreview
         Else
             MsgBox "You didn't select anything, so I'm not going to open this query"
         End If

Open in new window

0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 2000 total points
ID: 26466848
I believe so, as long as the report uses that query as its recordsource, you are in business.

J
0
 
LVL 7

Author Closing Comment

by:andymacf
ID: 31673469
Dear Jeff,

Many thnaks for your patience and understanding over the last few weeks whilst trying to sort out this problem.  I feel I have learnt a great deal from you and expanded my limited knowledge of access considerably.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26469545
Always happy to help!
J
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question