Solved

MS Access and DataGrid

Posted on 2004-04-08
11
5,964 Views
Last Modified: 2012-08-13
Hi,

I have an access form that I would like to add a data grid to. Everything I have read says "Don't Do It!"
I know that I can show an access form in the datasheet mode and that will display like a grid. However, I can't find a way to make the datasheet appear in a cross tab format.

For example, my table contains the following fields: SKU, STORE, QTY when pulling data for my form I just select all records from the table and assign the correct values to the txt boxes. The datasheet view then appears and looks like this:

SKU:    STORE:   QTY:
11111       335       10
11111       336       11
11111       337         9
22222       335       22
22222       336       19
22222       337       10
33333       335       10
33333       336       15
33333       337        0

But what I would like is this:

SKU:       STORE 335:   STORE 336:    STORE 337:
11111            10                  11                  9
22222            22                  19                10
33333            10                  15                  0

Any suggestions on making this happen? I have been able to make this work using a datagrid in VB but in Access I'm at a loss so any help would be appreciated.

Thanks,
Jessica
0
Comment
Question by:jessib
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 10785603
Hello jessib,

Sounds like the built in Crosstab query to me.
Start a new query and follow the crosstab query wizard.

Pete
0
 
LVL 11

Expert Comment

by:phileoca
ID: 10785628
Greeting jessica,
create a query, and put this in SQL, this will create what you want.   u can then call the query from a REPORT or form..  i suggest the REPORT:

TRANSFORM Sum(yourtable.QTY) AS SumOfQTY
SELECT yourtable.SKU
FROM yourtable
GROUP BY yourtable.SKU
PIVOT yourtable.Store;

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Jessica,
Replace 'YourTable' with yourtable name.
Sku = sku field
Store = store field
QTY = QTY field

KT
0
 

Author Comment

by:jessib
ID: 10785698
Thanks for both replies, I see I forgot to mention one thing in my first post. The reason I am wanting a grid format is so that my users can modify the QTY field. I have been able to get a crosstab query to appear as I want, but of course since it is a query I'm not able to modify any fields.

Any suggestions?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 11

Expert Comment

by:phileoca
ID: 10785796
just curious, what was your CODE in VB.
0
 

Author Comment

by:jessib
ID: 10785838
I didn't use this exact setup in vb but one similar...I was able to make the datagrid work using an ADO datasource:

doing so...

selected data from table
set the ado.recordsource = strSelect
           ado.refresh

then set the datagrid's datasource to the ADO

I tried something similar in access and was unable to get it work
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 10786087
Jessica, just a quick comment:

< but of course since it is a query I'm not able to modify any fields. >

Well, that isn't completely true. It all depends on the query. Crosstabs, usually (if not always) no. Some SELECT queries yes, while others no. I don't know the exact rules, but that's because If I was shown an SQL statement, I'd be able to tell if you can edit it or not. Anyways... ;)

Just popping in.

Andrew
0
 

Author Comment

by:jessib
ID: 10786153
Andrew,

Thanks.  This example from Phileoca gives me a crosstab which is the format I want it just doesn't allow editing of the QTY field.

TRANSFORM Sum(yourtable.QTY) AS SumOfQTY
SELECT yourtable.SKU
FROM yourtable
GROUP BY yourtable.SKU
PIVOT yourtable.Store;

You said that crosstabs, usually don't allow modifications, can you give me an example of when a crosstab may allow a modification?

Jessica
0
 
LVL 7

Accepted Solution

by:
donpricejr earned 500 total points
ID: 10786649
Here are the rules on updating query's in a Jet source:
-----------------------------
When can I update data from a query?
In some cases, you can edit data in query Datasheet view to change the data in the underlying table. In other cases, you can't. The following information shows whether a query's results can be updated, and if not, whether there is an alternative.

Data is updatable

You can update a query or query field in the following cases:

A query based on one table
A query based on tables with a one-to-one relationship
The query's results contain a Memo, Hyperlink, or OLE Object
Data is updatable under certain conditions

If a query is based on tables with a one-to-many relationship, you might not be able to edit the data for the following query fields.

Query field Solution
Join field from the "one" side Enable cascading updates between the two tables.
New records, if the "many" side join field doesn't appear in the datasheet Add the join field from the "many" side to your query to allow adding new records.
Join field from the "many" side, after you've updated data on the "one" side Save the record; then you'll be able to make changes to the "many" side join field.
Blank field from the table on the "one" side of a one-to-many relationship where an outer join exists Enter values in fields from the table on the "many" side, but only if the joined field from the "one" side contains a value for that record.
New records, if entire unique key of ODBC table isn't output Select all primary key fields of ODBC tables to allow inserts into them.

Data can be deleted but not updated

Query or query field Solution
Query (or underlying table) for which Update Data permission isn't granted To modify data, permissions must be assigned.
Query (or underlying table) for which Delete Data permission isn't granted To delete data, permissions must be assigned.  

Data can't be updated

Query or query field Solution
Query based on three or more tables in which there is a many-to-one-to-many relationship Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
Crosstab query  None
SQL pass-through query  None
Query that calculates a sum, average, count or other type of total on the values in a field, or an update query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.  
Union query  None
Query whose UniqueValues property is set to Yes  None
Query that includes a linked ODBC table with no unique index, or a Paradox table without a primary key  None
Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view You must join the tables properly in order to update them.
Calculated field  None
Field is read-only; the database was opened as read-only or is located on a read-only drive  None
Field in record that has been deleted or locked by another user A locked record should be updatable as soon as it is unlocked.
-------------------------

-Don
0
 

Author Comment

by:jessib
ID: 10805884
Thanks to all who answered, I was able to find a solution to my problem. Instead of using the data grid I have been able to place a msflexgrid on my form and create the layout that I wanted. I referenced MSKB Article 241355 to get this to work.
0
 

Expert Comment

by:madhavprabhu
ID: 11654562
Hello Jessica,
I have the same scenerio as yours, i.e. want an editable datagrid for displaying crosstab query, and on edit of field update the underlying table.
As you have suggested, i refered the MSKB Article 241355, but how did you tackle the events of mouse move, enter cell, leave cell and the dynamic moving of textbox.
This is because, ms acces forms does not support this events.
Kindly reply at the earliest,
Regards,
Madhav
0
 

Expert Comment

by:heddie
ID: 12321194
phileoca,

with regards to ur response..

TRANSFORM Sum(yourtable.QTY) AS SumOfQTY
SELECT yourtable.SKU
FROM yourtable
GROUP BY yourtable.SKU
PIVOT yourtable.Store;

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Jessica,
Replace 'YourTable' with yourtable name.
Sku = sku field
Store = store field
QTY = QTY field


how would u do this if I wanted a query to display the the results in a similar pattern.. meaning the query has the field I wanted to display as oppose to a table.

rgds,

ps... can u show me how I can get a parameter prompt for the query.

tks,
h'eddie


0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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