I need help with a grid control in Access

Good morning Experts,
I need your assistance using a Grid Control in an Access 2013 application.  This application will be used to schedule employees to a given job.

The grid must be able to do the following functionality:
1)  I need to be able to dynamically generate the columns in the according to the job dates.
In my example (see image), I have a job that runs for 3 days (from 12/12/2014 to 12/14/2014).  
In this case, the first column will always be the 'EMPLOYEE' column, but the columns that follow should be labeled after each of the job dates (i.e. the second column should be labeled  (12-12), the third column should be labeled  (12-13),  and the fourth column should be labeled (12-14).

2) I want to be able to select/high-light various cells in the grid, and schedule my employees to work a job only for the selected/high-lighted days.

I have included an image to illustrate what it is I want to accomplish.Grid SampleNOTE:  I used Excel to simulate the grid.

How can I accomplish this?
Is there a grid control in Access 2013 that I can use for this?  
Can I use a subform to achieve this functionality?
Do I need to use a third party grid control for this? If so, can you recommend one?

Thank you very much in advance,
mrotor
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access does not include a grid control, and there are very few that actually work in Access. In many cases, you can do what is needed by creating a Form in either Datasheet or Continuous view, but you won't have the "select or highlight a cell" feature.

BennetTec has the TList contol (http://www.bennet-tec.com/btProducts/TList/TList.htm). It works with Access and is very customizable. You could very likely get something like this to do much of what you want. Most 3rd party grids have cell-level events, which allow you to determine when a user doubleclicks on a specific cell, so you could respond by "highlighting" that cell in some manner. I've tested this grid in Access, and it seems to work as advertised. I've never deployed it to end users, however, but it seems to work well enough in my testing.

There are other Grids that work with Access too, I believe:

http://10tec.com/articles/ms-access-grid-control.aspx
http://www.exontrol.com/exgrid.jsp
http://www.dbi-tech.com/ComponentPage_ctGrid.aspx

I've played around with the dbi-tech grid, and it seems to do pretty decent job. I've not used the others.
0
mainrotorAuthor Commented:
Hi Scott,
Thank you very much for your reply and providing the links to the 3rd party control.

What do you mean by:
"... you can do what is needed by creating a Form in either Datasheet or Continuous view ..."

Are you referring to changing the column header names to match the job dates?

mrotor
0
mainrotorAuthor Commented:
Scott and other Experts,
Would it be possible to embed Excel in my Access application?  
Would I be able to load the spreadsheet with the data from my Access tables?
Would I be able to programmatically  format the spreadsheet to look like the grid in the image i posted in my original question?
Also would I be able to programmatically save the selected/high lighted information from Excel into the Access tables?

thanks,
mrotor
0
PatHartmanCommented:
Some time ago I had a need to create a spreadsheet-like interface for data entry.  The problem of course is that Access is based on relational database theory and that is very different from the flat-file organization of a spreadsheet.  But, users do love their grids.  The attached database shows two different examples  One uses months and the other uses expense types.  

The point though is that the underlying data is kept in a properly normalized table and the "pivot" is done with a series of queries and requires only a couple of lines of code for each control on the form.  The "columns" are fixed and so must be known ahead of time so this solution works best for things like time keeping where you want to have a week's worth of columns for data entry or forecasting where you want to see a year at a time.

I've included a power point presentation I used to demonstrate the functionality.  You are free to copy the code and make use of the concept.  All I ask is that if you distribute the files to anyone else, you attribute me and do NOT modify them at all.  Just send the zip file exactly as it was when you received it.

I cannot upload the file since it is too large for EE.  It is about 2M so it can be emailed.  If you want it, send me a PM with your email address and I will send it to you.  
Bound Denormalized Form in datasheet view Bound Denormalized Form in design viewThe following is the code from three of the twelve fields.  As you can see, it is customized for each month.
Private Sub EstJulAmt_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.EstJulAmt.OldValue) Then
        Me.EstJulDT = "7/1/" & Me.Parent!cboYearFilter
        Me.EstJulType = Me.cboEstJanType
    End If
    Me.JulUpdateDT = Now()
End Sub

Private Sub EstJunAmt_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.EstJunAmt.OldValue) Then
        Me.EstJunDT = "6/1/" & Me.Parent!cboYearFilter
        Me.EstJunType = Me.cboEstJanType
    End If
    Me.JunUpdateDT = Now()
End Sub

Private Sub EstMarAmt_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.EstMarAmt.OldValue) Then
        Me.EstMarDT = "3/1/" & Me.Parent!cboYearFilter
        Me.EstMarType = Me.cboEstJanType
    End If
    Me.MarUpdateDT = Now()
End Sub

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What do you mean by:
"... you can do what is needed by creating a Form in either Datasheet or Continuous view ..."
I wrote "in many cases you can do what is needed", with my point being that using a Form or Subform in Datasheet or Continuous View, you can simulate many of the features of a grid. However, the highlighting and such you require can't really be done effectively.

Would it be possible to embed Excel in my Access application?
 You can use an OLE Bound Object control and embed an Excel workbook in Access.

Would I be able to load the spreadsheet with the data from my Access tables?
There are a variety of ways to do that. The simplest is to link the two items, so where data changes in your Access tables you'd see that in the workbook

Would I be able to programmatically  format the spreadsheet to look like the grid in the image i posted in my original question?Also would I be able to programmatically save the selected/high lighted information from Excel into the Access tables?
You can work with any Excel workbook using Automation, so you should be able to do that. Again, the trick is going to be determining when the user "highlights" a cell. I don't work with Excel enough to know if that's possible, but it may be.

In many cases, there's a compromise of what we'd like to do versus what we end up doing. While it'd be nice to have the "highlight a cell feature", you may not be able to do that ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.