Results 1 to 12 of 12
  1. #1
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291

    bypassing passing parameter when null

    I have a form to input data and on that form I have a combo box that I want to be able to select an item in the list and then only the records which have that value will I be able to navigate to using the next record buttons.

    My plan to do this is to have a query which receives a parameter from the combo box:


    PARAMETERS [Forms]![InspectionForm]![cmbArea] Text ( 255 );
    with a where statement
    WHERE ((IIf(([Forms]![InspectionForm]![cmbArea]<>"ALL"),(([Inspections].[Area])=[Forms]![InspectionForm]![cmbArea]),[Inspections].[MNumber]<>""))<>False)

    My problem lies in when I open the inspection form it asks for a value for the parameter because I am using the query as the record source for the inspection form.

    My ideas for solutions,
    1 - Is there a way that I can set if the parameter I am "Passing" doesnt exist yet it will just bypass it?
    2 - Maybe use the table instead of the query for the inspection forms record source and then before update event of the combo box write code that would filter what records could be selected. (If this is an option I dont know how to filter records without changing the record source so I would need some help there)

    Any other ideas or help would be appreciated, thanks

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I too have had issues with this. The way I've gotten around it is this (only really plausible if you dont have many criteria):

    Lets say for example I have a list of people and have them gender separated. I want, in my form, to be able to filter by gender, or not at all.

    i would use (syntax most likely wrong. i can never remember the proper way off the top of my head):
    iif(isnull(forms!frm!chkGender),gender = M OR gender = F,gender = Forms!frm!chkGender)

    what this does is accomplish your goal without doing it the way you want. basically, if theres no option given on the form, the query will look for all possibles (the reason why it wont work for too many criteria). if there is an option, then it passes the chosen option.

    Again, syntax is probably incorrect but the logic is good.

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    ID Name Color 1stPreference 2ndPreference 3rdPreference 1 RuralGuy Blue Yes No No 2 Pbaldy Red No Yes No 3 ajetrumphet Yellow Yes No No 4 Drake Indigo No Yes No 5 Maximus White Yes No No 6 Cowboy Violet No No Yes 7 Sam Blue No Yes No
    Above is a Table that I have called Table1. The db has a form exp. It has a Combobox to select colors and a option group to select preference.

    i have used Docmd.ApplyFilter to filter the Following ways:
    1) when only colors Selected. all entries relatin to that color is filtered.
    2) when Color and and preference is selected filter condition takes both the criteria in consideration to filter records.
    3) When all color is selected and a preference is selected then records are only filtered according to preference.

    Two Labels one to clear filter the other to deselect the Preference Option Box.

    Code used:

    Private Sub Combo16_AfterUpdate()
    Dim strCondition As String
    Dim strPreference As String


    Select Case Me.Frame18
    Case Is = 1
    strPreference = "[1stPreference]=" & True
    Case Is = 2
    strPreference = "[2ndPreference]=" & True
    Case Is = 3
    strPreference = "[3rdPreference]=" & True
    End Select

    Select Case strPreference
    Case Is = ""
    Select Case Me.Combo16
    Case Is = "All"
    Me.Filter = ""
    Case Else
    strCondition = "[Color]=" & "'" & Me.Combo16 & "'"
    DoCmd.ApplyFilter , strCondition
    End Select
    Case Else
    Select Case Me.Combo16
    Case Is = "All"
    strCondition = strPreference
    DoCmd.ApplyFilter , strCondition
    Case Else
    strCondition = "[Color]=" & "'" & Me.Combo16 & "' And " & strPreference
    DoCmd.ApplyFilter , strCondition
    End Select
    End Select
    End Sub


    scope of this code:
    1) Filter Records according to criteria provided by a Combobox and an option group on the form.
    2) navigation button can be used to navigate throufgh the filtered records.


    if this solves your problem mark the thread solved.

    refer to the sample mdb attached.

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    is your problem solved. If yes mark this thread solved. If you have solved the problem in some other way please post it for the convinence of others.

    Thanks.

  5. #5
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    I am still working on the problem, I will update status when I finish. Your Info is helping though, thanks much.

  6. #6
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    Ok, currently I am passing a value to a query to filter what records I look at. I think I was doing it the wrong and the way you described is probably better but I need a little help.

    When I click my button from my main form I will get the combobox value that will be used as filter. Then I open the form with the filter. My question is, I have a filtered selection how do I then press a print button to open a report with the same filter.

    Ideas:
    1 - Make a textbox on the form that could store the filter and then when use that value when going to print.
    2 - Thats all I can think of, I dont think a global variable would work but I could be wrong.

    Thanks for help.

  7. #7
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    That filter is either stored as a query or a subform. Whichever it is, you can create a report from it. As long as the fields you want in the report wont change when the filtered results change, you can have a stock report and have it populate with a command button.

  8. #8
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    I dont understand.

  9. #9
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    You have a saved query in which you pass the criteria to in order to filter your results. Well, you can create a report (reports tab) that will take that query and the parameters you passed to it, and create a report with all the fields of that query. You can then save that report as a pdf (or whatever else it supports) and print it off. Just fillow the directions on the report wizard.

  10. #10
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    so this query is acting as a storage variable?

  11. #11
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    From my understanding of your description, yes. Just follow the report wizard.

  12. #12
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Hi what is the problem that you have now. do you want to use the information filtered to be passed on to a report if it is the case just let me know I think I have a solution. tell me exactly what you need.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-11-2010, 02:11 PM
  2. Passing SQL result into variable
    By jonny in forum Access
    Replies: 3
    Last Post: 10-18-2009, 07:46 AM
  3. Passing a variable to a form
    By cjamps in forum Forms
    Replies: 0
    Last Post: 03-02-2009, 05:32 AM
  4. Passing Values
    By dromorkid in forum Forms
    Replies: 0
    Last Post: 11-25-2008, 05:04 PM
  5. Bypassing Startup options with Ctrl W
    By rayc in forum Security
    Replies: 0
    Last Post: 09-06-2006, 09:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums