Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Parameter prompt (Access 2000)

    I have created a simple form to look up a single record in an order table, and have included "Like [Enter Order No.]" in the criterion for the Order No. field so that I can look up a specific record, but how can I modify the criterion so that (a) if the entered number doesn't exist in the table, I can display a message or jump to the next record that does exist, and ([img]/forums/images/smilies/cool.gif[/img] so that once the intial record has been displayed, I can use the arrows to step backwards and forwards to view other records.

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

    Re: Parameter prompt (Access 2000)

    Remove the criteria from the query.
    Open the form in design view.
    Make sure that the Control Wizards button on the Control Toolbox is on (down)
    Place a combo box on the form, for example in the form header.
    The Combo Box Wizard should start.
    Specify that you want to look up a record (option 3)
    Follow the instructions, and specify the table in which the Order No field resides, and the Order No field.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Parameter prompt (Access 2000)

    Thanks for your reply. I followed all the steps that you listed, but after selecting the Order No. field in the Combo Box wizard, I got an error message, "Syntax error in query expression '[SELECT [Order]].[OrderNo]' ".

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

    Re: Parameter prompt (Access 2000)

    Strange - there shouldn't be a square bracket before SELECT and only one after Order. I can't explain that. I would try again.

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

    Re: Parameter prompt (Access 2000)

    Check the query parameters and see if you have anything in there. Access 2000 and later are very helpful about adding missing brackets in parameters. The problem is that the brackets don't get put in the right place.
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Parameter prompt (Access 2000)

    I tried again from scratch, but the same error occurred. I don't get an error with the first two types of combo box (although they don't seem to do much), only with the third type (record lookup). Is there another way to create a record lookup box? BTW, I'm using Access with linked SQL tables; does that matter?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Parameter prompt (Access 2000)

    Where should I look to check the query parameters?

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

    Re: Parameter prompt (Access 2000)

    You can create the combo box without the wizard, it's not that hard. See if you can create a lookup combo box with the wizard in a database with local tables, then study its properties and the code for the After Update event generated by the wizard. That should give you an idea how it should look for your form.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Parameter prompt (Access 2000)

    Thanks, yes with a bit of trial and error I got it working as you suggested.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Parameter prompt (Access 2000)

    I created another combo box on another form by copying the code from this one and replacing the combo box name and the field name, but there are two problems.

    (1) The form is ordered by the RefNo field, which is the one that I want to look up using the combo box. However, the list in the combo box is not in the same order as the records in the form itself are; e.g., the form lists records in the order ABC001, ABC002, ABC003, etc., but the list in the lookup combo box has ABC001, XYZ987, ABC003, etc., (which reflects the ordering of the records by the primary key, which is not used on the form.) How can I get the list in the lookup combo box to be ordered differently?

    (2) When I enter a reference no. in the lookup combo box, the box correctly displays a list of reference nos. from the records in the file, but when I press enter or click on an entry, I get a Run-time error '13': Type mismatch, which the de###### identifies as occurring in the following line.

    rs.FindFirst "[RefNo] = " & Str(Me![Combo10])

    How can I correct this?

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

    Re: Parameter prompt (Access 2000)

    1. Create a query that returns the reference numbers in ascending order, and use this query as Row Source for the combo box.

    2. Since RefNo is a text field, you must enclose the value in quotes. One way to do this is to concatenate with Chr(34) (34 is the ASCII code for "):

    rs.FindFirst "[RefNo] = " & Chr(34) & Me![Combo10] & Chr(34)

    (No need to use Str here)

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Parameter prompt (Access 2000)

    Thanks muchly for another prompt and patient reply to another newbie question (I'm learning fast!)

    One more question if I may: How can I suppress blank entries from the lookup list?

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

    Re: Parameter prompt (Access 2000)

    In the query that acts as Row Source for the combo box, put

    Is Not Null

    in the Criteria line under the RefNo field.

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Parameter prompt (Access 2000)

    The combo box's row source doesn't have a query, it just contains the following code.
    SELECT[dbo_Client].[ClientId], [dbo_Client].[FamilyName], [dbo_Client].[FirstName] FROM dbo_Client ORDER BY [dbo_Client].[FamilyName], [dbo_Client].[FirstName]

    I tried appending the following code that I found in some other queries,
    WHERE (([dbo_Client].[FamilyName]) Is Not Null)
    but I get a syntax error.

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

    Re: Parameter prompt (Access 2000)

    The WHERE part should come before the ORDER BY part. Try this:

    SELECT [dbo_Client].[ClientId], [dbo_Client].[FamilyName], [dbo_Client].[FirstName] FROM dbo_Client WHERE [dbo_Client].[FamilyName] Is Not Null ORDER BY [dbo_Client].[FamilyName], [dbo_Client].[FirstName]

Page 1 of 2 12 LastLast

Posting Permissions

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