Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2034
  • Last Modified:

Query Formatting in Access 2010

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
Tracy
Asked:
Tracy
  • 6
  • 4
  • 2
2 Solutions
 
Jeffrey CoachmanCommented:
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
 
TracyVBA DeveloperAuthor Commented:
Thanks for your reply Jeff.

Yes, it's the Datasheet view for the query, not on a report.
0
 
Jeffrey CoachmanCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TracyVBA DeveloperAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
TracyVBA DeveloperAuthor Commented:
>>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
 
TracyVBA DeveloperAuthor Commented:
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
 
Jeffrey CoachmanCommented:
<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
 
mbizupCommented:
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
 
TracyVBA DeveloperAuthor Commented:
mbizup - I took a snapshot of the Datasheet section and I don't see the Alternate Background Color.
Screenshot.JPG
0
 
mbizupCommented:
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
 
TracyVBA DeveloperAuthor Commented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now