Solved

I need help with a grid control in Access

Posted on 2014-12-07
5
874 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 85
ID: 40485576
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
ID: 40485764
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
ID: 40485794
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 37

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 40487400
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 85

Accepted Solution

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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