Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter Query--Opening Lookup List for Selection (Access 2000)

    Can someone please let me know if there is a way to open up a lookup table (or its form) when running a parameter query and then take the user's selection in the lookup list as the parameter selection to filter the table? I have a table that lists raw materials and their code numbers. Users want to search the main table by the name of the raw material, which is entered via a combo box on the main form linked to the lookup list table; however, users more than likely won't remember the spelling of some of the materials, e., g., "2-Chloromethyl-3,5-dimethyl-4-methoxy pyridine hydrochloride". Is there a way to select the material from a list and then use that selection to run the parameter query? Any help you can give this novice programmer would be very much appreciated. Thanks...Mary

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    I would guess that you are running the parameter query from a command button. If that

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    I'm not sure where the parameter query comes into this. What are you doing with it? You can certainly create a dropdown on a form that lists the items in the lookup table and then use the selection to filter the form records. No parameter query is required. Perhaps if you explain further, someone can help you find an answer.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    Hello, Rupert...I just tried your suggestion and indeed running the query from a command button on my Switchboard works great! Thank you very much for the wonderful tip--my users will certainly appreciate not having to look up the spelling of those raw materials!...Is there a trick to developing a "report" based on that same parameter query that is run from a command button? I tried to create a report but am not having any luck with creating a report based on this particular parameter query. Any advice you can give me about creating a report based on this parameter query will be greatly appreciated. Thanks again...Mary

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    Thank you for your quick response, Charlotte. The query was created to be the base for a monthly report. Rupert's suggestion worked great for running the query, and now I'm trying to figure out how to create a report based on that particular parameter query. Any advice you can give me will be greatly appreciated. Thanks again...Mary

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    The following code is from the "Service Call Management" example DB supplied with Access. If you place this code in your reports code module and substitute your form name for "Report Date Range". You will need to base the report on the "parameter" query you modified earlier.

    <pre>Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1
    End Sub
    '
    '
    '
    Private Sub Report_Close()
    DoCmd.Close acForm, "Report Date Range"
    End Sub
    '
    '
    '
    Sub Report_Open(Cancel As Integer)
    DoCmd.OpenForm "Report Date Range", , , , , acDialog, "Sales by Month"
    If Not IsLoaded("Report Date Range") Then
    Cancel = True
    End If
    End Sub
    </pre>


  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    Hello, Rupert...Many thanks for the report code. I put the code in my report module (on open, close, nodata events), but when I try to run the report, I get a "Compile error: Sub or Function not defined" error message and it stops on "IsLoaded". I rechecked the code to make sure I had substituted my form's name and report's name where necessary and it seems okay. Do you know what the problem might be? Again, many thanks for your help....Mary

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    Sorry...

    I should have included this function as well. Put this in a module called "basUtility" or something similar (Name not important)

    <pre>Function IsLoaded(ByVal strFormName As String) As Integer
    ' Returns True if the specified form is open in Form view or Datasheet view.

    Const conObjStateClosed = 0
    Const conDesignView = 0

    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
    If Forms(strFormName).CurrentView <> conDesignView Then
    IsLoaded = True
    End If
    End If

    End Function</pre>


  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    Rupert, thanks....I copied the Function into a new module called "basUtility" as you suggested and tried running the Report that is based on the parameter query in question. The lookup form pops up for user to select the raw material, but once combo box selection is made and the user clicks OK, the query opens behind the lookup form, instead of the Report, and the form stays on screen. What else should I be doing? Appreciate your help!....Mary

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    Remove all the the code from the command button and replace it with this >>>> Me.Visible = False

    Change the forms border style to Dialog
    set popup to "Yes" and modal "yes"

    I think, hope that i'll work!

    (I hope you are making backups [img]/forums/images/smilies/smile.gif[/img]

  11. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query--Opening Lookup List for Selection (Access 2000)

    Thank you, Rupert....I've put my new database aside for awhile, but will soon try the procedure again incorporating your last suggestion. I appreciate all your help!...Mary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •