Creating a Stacked Column Chart

[Lkwdmntr]

I think I came up with a solution. I could use code when the value is being entered on the form. When the client checks the box, I will run it through a Select Case statement. Not sure where I should put it. I'm thinking "AfterUpdate" or "LostFocus". I could also do it when I calculate all the points on the form. Either way, I will copy the values directly into the other table. I won't have to make any changes to the queries being used to collect the data for the charts.

I'm going to work on it tomorrow. I'll let you know how it works.

I think my next step after that will be how to call the individual actions for the entire 12 weeks and put it into a chart. I'm wanting to use a form version with a print button that will print a report version.

 

[Lkwdmntr]

So in my solution, I ran across a little clitch. It's should be very simple to most that monitor this site.

I am using a Select Case statement and am getting the error "Object variable or With block variable not set" Here's the code that is getting flagged.

Case 217
       If Me.ActivityChallengeYes = True Then
        dbs.Execute "UPDATE Weekly_Challenges " _
            & "SET MonACYes = True, MonACNo = False" _
            & "WHERE UserID =" & Form_FRM_Mon_Challenges.UserID _
            & " AND WeekNumber=" & Form_FRM_Mon_Challenges.WeekNumber _
            & "StandardAction='" & Me.StartTimeAction & "';"
        dbs.Close
       Else
        dbs.Execute "UPDATE Weekly_Challenges " _
            & "SET MonACYes = False" _
            & "WHERE UserID =" & Form_FRM_Mon_Challenges.UserID _
            & " AND WeekNumber=" & Form_FRM_Mon_Challenges.WeekNumber _
            & "StandardAction='" & Me.StartTimeAction & "';"
        dbs.Close
       End If
      Case 218

 

[isladogs]

Try replacing Form_ with Forms!

 

[Lkwdmntr]

Actually I had forgotten to include "Set dbs = CurrentDb".

Now I'm getting "Syntax error (missing operator) in query expression "UserId = 2 AND WeekNumber= 1 StandardAction= 'Exercise Routing".

I did try changing the Form_ to Forms!, no difference.

 

[Lkwdmntr]

Nevermind, I found it. I forgot one of the "AND"'s

 

[isladogs]

Missing AND before StandardAction

 

[Lkwdmntr]

Hi guys, I'm using my select case statement and I don't think the logic is right. I have a checkbox and when it is clicked this code kicks off.

Case 217
       If Me.ActivityChallengeYes = True Then
        dbs.Execute "UPDATE Weekly_Challenges " _
            & "SET MonACNo = True, MonACYes = False " _
            & "WHERE UserID =" & Form_FRM_Mon_Challenges.UserID _
            & " AND WeekNumber=" & Form_FRM_Mon_Challenges.WeekNumber _
            & " AND StandardAction='" & Me.StartTimeAction & "';"
        dbs.Close
       Else
        dbs.Execute "UPDATE Weekly_Challenges " _
            & "SET MonACNo = False " _
            & "WHERE UserID =" & Form_FRM_Mon_Challenges.UserID _
            & " AND WeekNumber=" & Form_FRM_Mon_Challenges.WeekNumber _
            & " AND StandardAction='" & Me.StartTimeAction & "';"
        dbs.Close
       End If
      Case 218

Sometimes the table reflects the change and others it doesn't. My brain is a little fried and thought some fresh eyes might see something that I'm not.

 

[Lkwdmntr]

Well, I finally got everything straightened out. I ditched the Case statements, realized I didn't need them. A simple If Statement took care of it.

If count > 0 Then

    If Me.ActivityChallengeNo = True Then
        dbs.Execute "UPDATE Weekly_Challenges " _
            & "SET MonACNo = True, MonACYes = False " _
            & "WHERE UserID =" & Form_FRM_Mon_Challenges.UserID _
            & " AND WeekNumber=" & Form_FRM_Mon_Challenges.WeekNumber _
            & " AND StandardAction='" & Me.StartTimeAction & "';"
        dbs.Close       
    Else
    End If
   
Else
End If

Now it's on to making the graphs. Here's the query I'm using and I sent the data in an excel file. I was able to get my cut down copy of my DB to under 2MB, but it's saying it's too large. Here's a google drive link if anyone is interested.

Google Drive Link

SELECT PlanAndActPointsUNION.UserID, PlanAndActPointsUNION.WeekNumber, PlanAndActPointsUNION.Action, ((PlanAndActPointsUNION.[PlannedMon])+(PlanAndActPointsUNION.[PlannedTue])+(PlanAndActPointsUNION.[PlannedWed])+(PlanAndActPointsUNION.[PlannedThu])+(PlanAndActPointsUNION.[PlannedFri])+(PlanAndActPointsUNION.[PlannedSat])+(PlanAndActPointsUNION.[PlannedSun])) AS Planned, ((PlanAndActPointsUNION.[ActualMon])+(PlanAndActPointsUNION.[ActualTue])+(PlanAndActPointsUNION.[ActualWed])+(PlanAndActPointsUNION.[ActualThu])+(PlanAndActPointsUNION.[ActualFri])+(PlanAndActPointsUNION.[ActualSat])+(PlanAndActPointsUNION.[ActualSun])) AS Actual
FROM PlanAndActPointsUNION
WHERE (((PlanAndActPointsUNION.UserID)=2) AND ((PlanAndActPointsUNION.WeekNumber)<13) AND (((([PlanAndActPointsUNION].[PlannedMon])+([PlanAndActPointsUNION].[PlannedTue])+([PlanAndActPointsUNION].[PlannedWed])+([PlanAndActPointsUNION].[PlannedThu])+([PlanAndActPointsUNION].[PlannedFri])+([PlanAndActPointsUNION].[PlannedSat])+([PlanAndActPointsUNION].[PlannedSun])))>0))
ORDER BY PlanAndActPointsUNION.UserID, PlanAndActPointsUNION.Index, PlanAndActPointsUNION.WeekNumber;

A big problem is how to pull the data one action at a time (for all 12 weeks), make the graph and put a button in the form to go to the next action. I would think a crosstab query would work, but you can't have two values under the weeks.