Solved

Query Formatting in Access 2010

Posted on 2011-02-18
13
1,973 Views
Last Modified: 2012-05-11
I'm starting to use Access 2010 and I have a question about the new formatting options in queries.  I setup a query with the layout I want.  I want to show totals, and I setup the color to highlight every other line in datasheet view and some other little things.

My question is, how can I save this as a default for all other queries.  I need to apply it for all new queries, as well as any existing ones.

Thanks for the help.
0
Comment
Question by:broomee9
  • 6
  • 4
  • 2
13 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34929342
Does this Have to be done in Datasheet view?

You did not post any details on how you:
"setup the color to highlight every other line in datasheet view and some other little things."
...so I can't be sure what you are asking for is possible.

For example, Access can have default template Forms or Report, ...not queries.

Depending on what exactly you did, AFAICT, this can all be done in a report.
Create a new blank Report and set all your options/settings.
Then name the report:  rptCustomTemplate1
Then in the Database Options-->Object Designers-->Form/Report Design View-->Report Template
Type in: rptCustomTemplate1

Compact/repair the DB and create a new report in design view.
It will have your custom settings.

JeffCoachman
0
 
LVL 24

Author Comment

by:broomee9
ID: 34929647
Thanks for your reply Jeff.

Yes, it's the Datasheet view for the query, not on a report.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34953922
Again, I don't know of any way to create a "Design template" of a query.

Will you be posting the specifics on:
    "setup the color to highlight every other line in datasheet view and some other little things."
... I am still at a loss as to what this means specifcally

I don't know how re-wording the question will help....
But I leave that to you.

Jeff
0
 
LVL 24

Author Comment

by:broomee9
ID: 34954162
If you have Access 2010 go into Datasheet view on a select query.  Select the Home tab and you will see the formatting options I mentioned.  Once I make all the changes I want, my actual question is how to make it so these changes will be seen in all select queries in datasheet view ("My question is, how can I save this as a default for all other queries.").

>> Will you be posting the specifics on:
    "setup the color to highlight every other line in datasheet view and some other little things."
... I am still at a loss as to what this means specifcally
See attached screenshot.  I already know how to do this, so this is not what I'm asking for help with.  How do I save these changes for future use is what I'm asking for.

>>I don't know how re-wording the question will help....
It didn't seem like you understood what I was talking about, so perhaps re-wording it would be more clear.

If you have any suggestions, please let me know.  I thought this was a dead question though, so that's why I was going to delete it.  But if you know what I'm talking about now and know how to do it, then I'd be happy to hear it.
Snapshot.jpg
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 150 total points
ID: 34954771
Thanks for the clarifications.

1. <How do I save these changes for future use.>
I just double-checked again in Access 2010 to be sure.
I don't see a way for this to be done.

2. I guess my question was why this had to be in a query specifically.
Again, while there is no direct way to saye a query template, although there is a way to save a form or report template.
You can set up a form to display in Datasheet view and do the Alternate row shading.

However the "Total" would be tricky.
A datasheet view form wil not allow for totals.
To do this you will need to create a "Tabular/Continuous" form, then put a control in the form footer with a controlsource of: =Sum([YourField])
...but again, this requires a differnt form design, and there is no way to make a "template" that might sumarize fields.

So again, if your ultimate question is:
<How do I save these changes for future use>
Unfortunately, the answer appeares to be: You can't

JeffCoachman
0
 
LVL 24

Author Comment

by:broomee9
ID: 34955209
>>1. I don't see a way for this to be done.
I didn't see a way either, hence the question :-)

>>2. I guess my question was why this had to be in a query specifically.
I search through the data in my tables a lot for ad hoc questions by users.  It's very handy to have the totals added (not necessarily for a specific field, but just added to the bottom, so I can select as necessary) and for every other row to be highlighted.  My tables have millions of records, so it's nice to differentiate between the lines, especially when there are many many columns in each table.  (Note: I did not design the tables, they're linked tables to an ODBC using SQL Server).

I didn't want to use a form, because I'm looking for something quick and dirty, but I didn't want to have to do it every single time I look at any query in datasheet view.

If it's alright with you, I'll leave this open for a day or two and see if any other experts pipe in, if not I guess the answer is it can't be done, and I'll give you the points for your comment http:34954771

Thanks again for taking a look.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 24

Author Comment

by:broomee9
ID: 34955223
One change:
I search through the data in my tables

Should be:
I search through the data in my tables by using queries so I can aggregate the data
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34957715
<If it's alright with you, I'll leave this open for a day or two and see if any other experts pipe in>
Sure, no problem.

;-)

Jeff
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34959832
broomee9,

If I'm reading your question right, you are simply trying to alternate colors (highlighting) in your datasheet views of queries, tables, etc.

This works in Access 2007, and I imagine that Access 2010 is a slight variation:

- Click the office 'blob' in the upper left-hand corner of the Access window
- Click 'Access options'
- Goto the Datasheet options
- Change the "Alternate Background Color" setting to whatever you want your highlight color to be.

This persists (ie it is 'saved'), and it applies to all datasheet views in your database.
0
 
LVL 24

Author Comment

by:broomee9
ID: 34960887
mbizup - I took a snapshot of the Datasheet section and I don't see the Alternate Background Color.
Screenshot.JPG
0
 
LVL 61

Accepted Solution

by:
mbizup earned 350 total points
ID: 35033783
broomee9,

Sorry about the delayed response...

You're absolutely right - it appears that the Access 2010 upgrade has reduced the flexibility in datasheet formatting that Access 2007 has.

You can however still highlight alternating rows by selecting 'Automatic' as shown in the attached screenshot.

This will highlight every other row, based on whatever the color scheme for the database is overall.  And unlike actually choosing a color, if you choose 'automatic' highlighting it persists ('saves') from query to query, and datasheet views of tables, etc throughout the current database (but I think you are stuck with the default theme highlighting color).

I think this is as close as it gets to what you are trying to do.

Datasheet-Highlighting.PNG
0
 
LVL 24

Author Closing Comment

by:broomee9
ID: 35070193
OK, thanks for the explanation.  I split the points mostly because both answers were it can't be done but mbizup, your answer showed at least one piece that can be done.

Thank you both.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 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

18 Experts available now in Live!

Get 1:1 Help Now