Results 1 to 12 of 12
  1. #1
    tylerthompson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    7

    Custom Query Parameter Prompts-- Problem


    Hi all, I'm reworking a previous database for my company's inventory. The previous database is horribly convoluted, and I'm trying to simplify things. To enter the "Edit Inventory" form, two parameters must be passed to the "Edit Inventory" query (in this case, "Supplier Style Number" and "Lot Number"). At the moment, these parameters are passed through simple, default dialog boxes. However, I would like to use a custom dialog box with combo boxes to enter the query parameters. I've already created the form and referenced the combo boxes in the query's Parameter field, yet the old prompts pop up instead, with the form references (i.e. [Forms]![frmQry]![SSN]) as the caption of the prompt. The references seem fine to me, I don't know where the hole is that prevents the query from recognizing the form. This is somewhat time-sensitive and I'd appreciate any help!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Clear the RecordSource from the form and save it and then set it to the query again. Would you believe Access keeps a hidden copy of the old query.

  3. #3
    tylerthompson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    7
    Well the whole RecordSource thing didn't work out, but since you said that Access keeps a hidden copy of the query, I just remade the query and it worked perfectly! Thank you so much!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Glad I could help. Are we ready for the Solved thread tool yet?

  5. #5
    tylerthompson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    7
    Ah, bad news. A new problem came up. When I enter two parameters I know to be valid using the query from the form, it returns a blank datasheet. In fact, all values from the form return a blank datasheet. I tested the values manually and they work. Is this a problem with datatype (i.e. the query not recognizing the right datatype from the combo box)? Or is there something else fishy?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would not let Access determine the datatype since it can get it wrong. Use explicit functions to Type your data. How about posting the SQL view of your query so we can look at it?

  7. #7
    tylerthompson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    7
    No need, I figured out the problem! The combo boxes look up fields in a table, and apparently since the data in the tables has to be paired with a primary key field, the query couldn't recognize the value. I cheated and wrote a script to take the combo box value and transfer it to an invisible text box, and then using the text box as the query parameter. All is working smoothly now. Thanks again for your support, and I apologize for my vague description of the problem. Solved!

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Ouch! Using the hated Lookup Fields in your tables. At least you got things working and thanks for marking the thread.

  9. #9
    tylerthompson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    7
    Whew...that article scares me. However, I don't think I'm using Lookup Fields in my tables. This is the SQL in the Row Source property of my combo boxes:
    SELECT [public_Inventory].[Raw Material Entry], [public_Inventory].[Supplier Style Number] FROM [public_Inventory] ORDER BY [Supplier Style Number];

    SELECT [public_Inventory].[Raw Material Entry], [public_Inventory].[Lot Number] FROM [public_Inventory] ORDER BY [Lot Number];

    I checked the Lookup tabs in my two tables and they were empty. Am I really using Lookup Fields? I really want to avoid bad Access practices, which is why I'm remaking the database in the first place...

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The Lookup tab of the field will have a ComboBox and an SQL statement if you are using Lookup Fields.

  11. #11
    tylerthompson is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Posts
    7
    Whew! I think I'm safe then. Thanks!

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Good! Have fun with the rest of the project.

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

Similar Threads

  1. Display Query Value That Requires Input Parameter
    By Access::Student in forum Access
    Replies: 1
    Last Post: 05-29-2009, 08:43 PM
  2. Problem with parameter driven combo box
    By clydet2 in forum Queries
    Replies: 0
    Last Post: 04-06-2009, 12:19 PM
  3. Replies: 6
    Last Post: 02-20-2009, 11:50 AM
  4. Run Query without prompts
    By gailoni in forum Queries
    Replies: 4
    Last Post: 11-03-2008, 01:42 AM
  5. Add combo box to parameter query
    By louisa14 in forum Queries
    Replies: 1
    Last Post: 12-10-2005, 08:38 AM

Tags for this Thread

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