Form for editing data in a flatfile format.

[clifftech]

I only get a chance to work with Access about once a year when I'm asked to develop a quick tool in Access. Obviously I don't have much of a chance to develop my Access skills so I need help with this latest Access tool.

I need an Access form for engineers to enter hours by month for the next 12 months. Since engineers work on different projects they need to enter monthly hours for each of their projects. The form would look like a simple Excel (flat file) format where there is a column for each month and a row for each project. I'm finding this not so easy with a relational database. I have developed a table for engineers "tblEngineer" (name, id, title, etc.), a table for projects "tblProjects" (project number, dates, etc.), and a table "tblMonthlyHrsWork" that handles the many-to-many relationship for the engineers and projects (engineers can work on many projects and projects have many engineers). To make it easy for engineers to enter or change their hours, I need to develop a form that looks like a crosstab query or pivottable query that shows the hours by month in columns, and project number by rows just like a spreadsheet. Unfortunately, the values in a crosstab or pivottable query cannot be edited.

I have considered other ways to structure the tables or considered using VB to query and edit data from within a form but nothing comes to mind within my skill level. Could someone provide some advice or point me to an article/forum that may help.

 

[dhookom]

You could use a form with record source of a temporary table that isn't normalized. When the user submits the values, you would need some type of code or queries to copy the values from the temporary table to the permanent, normalized table.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I do what Duane suggests, but it does require some more advanced Access vba skills. I have built a lot of "room reservation" or schedule databases that has a non normal table bound to the form that writes to a normalized table to store the data. The trick is that it also has to read the data out of the normal table into the non-normal table for display purposes. It is either that or go completely with an unbound form. The unbound form, may be a little more technically easier to understand but a whole lot more labor intensive. I will see if I have an example. But it does require some more advance VBA skills. I know of no native way to do this.

 

[clifftech]

Both of your responses confirm my thoughts but you have given me a direction for figuring this out. Any articles, examples, web sites you can point me to to get me started would be greatly appreciated. Looks like I'm going to learn VBA over the holidays! Thanks.

 

[MajP]

In truth if I had to do this, I would make the front end in vb.net and use access as the backend. The Datagrid in visual studio is so powerful and tailorable on the fly, that this would be relatively easy and you could put in all kinds of bells and whistles. However, that would assume you were proficient in .net. There is a flexgrid control that can be used in Access, but it is an active X control. You may look here for some very good examples.

http://www.rogersaccesslibrary.com/forum
then add this to the url
/forum21.html

(For some reason this url does not render correctly on this website)

Personally this control has kicked my butt. I can never get it to register and can never distribute it. You may try it. I have never understood why Access does not come with a native unbound data grid control. It would really simplify a lot of things.

The temp table idea is really a workaround for this shortcoming. Basically the temp table allows you to fake the grid control, by creating a continous form bound to it. Access is also very poor in allowing you to manipulate and build dynamic forms. So what you are asking is complicated to do with an unbound form since the number of projects and columns are variable. Somewhere I have an example of this using a temptable, and I will look for it. In fact I reference it here, but the link is no longer good
http://www.tek-tips.com/viewthread.cfm?qid=1649247

If you use the advance search feature on this site, and type
keyword: Grid
Handle: MajP
or
keyword: Spread Sheet Control
Handle: MajP

You will see that I seem to have discussed this idea a whole lot with various different ideas.

Here is one that uses the temp table idea, but it is pretty extreme. It is a room booking database, but there are multiple rooms and multiple rooms perpage. All the ideas are here
http://www.4shared.com/office/fSz6Pfqr/RoomBooking_Ver_30.html

 

[clifftech]

MajP, you've given me plenty of information that I need to digest which will take me awhile. Please send me anything else you think would help. Thanks.

 

[MajP]

http://www.4shared.com/file/nsOy0mfH/MajPSpreadSheetControl.html

It basically does everything that you want. This method is not technically very hard, but there are so many interconnected pieces that it makes it really hard to do. Having a grid control would make this a lot easier. If you have a limited amount of projects, I would think an unbound form would be conceptually easier to do.

Bottom line here are the big moving parts.
1) Basically you will have a normalized table, I called it tblData. And a non-normal table called tblGrid.
2) The form is bound to your grid table
3) As the form open you clear out the grid table, and read all the projects and stick them into the grid
4) You pick a person and the year and it reads the data table for that person and that year and sticks it into the grid table
5) After editing the grid form you write the data to the normal table
-Read all records in the grid
-See if that information exists in the data table
- if it does not exist in the data table add a new record
- if it does exist then edit the existing record

Load the form and pick the first employee and 2012. You will see it populate. Then write in the grid and hit save.

