jessib
asked on
MS Access and DataGrid
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
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
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
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
ASKER
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?
Any suggestions?
just curious, what was your CODE in VB.
ASKER
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
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
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
< 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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
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
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
Sounds like the built in Crosstab query to me.
Start a new query and follow the crosstab query wizard.
Pete