Dynamic cross-tab (and report)

[Gflanagan]

I got help with a dynamic report a few months ago. I have attached a picture of the report. It is a five month report. Recently a Department Head asked if I could should the comments "only for the most recent month" on the five month report. I believe she made this request for the report to be more ledgible. Her request is contradicting because the report is based on a date range. Is there anything I am over looking that could make this happen? I can send part of the database if I need to.

[Gflanagan]

I forgot to upload the attahment.

I got help with a dynamic report a few months ago. I have attached a picture of the report. It is a five month report. Recently a Department Head asked if I could should the comments "only for the most recent month" on the five month report. I believe she made this request for the report to be more ledgible. Her request is contradicting because the report is based on a date range. Is there anything I am over looking that could make this happen? I can send part of the database if I need to.

[HansV]

You could restrict the date range to the last month within the selected range. Would that do what the department head wants?

[Gflanagan]

I will try that and then message back after lunch :)
I attached a picture of my date range form

[Gflanagan]

If I could select 2 date ranges for the output Report it would work. I could select a date range for comments (which would be for the most recent month) and I would use the other date range that is there. I guess I would have to set that up in the code if it is possible to do.

[HansV]

Try this: in the Dynamic query, change Comments in the query grid to

Comments: IIf([ForTheMonth]>DateAdd("m",-1,[Forms]![Dynamic]![txtEndDate]),[ScoreCards].[Comments],Null)

This will hide the comment if it is from a month or more before txtEndDate.

[Gflanagan]

ok great :) I will try it thank you :)

[Gflanagan]

Hi
The comments I tryed changing comments from group to last, I think it sort of worked. I have this other problem, I have to work on correcting for a meeting on Friday.


This not a question about dynamic reports. I searched the forum for it but could not find anything. This is however for my dynamic report.

I orignally had this set up where users had to enter a decimal value they would prefer using percentages except my if then statement does not recognize when they enter 100%

This is not working for 100% when I put 100.00 it automatically changes to 100# and then it doesn't recognize that the user entered a 100% is there any way around this or a way to do this?

'>100 percent
Select Case Me.Txtdecimal
Case Is >= 100
Select Case Me.ComboGoals.Column(2)
Case 9, 45, 151, 169, 188, 199, 207, 55
Me.txtMetGoal = "Met Goal"
End Select
End Select

[HansV]

If you want users to be able to enter a number as 30% or 100%, set the Format property of the text box to the custom format 0%. Access should then recognize 30% as 0.3 and 100% as 1.
As a consequence, you should test for

Case Is >= 1

since 100% is equivalent to 1.

[Gflanagan]

ok thank yo so much :)

[Gflanagan]

When the user enter the data for goal #1 and hit the command button (add) I need to for the form (combobox box) to automatically go to Goal #2

I go it to almost work, it does go to Goal#2, with the following code but it will not go to 3
Me.ComboGoals = Me.ComboGoals.ItemData(0) + 1

It automatically went to goal#2 but after hit the command button (add) it did not go to 3

I pasted the big code below

Code: Select all

'Add data to form
CurrentDb.Execute "INSERT INTO Scorecards (ForTheMonth,Operations,FiscalYear,Groups,Department,Contact,Goals,Code,Benchmarks,Results,Comments,MetGoal)" & _
"VALUES('" & Me.TxtDate & "','" & Me.TxtOperations & "','" & ComboFiscal & "','" & Me.ComboDepartment & "','" & Me.txtDepartment & "','" & Me.txtContact & "','" & Me.TxtCode & "','" & Me.ComboGoals & "','" & Me.txtBenchmarks & "','" & Me.TxtActualBenchmark & "','" & Me.txtComments & "','" & Me.txtMetGoal & "');", dbFailOnError

  
'Requery Form
frmscorecardssub.Form.Requery

'Clear Form

Me.txtBenchmarks = ""

Me.Txtdecimal = ""
Me.Txtnumber = ""
Me.Comboyesorno = ""

Me.txtComments = ""
Me.ComboDepartment.SetFocus
Me.txtMetGoal = ""
Me.ComboGoals = Me.ComboGoals.ItemData(0) + 1

