Solved

Access 2007 Disabling "Enter Parameter Value" prompt

Posted on 2011-03-25
15
1,200 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
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
 
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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now