Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65

    Report And Subreport multiple user inputs

    I have a report that is linked to one query and then a subreport that is linked to another query. Both queries have the same user input (Work Order Number). However, when i open the report, i get a prompted for the work order number 3 times. How do I fix this so I only get one prompt? Thanks in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Need to know more about report design. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65
    Here is the attached DB: Sample DB.zip Thanks for the help!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Can't run the report with the linked workbook.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65
    Please try this DB: Sample DB.zip I replaced the linked spreadsheet. Thank you!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Haven't had a chance to look at the revised db but it occurred to me after my previous posting that you are probably using parameterized query with input prompts. I never use input prompts. Instead, input criteria on a form and reference the form control as parameter. Review: http://datapigtechnologies.com/flash...mtoreport.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65
    June7,

    Thanks for the link, it worked perfectly! I decided to use a text box instead of a combo box. Is there anyway to have a validation so that if the user types in a Work Order Number that doesn't exist, an error message can pop up saying so?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you use a combobox that restricts input to item from the RowSource list, that validation is not needed. Otherwise will need code in textbox BeforeUpdate event that queries the table for the value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65
    Can you help me with the code for the BeforeUpdate event? I think this will be the only way to do it being that there are a lot of Work Order numbers and new ones are generated weekly. It would be too long of a list if we listed all the Work Order numbers in a combo box.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What is 'too long'? I have comboboxes that list thousands of order numbers. Often I set the RowSource to sort descending so the most recent are at top of list. Since users are familiar with the order number structure, they start typing the number and AutoExpand property of combobox matches number in list.

    Review http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65
    So I took your advice and used a combo box. Is there a way to get rid of duplicates in the combo box list?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why are there duplicates? Don't you have a work order table where the work order number is a unique ID?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65
    No I don't have a table like that. The table I'm using is based on production. So if a work order has to make 500,000 parts, it might take 3 weeks to complete that which means that the work order will show up each week. I'm pulling the work order from that table.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So each record repeats Work Order info - number, date, client, etc? Not sure data structure is normalized.

    However, use DISTINCT keyword in query or do a GROUP BY.

    SELECT DISTINCT WONum FROM table;

    or

    SELECT WONum FROM table GROUP BY WONum;

    Both should produce the same dataset.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    JGrots is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Jan 2013
    Posts
    65
    Sorry for being slow, but where do I type this into?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using user inputs as variables in a query/report?
    By cheese9799 in forum Reports
    Replies: 3
    Last Post: 03-01-2011, 11:30 AM
  2. Handling multiple inputs on form fields
    By wake74 in forum Access
    Replies: 1
    Last Post: 09-14-2010, 11:06 AM
  3. Report and Subreport
    By newbie in forum Reports
    Replies: 0
    Last Post: 02-16-2010, 12:14 PM
  4. Replies: 1
    Last Post: 11-30-2009, 05:11 PM
  5. Multiple inputs one result
    By ee12csvt in forum Queries
    Replies: 0
    Last Post: 09-11-2009, 03:19 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