Me.txtBenchmarks.Value = Me.ComboGoals.Column(2)
Me.TxtCode.Value = Me.ComboGoals.Column(1)
Me.TxtOperations.Value = Me.ComboGoals.Column(3)
Me.TxtDate.Value = DateSerial(Me.Cboyear.DefaultValue, Me.ComboMonth, Me.CboDay.DefaultValue)
Me.txtComments.Value = DateSerial(Me.Cboyear.DefaultValue, Me.ComboMonth, Me.CboDay.DefaultValue)

  Select Case Me.ComboGoals.Column(0)
  Case 7, 8, 14, 15, 16, 17, 18, 19, 21, 26, 33, 39, 40, 41, 42, 44, 48, 49, 52, 53, 57, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 76, 77, 78, 81, 82, 83, 92, 93, 94, 95, 98, 101, 102, 104, 105, 106, 107, 109, 110, 111, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 137, 138, 140, 141, 142, 154, 155, 157, 159, 160, 170, 174, 175, 176, 177, 179, 180, 181, 183, 190, 198, 202, 203, 206, 212, 215, 217, 218, 220, 221, 222, 223, 224, 225, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 243, 244, 245, 247, 248, 249
  Me.Comboyesorno.Visible = True
  Me.Txtnumber.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = False
  Me.TxtActualBenchmark.Enabled = False
  Me.Label32.Visible = False
  Case 1, 4, 6, 10, 11, 12, 13, 22, 23, 24, 25, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 46, 47, 50, 51, 56, 59, 61, 74, 75, 79, 80, 84, 85, 86, 87, 88, 89, 90, 91, 96, 99, 100, 108, 110, 112, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 139, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 153, 156, 158, 160, 161, 162, 163, 164, 165, 166, 168, 171, 172, 173, 174, 184, 185, 186, 187, 192, 193, 195, 196, 197, 200, 204, 205, 208, 209, 210, 211, 213, 214, 216, 219, 226, 246, 240, 241, 242
  Me.Txtnumber.Visible = True
  Me.Comboyesorno.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = False
  Me.TxtActualBenchmark.Enabled = False
  Me.Label32.Visible = False
  Case 2, 3, 5, 9, 20, 43, 45, 54, 55, 58, 60, 97, 103, 151, 152, 167, 168, 169, 182, 188, 189, 191, 199, 201, 207
  Me.Txtdecimal.Visible = True
  Me.Txtnumber.Visible = False
  Me.Comboyesorno.Visible = False
  Me.TxtActualBenchmark.Visible = False
  Me.TxtActualBenchmark.Enabled = False
  Me.Label32.Visible = False
  Case Else
  Me.Txtnumber.Visible = False
  Me.Comboyesorno.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = True
    Me.Label32.Visible = True
    Me.TxtActualBenchmark.Enabled = True
 
  End Select
  
  
  Select Case Me.ComboGoals.Column(0)
  Case 119
  Select Case Me.ComboMonth
  Case "4", "8", "12"
  Me.Comboyesorno.Visible = True
  Me.Txtnumber.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = False
  Me.TxtActualBenchmark.Enabled = False
  Me.Label32.Visible = False
  Case Else
  Me.Txtnumber.Visible = False
  Me.Comboyesorno.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = True
    Me.Label32.Visible = True
    Me.TxtActualBenchmark.Enabled = True
    Me.TxtActualBenchmark = "N/A"
    End Select
    End Select
    
    Select Case Me.ComboGoals.Column(0)
  Case 72, 73, 76, 77, 78, 52
  Select Case Me.ComboMonth
  Case "1"
  Me.Comboyesorno.Visible = True
  Me.Txtnumber.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = False
  Me.TxtActualBenchmark.Enabled = False
  Me.Label32.Visible = False
  Case Else
  Me.Txtnumber.Visible = False
  Me.Comboyesorno.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = True
    Me.Label32.Visible = True
    Me.TxtActualBenchmark.Enabled = True
    Me.TxtActualBenchmark = "N/A"
    End Select
    End Select
  
  Select Case Me.ComboGoals.Column(0)
  Case 239
   Me.Txtnumber.Visible = False
  Me.Comboyesorno.Visible = False
  Me.Txtdecimal.Visible = False
  Me.TxtActualBenchmark.Visible = False
    Me.Label32.Visible = False
    Me.TxtActualBenchmark.Enabled = False
    MsgBox ("Blank do not enter results!"), vbOKCancel                          ' Tell user
      Me.txtContact.SetFocus
  End Select
 
   

End Sub



Private Sub CmdClear_Click()



