Solved

Access 2007 Disabling "Enter Parameter Value" prompt

Posted on 2011-03-25
15
1,220 Views
Last Modified: 2012-05-11
Can someone tell me how to disable the "Enter Parameter Value" prompt box.  I understand why its popping up because the values are blank, but I still do not want it to pop up.

 I am running a report in Access 2007 and sometimes the values will be blank and sometimes they will have a value.  I don't want the users to have to click ok through these prompts to display the report.

Thanks!
0
Comment
Question by:inimicaljords
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35216353
open the recordsource of the report in design view and remove the criteria for that particular column
0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35216392
I don't want to remove the criteria, because sometimes it will have a value.  I just don't want the prompt.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35216469
you will then need to use a form for opening the report.
* provide a combo box for selecting the parameters for your report
* open the report using vba codes

dim strParam as string

if  strParam & ""="" then
   docmd.openreport "reportName", acviewpreview
 else
  docmd.openreport "reportName", acviewpreview,, strParam

end if
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:peter57r
ID: 35216586
When  the criteria parameter field has a value where is that value coming from?
(You are suggesting that it's not from user entry into the parameter box.)
0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35217294
All of the values are pulled from a table.

For example:

I have a model of a printer which could potential have had at a max 5 different users, but maybe less.

So the report is laid out like

Printer info
  User1
     -info
  User2
    -info
  User3
    -info
  User4
    -info
  User5
    -info

If the printer only has had 2 users, than user 3,4, and 5 will be blank, thus reporting the "Enter parameter value" prompt.  So my easiest thought would be to try and disable the prompt if it is possible because the empty values do not bother me.

Otherwise, I gotta come up with another solution.  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35217343
can you upload a copy of the db.
0
 
LVL 21
ID: 35217344
<<Otherwise, I gotta come up with another solution.  >>

Is each user a separate record?

If the 5 users are fields in the same record then that is your underlying issue. You have repeating fields that  causing your database to not be properly normalized. The de-normalized structure is the root cause of why this is not easy to do.

0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35217647
Yes, they are in the same record.

I tried making 5 different reports and its still not going to work the way I want it to because there will still be blank fields in some users.

I am attaching the DB.  I guess I can put default values in for all fields so there not blank, but that's not really what I wanted to do.

I'm not an expert at making DB so don't make fun of me too bad =P

TA-Printer.accdb
0
 
LVL 21
ID: 35217792
Took a quick peek at your database. You have a single table that looks like a spreadsheet not a relational database.  The dat need to be split inot multiple tables. I see at least two. I am suspecting ther will be more thatn that when designed proeperly.

I will do one level of normalization to see if that solves your issue.  I will post it shortly.

 

0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35217840
I just made that db today so if you have any pointers to make for a better way of doing it, I am open to it and would greatly appreciate the suggestions.  As you can tell from the DB, basically I am trying to keep track of parts and cost it takes to repair the printer as well as notes from the repair tech.  Currently, we are using an excel sheet to do this.  The reason we stop at 5 users is the printers should not need to be repaired more than 5 times before it gets replaced.

0
 
LVL 21
ID: 35218101
When designed properly is does not matter how many user or repairs are done. The design is still the same.

From looking at your design it looks like you are tracking Printer repairs by user. Is that correct?

I have attached a sample of just the tables based on tracking repairs by user just to give you an idea:.




   TA-Printer-HiTechCoach.accdb
0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35232468
I have been playing with your normalized design and I am a bit confused on a few parts.  I see how the relationships are occuring, but I'm not understanding how some of it works.

First off, is it better to use a query to gather the info from tables or use an SQL statement on the form record source?

SELECT * FROM Printers, PrinterUsers, PrinterUsersRepairs;

or

SELECT * FROM qryClientData;

Secondly, the user, parts, and cost are confusing me.  Since I only have one field of Parts and Cost, how do I reflect that onto the form?  I bound part 1 to PrinterUserRepairPart which works fine, but with part 2, how do I get it to select the 2nd PrinterUserRepairPart id?  Seems like I would have to use a loop or something.  The same thing would have to happen for the cost, users, repair tech, and notes.

I do greatly appreciate the help though.  I'm starting to get a better understanding of the relationships.
0
 
LVL 1

Author Comment

by:inimicaljords
ID: 35232533
Also, if I am looking at this right, the notes and tech name should be under PrinterUsers instead of the PrinterUsersRepairs because it will be the same tech and notes for all the parts.  I dont need notes on every part.  Does that sound right?

Just want to make sure im on the right track here.
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 35233010
<<First off, is it better to use a query to gather the info from tables or use an SQL statement on the form record source?>>

A query is a SQL statement.  I see not different in performance between storing the SQL as a saved query or the SQL in the Form's record Source.


<<Secondly, the user, parts, and cost are confusing me.  Since I only have one field of Parts and Cost, how do I reflect that onto the form? >>
Use a sub form. This will allow as many records as needed. No VBA codeing or looping required.


<<Also, if I am looking at this right, the notes and tech name should be under PrinterUsers instead of the PrinterUsersRepairs because it will be the same tech and notes for all the parts.  I dont need notes on every part.  Does that sound right?>>

The only thing you can count on is that things will change.

You need to track the Tech by job or repair if you wan to maintain the history of who did the repair. The chance the Tech will change in the future is probably 100%. Unless the database have a very short life span, like a few months, it is best to think long term.

I would track the notes by part. This way you can build a KB far each part.  If there is a not for each part then you can search and get all the notes for a specific part. If the notes are by repair then it may not be as useful information when you go to mining the data.

This is also a good time to interview the people that will be using the database to find out their needs. What do the tech need out of the system? What does the manager/management need?
0
 
LVL 1

Author Closing Comment

by:inimicaljords
ID: 35242062
thank you for all the help.  It is going to take some learning for me to go through the relationships.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question