Color of Data Sheet (1 Viewer)

[WILYWAYNE11]

I have designed a query, but when I run it the resulting data sheet view table is light gray. How do I change the color of the grid to white?

Thanks for the help

 

[arnelgp]

you create a Datasheet Form for your query.
when you View the datasheet Form, you can change the colors
on the Form's Contextual tab (Form Datasheet).

 

[GPGeorge]

I have designed a query, but when I run it the resulting data sheet view table is light gray. How do I change the color of the grid to white?

Thanks for the help

As Arnel suggest, the most appropriate way to manage this is to use an interface object to display the data to the user with layout and formatting that you want to apply.

Remember, all relational database applications consist of several layers, or tiers.

1. Data Layer-- this is where the data is stored; in tables
2. Logic Layer -- this is where you write code to manage data and manipulate the user interface; VBA or macros
3. Interface Layer -- this is where you provide a way for users to interact with data indirectly; forms and reports

A good application maintains that separation. In other words, users only see or interact with data by means of an interface object, never directly in tables. Unfortunately, Access gets used in ways that are contrary to that principle. Primarily that can be attributed to the unique Access development environment which incorporates all of those elements in a single accdb file making it seem easy to blur that structure by injecting interface properties into tables and so on.

That leaves us with queries. Where do they fit? I would argue that queries are behind-the-scenes components of all three of the other components. Queries work directly with tables to retrieve filtered, sorted and aggregated data from those tables. They also work directly with forms and reports by providing datasets of that filtered, sorted or aggregated data to the forms and reports, which in turn provide the user the ability to see and manipulate that data in a controlled manner. Queries also work in code, where you can open recordsets, for example, or update data and perform other data management tasks safely out of the sight of the user.

In short, queries reach across all of the other tiers in a support role, not as a direct user interface to the data. Don't try to overload that function by expecting users to directly see queries, with or without interface defined layout and formatting.

Allowing for the uncommon situation where an advanced user can be trusted to work directly with your organization's precious data via queries that you provide or that they create themselves, I would venture to say that queries really don't need to be shown to users.

 

[WILYWAYNE11]

As Arnel suggest, the most appropriate way to manage this is to use an interface object to display the data to the user with layout and formatting that you want to apply.

Remember, all relational database applications consist of several layers, or tiers.

1. Data Layer-- this is where the data is stored; in tables
2. Logic Layer -- this is where you write code to manage data and manipulate the user interface; VBA or macros
3. Interface Layer -- this is where you provide a way for users to interact with data indirectly; forms and reports

A good application maintains that separation. In other words, users only see or interact with data by means of an interface object, never directly in tables. Unfortunately, Access gets used in ways that are contrary to that principle. Primarily that can be attributed to the unique Access development environment which incorporates all of those elements in a single accdb file making it seem easy to blur that structure by injecting interface properties into tables and so on.

That leaves us with queries. Where do they fit? I would argue that queries are behind-the-scenes components of all three of the other components. Queries work directly with tables to retrieve filtered, sorted and aggregated data from those tables. They also work directly with forms and reports by providing datasets of that filtered, sorted or aggregated data to the forms and reports, which in turn provide the user the ability to see and manipulate that data in a controlled manner. Queries also work in code, where you can open recordsets, for example, or update data and perform other data management tasks safely out of the sight of the user.

In short, queries reach across all of the other tiers in a support role, not as a direct user interface to the data. Don't try to overload that function by expecting users to directly see queries, with or without interface defined layout and formatting.

Allowing for the uncommon situation where an advanced user can be trusted to work directly with your organization's precious data via queries that you provide or that they create themselves, I would venture to say that queries really don't need to be shown to users.

Great info, but didn't tell me how to correct the issue.

 

[Pat Hartman]

Great info, but didn't tell me how to correct the issue.

Didn't @arnelgp tell you how to solve the problem? George was merely explaining why you shouldn't be even using a query in this situation.

I've never used arne's method. This is what I use:

Private Sub Form_Open(Cancel As Integer)
    Me.DatasheetBackColor = 12574649            ''green
    Me.DatasheetAlternateBackColor = 12574649
    'Me.DatasheetBackColor = 13487510           ''blue
    'Me.DatasheetAlternateBackColor = 13487510
End Sub

You have to figure out the value for the color you want. I included two.

 

[CJ_London]

The issue is you can't modify that property for a query, you need to use a form (which can be viewed as a datasheet) as Arnel suggests. i.e. create the form, open it a datasheet, click on the datasheet tab in the ribbon

 

[Pat Hartman]

@CJ_London So you want to modify the query property manually? You don't want to use VBA to set the background property of a form? OK. Whatever

 

[isladogs]

Although you can't alter the background colour for a query from its property sheet, you can do so from the ribbon.
Open the query in datasheet view, click the Home tab on the ribbon and look at the Text Formatting section.
From there, you can alter both the background default colour and the alternate row colour

 

[CJ_London]

OP said 'a query' - what is the point of VBA code in that situation?

 

[Pat Hartman]

The point of the code, if you read George's reply is that opening a query for the user to interact with is downright dangerous and should never be done unless you have a death wish. Forms are for interacting with users. That way you get to control updates and validate data. Unless of course you don't care what the user does to the data

 

[CJ_London]

where does the OP say 'for the user to interact with'? For all we know the query is not updateable

But don't disagree about users working directly queries

 

[skymou]

A form is better, but you can manipulate many display properties in a query. For example, once the datasheet is opened:

Screen.ActiveDatasheet.DatasheetBackColor=12574649
Screen.ActiveDatasheet.DatasheetAlternateBackColor = 12574649

Steve

 

[GPGeorge]

where does the OP say 'for the user to interact with'? For all we know the query is not updateable

But don't disagree about users working directly queries

I don't think it's possible to guarantee that users won't try to modify data if they have the option to do so. It's not a matter of the developer's intent, really. If you give a person a query, they can, and all too often, will use it in ways not intended.

 

[skymou]

If you want the saved query to remove the property, you can add the property to the QueryDef and then do something like:

CurrentDb.QueryDefs("Query1").Properties("DatasheetBackColor")=12574649

 

[skymou]

If you want the saved query to remove the property, you can add the property to the QueryDef and then do something like:

CurrentDb.QueryDefs("Query1").Properties("DatasheetBackColor")=12574649

I meant "remember", not "remove"

 

[skymou]

If the query is a snapshot or has a Group By, users cannot edit the results.

 

[Pat Hartman]

where does the OP say 'for the user to interact with'? For all we know the query is not updateable

When someone asks this question, it is because they think creating forms is hard so they don't have enough experience to understand how dangerous naked queries can be. Apparently @skymou is quite the fan though.

 

[skymou]

I am not a fan of displaying query datasheets to users, just answering the original question. My first words were "A form is better"...

 

[Pat Hartman]

I am not a fan of displaying query datasheets to users

You know so much about this that you had me fooled.

I see that you are fairly new here. Welcome

Sometimes when you answer questions, you might think about what prompted the question and how dangerous the answer is. Many people try to solve the problem themselves and then turn to us if they can't get the syntax right. This question is a prime example. So, do you just correct the syntax (i.e. load the bullets into the gun) or do you say, maybe this is not a good solution given its potential for danger? Try doing it this way instead.