Me.ComboGoals = ""
Me.txtBenchmarks = ""
Me.txtContact = ""
Me.ComboDepartment = ""
Me.Txtdecimal = ""
Me.Txtnumber = ""
Me.Comboyesorno = ""
Me.TxtActualBenchmark = ""
Me.txtComments = ""
Me.ComboDepartment.SetFocus
Me.txtMetGoal = ""
Me.TxtCode = ""



End Sub

Private Sub CmdClose_Click()
Application.Quit
End Sub





Private Sub CmdReport_Click()

On Error GoTo CmdReport_Click_Err
Select Case Me.ComboSummary
Case "Child Nutrition"
    DoCmd.OutputTo acOutputReport, "Group Child Nutrition Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "Purchasing"
DoCmd.OutputTo acOutputReport, "Group Purchasing Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "Business Office"
DoCmd.OutputTo acOutputReport, "Department Business Office Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "Technology"
DoCmd.OutputTo acOutputReport, "Department Technology Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "Payroll"
DoCmd.OutputTo acOutputReport, "Group Payroll Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "Accounts Payable"
DoCmd.OutputTo acOutputReport, "Group Accounts Payable Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "Comptroller"
DoCmd.OutputTo acOutputReport, "Group Comptroller Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "General Accounting"
DoCmd.OutputTo acOutputReport, "Group General Accounting Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select

Select Case Me.ComboSummary
Case "Transportation"
DoCmd.OutputTo acOutputReport, "Group Transportation Three Month Report", acFormatePDF, "", False, "", 0, acExportQualityPrint
End Select
CmdReport_Click_Exit:
    Exit Sub
    
CmdReport_Click_Err:
    MsgBox Error$
    Resume CmdReport_Click_Exit

[HansV]

Try changing

Me.ComboGoals = Me.ComboGoals.ItemData(0) + 1

to

Me.ComboGoals = Me.ComboGoals + 1

[Gflanagan]

Thank you :)
I tried this and it worked!!!!!!
Me.ComboGoals.Value = Me.ComboGoals.Value + 1

I may try to the other too. i really appreciate all of your help so much Thank you :)

[Gflanagan]

I think I read your post too fast I was so excited the code worked. I didn't realize it was the same.

All of my goals are numbered. For the Purchasng Department the goals are 110-122 then I added a goal and it is 243

So I want to do something like: If me.combogoals.value =122 then me.combogoals = 243 (maybe after update or soething) I will do some tests

[HansV]

How about

Me.ComboGoals.Value = Me.ComboGoals.ItemData(Me.ComboGoals.ListIndex + 1)

[Gflanagan]

I was testing out my form as the user and it worked great I put entries for 1-27 for one user. The combo box automatically went to the next goal in line after each entry. When I got to the last goal for that user, it entered the information but it gave me a runtime error saying I used the list.index improperly. Is there a way to code it for this not too happen? I searched a little, I realize there has to be a way with a conditional statement.


This did not work
If Me.cboName.ListIndex = -1 Then
MsgBox "A value is required for ...."
Me.cboName.SetFocus
Cancel = True
End If

I also found this: (I may try it next)
If IsNull(Me.cboName) Then
MsgBox "A value is required for ..."
Me.cboName.SetFocus
Cancel = True
End If

[HansV]

Try replacing

Code: Select all

    Me.ComboGoals.Value = Me.ComboGoals.ItemData(Me.ComboGoals.ListIndex + 1)

with

Code: Select all

    With Me.ComboGoals
        If .ListIndex < .ListCount - 1 Then
            .Value = .ItemData(.ListIndex + 1)
        Else
            MsgBox "There is no next item...", vbInformation
        End If
    End With

[Gflanagan]

If that doesn't work is there a way to handle the error with resume?


Me.ComboGoals.ListIndex = Me.ComboGoals.ListIndex + 1
If ComboGoals.ListIndex < ComboGoals.ListCount - 1 Then
ComboGoals.Value = ComboGoals.ItemData(ComboGoals.ListIndex + 1)
Else
MsgBox "There is no next item...", vbInformation
End If

[Gflanagan]

It works now Hooray!!! I changed the first line I may not need the if then statement I'm not sure.

Me.ComboGoals.Value = Me.ComboGoals.ItemData(ComboGoals.ListIndex + 1)
If Me.ComboGoals.ListIndex < Me.ComboGoals.ListCount - 1 Then
Me.ComboGoals.Value = Me.ComboGoals.ItemData(ComboGoals.ListIndex + 1)
Else
MsgBox "There is no next item...", vbInformation
End If

[HansV]

I'd remove the first line.

(I accidentally omitted the line End With in my previous reply; I have now added it)