Const gridTable = "tblGrid"
Const dataTable = "tblEmployee_ProjectHours"
Const gridForm = "frmProjectHours"

Public Sub writeFromGrid()
  Dim rsGrid As DAO.Recordset
  Dim rsData As DAO.Recordset
  Dim frm As Access.Form
  Dim strMonth As String
  Dim strYear As String
  Dim employeeID As Integer
  Dim projectID As Integer
  Dim fieldNumber As Integer
  Dim existingRecordID As Integer
  Dim plannedHours As Double
  
  Set frm = Forms(gridForm)
  Set rsGrid = frm.RecordsetClone
  Set rsData = CurrentDb.OpenRecordset(dataTable, dbOpenDynaset)
  employeeID = Nz(frm.cmboEmployee, 0)
  strYear = Nz(frm.cmboYear, 0)
  Debug.Print "emp id" & employeeID & " year " & strYear
  If employeeID = 0 Or strYear = 0 Then Exit Sub
  rsGrid.MoveFirst
  Do While Not rsGrid.EOF
    projectID = rsGrid!ProjectID_FK
    For fieldNumber = 2 To 13
      strMonth = rsGrid.Fields(fieldNumber).Name
      plannedHours = Nz(rsGrid.Fields(fieldNumber).Value, 0)
      If plannedHours <> 0 Then
        existingRecordID = getExistingRecordID(employeeID, projectID, strYear, strMonth)
        If existingRecordID = 0 Then
          AddNewRecord employeeID, projectID, strYear, strMonth, plannedHours
        Else
          EditExistingRecord existingRecordID, plannedHours
        End If
      End If
    Next fieldNumber
    rsGrid.MoveNext
  Loop
End Sub
Public Function getExistingRecordID(employeeID As Integer, projectID As Integer, strYear As String, strMonth As String) As Integer
  Dim strWhere As String
  Dim strDate As String
  strDate = strDateFromMonthYear(strYear, strMonth)
  strWhere = "employeeID_FK = " & employeeID & " AND projectID_FK = " & projectID & " AND dtmDate = " & strDate
  getExistingRecordID = Nz(DLookup("projectHoursID", dataTable, strWhere), 0)
End Function
Public Function getMonthNumber(strMonthName As String) As Integer
  Select Case strMonthName
    Case "January"
      getMonthNumber = 1
    Case "February"
      getMonthNumber = 2
    Case "March"
      getMonthNumber = 3
    Case "April"
      getMonthNumber = 4
    Case "May"
      getMonthNumber = 5
    Case "June"
      getMonthNumber = 6
    Case "July"
      getMonthNumber = 7
    Case "August"
      getMonthNumber = 8
    Case "September"
      getMonthNumber = 9
    Case "October"
      getMonthNumber = 10
    Case "November"
      getMonthNumber = 11
    Case "December"
      getMonthNumber = 12
  End Select
End Function

Public Sub AddNewRecord(employeeID As Integer, projectID As Integer, strYear As String, strMonth As String, plannedHours As Double)
  Dim strSql As String
  Dim strDate As String
  
  strDate = strDateFromMonthYear(strYear, strMonth)
  strSql = "INSERT INTO " & dataTable & " (employeeID_FK, projectID_FK, PlannedHours, dtmDate) "
  strSql = strSql & "VALUES (" & employeeID & ", " & projectID & ", " & plannedHours & ", " & strDate & ")"
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

Public Sub EditExistingRecord(existingID As Integer, plannedHours As Double)
  Dim strSql As String
 
  strSql = "UPDATE " & dataTable & " SET PlannedHours = " & plannedHours & " WHERE projectHoursID = " & existingID
  CurrentDb.Execute strSql
End Sub

Public Function strDateFromMonthYear(strYear As String, strMonth As String) As String
  Dim monthNumber As Integer
  Dim strMonthNumber As String
  monthNumber = getMonthNumber(strMonth)
  strMonthNumber = Format(monthNumber, "00")
  strDateFromMonthYear = "#" & strYear & "/" & strMonthNumber & "/01#"
End Function
Public Sub ClearGrid()
  Dim strSql As String
  strSql = "DELETE * from " & gridTable
  CurrentDb.Execute strSql
End Sub
Public Sub LoadProjects()
  Dim strSql As String
  strSql = "INSERT INTO tblGrid ( ProjectID_FK, projectName ) SELECT tblProjects.projectID, tblProjects.projectName FROM tblProjects"
  CurrentDb.Execute strSql
