How to fill in report matrix

[jsmath22]

Hello and thanks for the help!!

I am a pretty good VBA programmer for Excel but am a novice in Access. I am creating a database that employees can use to forecast how many hours they are going to be working on a project. In a form (frmSelectPerson) they use a combo box to select who they are and a date picker to choose the date. I then open a report (rptWrkEntry) and have that filtered to only their projects. Now, here is where I kind of get lost due to the dynamic nature of Access Reports. I have the Page Header as the date (start date is chosen on the previous form and I go 10 more weeks out) and that is working just fine. Then, I have the projects that person is working on as the rows, which could be 1 or 20. So now we are looking at a matrix, rows are Projects, columns are dates, and now I need to fill in the middle with hours, if they have been entered (if so, they have the option to update them and if not then leave it blank). Now, when you are looking at the report in Design View, there is only one row and the "middle" text boxes are called "txtHours1" to "txtHours10" but once the report is populated there could be three rows, so how to grab these text boxes in VBA IDK. Here is what I am looking for:

for I=0 to me.controls.count
set c=me.controls(I)
if instr(1, c.name, "Hours") then
'grab the associated project name so what row am I in
'grab the associated date, so what column I am in
'go to the Update table and see if that date/Project combo already has hours entered and, if so, populate txtHours
'if not leave txtHours empty
end if
next I

Right now I have this just with debug.print c.name to see but all it is giving me is the 10 I created. However, this is upon Report_Load, is there another event I should be using?


Holy macaroni, does that make sense at all?

Thanks for reading my rambling explanation!!
Me

 

[Micron]

I need to fill in the middle with hours, if they have been entered (if so, they have the option to update them and if not then leave it blank).

In my opinion, you are using the wrong type of object. Reports are for displaying existing data, not for entering or editing/updating data. You should be using a form, not a report.

 

[jsmath22]

Re: How to fill in report matrix/Now a form

Agreed, I now have a form with the projects listed (query from tblProject which is the Record Source for the form) as the rows. I have the desired dates being populated (using code to do that) where the text boxes for the dates are now in the Header of the form (columns). Now I need to query tblUpdate to get the hours and leave it blank if the user hasn't entered hours for that Project/Week combination. What is the best way forward here? I can create an array using VBA but then how do I use that to populate the form? Can I make a query for each of the dates (that is a variable I set in the code) and then some how populate the columns? Any ideas are welcomed!

Thank You,
Me

 

[HiTechCoach]

Re: How to fill in report matrix/Now a form

TIP: Data entry forms tend to match the talbe structure a lots close while reports rarely do.

The way you enter data does not have to match the way it is reported (comes out).

Your data can be reported in a matrix with a report using a crosstab query.

What you are trying to do is not generally the way data is entered into a database.

It is possible to have the data entry forms be a lot different than the table structure if you are will to do a lot of work.

There has been times when the extra programming effort really saved a lot of data entry time in the long run. In those few cases I have done something similar to what you want using two methods to flattens the data into a de-normalized spreadsheet. Turning records (columns) in a single row.

NOTE: These method do require a fair amount of VBA coding with recordsets.

1) Using a temp/word table that flattens the data into a row from many records. Use VBA code to loop through the records and in the temp table and write out to the properly normalized table.


2) Data grid (ActiveX) control.

Data is entered into a data grid (spreadsheet like) control. VBA code is used to read through the grid and save the data into records.

 

[Micron]

Re: How to fill in report matrix/Now a form

Maybe I'm missing something, but it seems you are over-complicating things. Why isn't your form's recordsource based on a query that includes tblUpdate as joined table, and the date controls arranged across the top as you wish? If multiple records need to be displayed at the same time, use a continuous form. Or is this a situation where you're trying to store/retrieve data from table rows and display it in columns?

 

[jsmath22]

Re: How to fill in report matrix/Now a form

HiTech Coach: Awesome, thank you for making me aware of the data grid option, I will look into that. I am a big programmer so that should be fun.

