Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form based on Parameter qry (Access 2000)

    I have a parameter query which I open in design view to enter ID#'s. The format is: Like "4525" Or Like "Z495" etc.. Is there some way to setup a form (dialog?) which will assist the user(novice) is entering these ID's? Also, if a typo occurs they will have to be able to edit the numbers & rerun the qry.
    Thank You

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form based on Parameter qry (Access 2000)

    You could create an unbound form (that is, not based on a table or query).
    In design view, put a text box on the form. Name it (for example) txtID and set the caption of the accompanying label to "Enter an ID" or something like that.
    Next, put a command button on the form. If you are not familiar with code, you can use the wizard to create it; select the "Other" category and from it, the "Run Query" action. In the next step, you can select the query to be run. Name the command button (for example) cmdOK.
    If you like, you can also place a command button on the form to close it, but it's not really necessary.
    You can now save the form and close it. Name it (for example) frmSelectID.
    Now, open the query in design view. Replace the criteria you have now by one of the options from the table below (or your own variation):

    <table border=1><td>Purpose</td><td>Criteria</td><td>ID exactly as supplied</td><td>[Forms]![frmSelectID]![txtID]</td><td>All ID's beginning with supplied text</td><td>Like [Forms]![frmSelectID]![txtID] & "*"</td><td>All ID's containing supplied text</td><td>Like "*" & [Forms]![frmSelectID]![txtID] & "*"</td></table>
    Note: in general, I would advise against having end users open tables or queries. Instead, I would create a form based on your query, and open *that* when the user clicks the command button.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form based on Parameter qry (Access 2000)

    Perhaps I wasn't very clear on what I 'm attempting to do.
    1) I am trying to keep the end user away from the qry.
    2) I guess I'm not using a parameter qry, but have been opening the qry in design view & in the ID column, criteria - would enter my data as such: Like "3339" Or Like "3232" etc.. Sometimes up to 75 ID's per instance and all #'s are as random as they get.
    3) The form would have to accept multiple ID's w/the proper format (Like "99" Or Like "22" Or Like "11") and upon completion save this in the qry, ID column, criteria. From there a report opens, each page displaying 3 records in the format of Index Cards, based on the IDs given.
    4) From there I already have a button which opens a report for viewing & printing the cards.
    The bottom line is "a novice will be entering the numbers, & I want to avoid any complications"
    Thank You

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Form based on Parameter qry (Access 2000)

    Be careful, as you might exceed the length of the criteria in the query. There seems to be no simple way to do this.
    Another way, input all your "likes" in a form based upon a temporary table, they would enter just the 99 or 3236 etc.
    Then build up a SQL on the fly using VBA code and read thru the temporary table for each of the "likes" then use the CreateQueryDef command to create the query.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form based on Parameter qry (Access 2000)

    To expand on Pat's idea. You do not appear to be using the Like statement with wild cards but only to get an exact match. If this is the case then the new table would probably be your best bet. If you create a new table with just the ID field and add this to the query linking the ID fields with a join, then only the records in the new table will be available for the report. if you add the new table to a continous form your users can use this to edit the ID's they wish to report on. You could also add a reset button to delete all records when they wish to start a new report.

    Hope this makes sense :-)

    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form based on Parameter qry (Access 2000)

    One possibility is to use a method from Microsoft's query samples database.

    Add a calculated field InParam([ID],[Type IDs separated by commas]) to the query, and set the criteria for this field to True. InParam is a custom function. Create a standard module with the following code:

    <font face="Georgia">Function GetToken(stLn, stDelim) As String
    Dim iDelim As Integer
    ' Searches for commas as a delimiter
    iDelim = InStr(1, stLn, stDelim)
    If iDelim Then
    GetToken = Trim$(Mid$(stLn, 1, iDelim - 1))
    stLn = Mid$(stLn, iDelim + 1)
    Else
    GetToken = Trim$(Mid$(stLn, 1))
    stLn = ""
    End If
    End Function

    Function InParam(Fld, Param) As Boolean
    ' The following two lines are optional, making queries case-insensitive
    Fld = UCase(Fld)
    Param = UCase(Param)
    Fld = Trim$(Nz(Fld))

    ' Parses values separated by commas
    Do While Len(Param)
    If GetToken(Param, ",") = Fld Then
    InParam = True
    Exit Function
    End If
    Loop
    End Function</font face=georgia>

    Of course, if the user needs to enter 75 ID's, this is going to be (a) a nuisance to the user, and ([img]/forums/images/smilies/cool.gif[/img] slow.

    Another possibility is to display all available ID's in a multi-select list box on a form. You can then create the criteria in code by looping through the Selected array.

Posting Permissions

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