End Sub
Public Sub LoadEmployeeHours()
  Dim rsData As DAO.Recordset
  Dim strSql As String
  Dim strMonth As String
  Dim employeeID As Integer
  Dim projectID As Integer
  Dim plannedHours As Double
  Dim frm As Access.Form
  Dim strYear As String
  
  Set frm = Forms(gridForm)
  employeeID = Nz(frm.cmboEmployee, 0)
  strYear = Nz(frm.cmboYear, 0)
  If employeeID = 0 Or strYear = 0 Then Exit Sub
  strSql = "Select * from " & dataTable & " where EmployeeID_FK = " & employeeID & " AND year(dtmDate) = " & CInt(strYear)
  Set rsData = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  Do While Not rsData.EOF
    strMonth = Format(rsData!dtmDate, "MMMM")
    plannedHours = rsData!plannedHours
    projectID = rsData!ProjectID_FK
    strSql = "UPDATE " & gridTable & " SET " & strMonth & " = " & plannedHours & " WHERE projectID_FK = " & projectID
    CurrentDb.Execute strSql
    rsData.MoveNext
  Loop
 End Sub

 

[clifftech]

MajP, This is very very close to the tool I need. A couple of changes will be needed. First, engineers could have as many as 20 projects that they are working on which means there could be up to 20 rows. Also there are about 250 possible projects in our organization so the Project Name text box needs to be a combo box to allow the engineers to choose which projects they will be working on. The other change is the 12 months needs to be a rolling 12 months - for example if engineers are filling out the form in June 2012, the form needs to have columns for July 2012 through June 2013. Having a Year combo box really isn't necessary but it may be helpful to have a combo box that lets the engineers pick the first of the 12 months they need to complete. For example, the engineer picks June 2012 then the first column is June 2012 and the next 11 months are automatically showing on the form.

I need to study your code to see if and how to incorporate these changes. Please let me know if you think I would face any major problems. Thanks.

 

[MajP]

This is all doable

First, engineers could have as many as 20 projects that they are working on which means there could be up to 20 rows. Also there are about 250 possible projects in our organization so the Project Name text box needs to be a combo box to allow the engineers to choose which projects they will be working on.

My demo was a little simpler in that it assumed that all users would see all projects. So when it loads the "grid" it simply reads all the projects. So now you would have to do some modification.
1) When it loads the grid instead of inserting all the projects from the projects table you would insert only the projects that the employee already has in the data table within the date range. That just requires a change to the sql insert statement
2) Conceptually it is easier to think of this form as unbound, in that the data has to be read and written from it to a table. So to select a new project, I do not think I would have a combo on the grid form. It might be doable, but there are a lot of events and validation that have to take place. It could get pretty complicated and confusing. Currently that field is locked and loaded when the form loads. I would instead have a button to add a new project. That would pop open a dialog form. On the pop up you could design it several ways to allow the user to easily pick projects and remove projects. I personally like to use "to from" list boxes or a listview with checkboxes. But you could simply have a continous form with a combo. Then when you close the pop up form you would load and remove projects from the grid based on the selection.

The other change is the 12 months needs to be a rolling 12 months - for example if engineers are filling out the form in June 2012, the form needs to have columns for July 2012 through June 2013. Having a Year combo box really isn't necessary but it may be helpful to have a combo box that lets the engineers pick the first of the 12 months they need to complete. For example, the engineer picks June 2012 then the first column is June 2012 and the next 11 months are automatically showing on the form.

1) I would first change the grid fields from named months. My fields would be M0, M1, M2
2) You would then need a way to select a year and month on the main form. You would need to define year if not this whole thing would only be useable for the current year forward.
3) When you select a year month you then dynamically change the label captions. So simply determine the selected month (base month). Then the label for M0 is the base month, M1 is the base month + 1, ....
4) Now when you write from the grid, you need to determine what month that column represents. If you dynamically loaded you labels correctly this is trivial
change
strMonth = strMonth = rsGrid.Fields(fieldNumber).Name
to something like
determine the month number for the base month. So July is 7. The fields (columns) that represent months in my grid are fields (2-13). This could be different if you put other columns in your grid. So my column M0 represents July and its the 2nd field index in the grid. M1 represents August and it is the 3rd field index.
So the fieldnumber - 2 + baseMonthnumber is the month number for that column. Until it goes over 12
dim columnMonthNumber
...
columnMonthNumber = fieldNumber - 2 + baseMonthNumber
if columnMonthNumber > 12 then columnMonthNumber = columnMonthNumber - 12
strMonth = format("2012/" & columnMonthNumber & "/01","mmmm")


So as I said this just gets convoluted. On another note I played with this in VB.net using the database as the datasource. .Net makes this so much easier and cleaner. The datagrid allows you to bind some columns and have some unbound columns. So the selected projects can be bound to a query. The other columns can be unbound. You can build the grid on the fly. Wish Access had something similar. I tried to load the Active X flexgrid. I can run dbs with flexgrids, but I cannot get around the license to add one.