Solved

I need help with a grid control in Access

Posted on 2014-12-07
5
715 Views
Last Modified: 2014-12-09
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
0
Comment
Question by:mainrotor
  • 2
  • 2
5 Comments
 
LVL 84
Comment Utility
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
 

Author Comment

by:mainrotor
Comment Utility
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
 

Author Comment

by:mainrotor
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
Comment Utility
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now