Micron: I did the query, it's a crosstab, here's the SQL for it:
TRANSFORM Sum(tblUpdate.Hours) AS SumOfHours
SELECT [LastName] & ", " & [FirstName] AS Name, tblProject.Project, tblProject.Details
FROM (tblPerson INNER JOIN tblProject ON tblPerson.ID = tblProject.Owner) INNER JOIN tblUpdate ON (tblProject.ID = tblUpdate.Project) AND (tblPerson.ID = tblUpdate.[Owner])
WHERE (((tblPerson.ID)=4) AND ((tblUpdate.UpdateDate)>=#8/31/2015# And (tblUpdate.UpdateDate)<=#9/28/2015#))
GROUP BY [LastName] & ", " & [FirstName], tblProject.Project, tblProject.Details
PIVOT tblUpdate.UpdateDate;

and then I went to the Create tab, select More Forms->Multiple Items and it created the exact form I need, super sweet. However, when I change the date in the query, it doesn't update the form's dates, it just give #Name? error for the dates that appeared on the original form that are no longer included in the date range of the query. Example: I created the from while the query was set to >=8/31/2015 and <=9/28/2015 I then changed the dates to 8/24 and 9/21 which looks wonderful in datasheet view but when I open the form, 8/31-9/28 is still showing with the 9/28 entry being the #Name? error. Oh, dear, what do I do now?

How can I do this "date control" thing across the top? I look that up on google and it just gives me the date picker Active X control. I can't have the user picking what dates. The update date must be a Monday for the future graphs and metrics that I will be doing.

Thanks for the help!!
Me

 

[Micron]

Re: How to fill in report matrix/Now a form

I'm having trouble following - there are gaps in the info. It's common for a poster to overlook minor details, but they are significant when you know virtually nothing of what's going on.

when I change the date in the query, it doesn't update the form's dates,

If you simply change the query date, the form will not reflect the change, even if you close it and re-open it. The query changes need to be saved. If that's not the issue
I then changed the dates to 8/24 and 9/21 I'm forced to choose whether you mean exactly that or 8/24/2015. Which should I focus on? See my point?
I'm not sure I understand the need for a crosstab query, and have scoured the posts here to try to figure out what "date control" thing means.
Maybe you'd be interested in uploading a copy of your db somewhere and I can take a look at it. If you prefer, you can pm me the link or discuss the idea. I'd rather cut to the chase and avoid ambiguity to save time as I've got a major woodworking project on the go.

 

[jsmath22]

Re: How to fill in report matrix/Now a form

You guys are awesome and really led me in the right direction. I finally found the answer. I am not sure the crosstab query was necessary but it provided the data formatting that I needed, Project as the row, date as the column, and hours in the Sum portion. The user chooses the start date on a previous form. Once they chose that date, I was able to change the query in the code but then the associated form wouldn't change the column headings to match the date filter in the query. So here is the format of the form:

8/31/2015 9/7/2015 9/14/2015 9/21/2015 9/28/2015
Project1 Project1 Description Hrs Hrs Hrs Hrs Hrs
Project2 Project2 Description Hrs Hrs Hrs Hrs Hrs

Then, when the user changed the dates (8/24/2015 to 9/21/2015), I changed the query in the code but when the associated form displayed:

8/31/2015 9/7/2015 9/14/2015 9/21/2015 9/28/2015
Project1 Project1 Description Hrs Hrs Hrs Hrs #NAME?
Project2 Project2 Description Hrs Hrs Hrs Hrs #NAME?

So I found this:
Create an Access form tied to a cross-tab query - ICB Consulting, Inc. Kbase

which says to update the query, which I had done, then I changed that code provided by the webpage to match what I am doing:

Private Sub Form_Load()
Dim rs As DAO.Recordset
Dim i

Set rs = Me.Form.RecordsetClone
Me("Header").Caption = Form_frmSelectPerson.sz_name & " Project Hours" 'just a label

For i = 2 To rs.Fields.Count - 1
'First 2 fields are Project and Description
Me("txtHours" & i - 1).ControlSource = rs.Fields(i).Name 'I named the text boxes and label boxes, the webpage code just left them as the default name
Me("lblDate" & i - 1).Caption = rs.Fields(i).Name
Next i
End Sub

I had no idea that a .controlsource could be a .name, this is like the sky is purple to me, haha!

Thank you for all your help, hopefully this will help someone trying to do the same thing.

Later Dayz,
Me

 

[Micron]

Re: How to fill in report matrix/Now a form

As far as the displayed date being wrong when you changed the date, now I get it. Not enough info before (for me at least).
it doesn't update the form's dates You were referring to the date being displayed at the top of your form 'column', not simply the dates that the query was returning.
I didn't clue in that the issue was dynamic field names with a crosstabe query - I have seen that before. Sorry.
Glad you solved it.

 

[HiTechCoach]

Re: How to fill in report matrix/Now a form

If you want a dynamic updating column headers based on a crosstab query then check out my example:

Dynamic Report based on a Crosstab query