Help with Option Group To select different Years Of Data

[Seanter]

Hello,




I have created a search form containing combo boxes and command buttons and would like add an option group to the form.

On my search form, the user must select a department (deptcombo), and then must select a supervisor (supcombo), with the option of selecting an employee (empcombo), before they can click on the button that opens the required form. I would like to add in an option group where, after selecting the department and supervisor, the user then selects the data year (2011, 2012, etc.) and from there clicks on a command button to pull up the appropriate form containing data for the year selected. I have included the field DataYear to all my tables and queries and need to find a way to separate the years when pulling the reports/forms. (I am very new to vba, and have been trying for almost a week to find the right code, with no luck.)

The code I have for one of the command buttons is as follows: (this command button pulls the previous 6 months of data; I need to separate the years somehow)

Private Sub CmdPrev6Mths_Click()
On Error GoTo CmdPrev6Mths_Click_Err

If Forms!Switchboard!DeptCombo & ""= "" And [Forms]![Switchboard]![SupCombo] & ""="" Then
Msgbox "Missing Dept and Supervisor"
Exit Sub
End if

If Forms!Switchboard!DeptCombo & ""= "" Then
Msgbox "Missing Dept"
Exit Sub
End if

If [Forms]![Switchboard]![SupCombo] & ""="" Then
Msgbox "Missing Supervisor"
Exit Sub
End if


<I WOULD LIKE TO ENTER CODE FOR THE DATAYEAR OPTION GROUP HERE>
OPTION VALUE 2 = 2011
OPTION VALUE 1 = 2012


If (Forms!Switchboard!DeptCombo = "Tech Ops") Then
' Open Tech Ops Scorecard Metrics Form
DoCmd.OpenForm "_frm_Scorecard_Mth", acNormal, "", "[_qry_Scorecard_Mth]![Supervisor] Like ""*"" & [Forms]![Switchboard]![SupCombo] & ""*"" And [_qry_Scorecard_Mth]![EmpName] Like ""*"" & [Forms]![Switchboard]![EmpCombo] & ""*"" And [_qry_Scorecard_Mth]![MthEndDate]>=Date()-183", , acNormal
End If
If (Forms!Switchboard!DeptCombo = "Plant Ops") Then
' Open Plant Ops Scorecard Metrics Form
DoCmd.OpenForm "_frm_Plant_Mth", acNormal, "", "[_qry_Plant_Mth]![Supervisor] Like ""*"" & [Forms]![Switchboard]![SupCombo] & ""*"" And [_qry_Plant_Mth]![EmpName] Like ""*"" & [Forms]![Switchboard]![EmpCombo] & ""*"" And [_qry_Plant_Mth]![MthEndDate]>=Date()-183", , acNormal
End If
DoCmd.Close acForm, "Switchboard"


CmdPrev6Mths_Click_Exit:
Exit Sub

CmdPrev6Mths_Click_Err:
MsgBox Error$
Resume CmdPrev6Mths_Click_Exit

End Sub


Someone told me to use either the Year or DateDiff functions, but I don’t know how to code those to bring it all together. I am extremely new to vba and am really hoping you can help me.

[jzwp11]

Using an option group or option buttons will only work for 2011 and 2012 with your current plan. That will force you to rework the form in 2013 which is probably not something you want to do. I would recommend creating a query based on one or both of the queries you are already using in your code and select the distinct DataYear. Use that query for a combo box; it will be dynamic from year to year.

That query would go like this:


SELECT DISTINCT DataYear
FROM queryname

As to your code to check if something was selected from the combo boxes, you can simplify it somewhat

You currently have this:

If Forms!Switchboard!DeptCombo & ""= "" Then
Msgbox "Missing Dept"
Exit Sub
End if

This is a little cleaner and forces control to the combo box with a missing selection

If nz(me.DeptCombo, "") = "" Then
  Msgbox "Missing Dept"
  me.DeptCombo.setfocus
  Exit Sub
End if

You may also want to try the IsNull() function in place of the nz() function.

If IsNull(me.DeptCombo) Then
  Msgbox "Missing Dept"
  me.DeptCombo.setfocus
  Exit Sub
End if

By the way if you are referring to controls on the same form as the button, you can use the me. shorthand notation rather than forms!formname

[June7]

This month criteria [_qry_Scorecard_Mth]![MthEndDate]>=Date()-183 is working?

You want previous 183 days but not from the previous year?

Could extract the year from Date().

AND Year([MthEndDate]=Year(Date())

Caution with using current date as criteria. If you run the report on Jan 1, the year criteria will likely result in no records, even though you probably want the data from last year.

Also, by subtracting days you won't get full month records. Run report on Aug 23 will pull records from Feb 22 forward. Is that what you want?

[Seanter]

I have buttons that bring up monthly reports for the last six months of 2011, as well as all 12 months of 2011. A 3rd button for YTD information brings up just one form for the 2011 calandar year. I only have monthly data, by month end date and one year end form with YTD 2011 data; all tables/queries have a datayear field for the year the data pertains to.

I may be better off leaving those as is, and just creating another button that would pull only data with a datayear >2011.

As for cleaning up the error messages for missing selections, I will try that tonight.

Thank you.

[jzwp11]

Along June7's reply, you can use the year function in the query for the combo box I mentioned

SELECT DISTINCT year(MthEndDate)
FROM queryname

[pbaldy]

And in case you didn't know, as I can't tell from:

OPTION VALUE 2 = 2011

You can give the options in an option group values other than 1, 2, 3. In your case, you can give each option the value of 2011, 2012, etc so no code is required to interpret the choice. You can simply use the value of the frame in your code.

[June7]

So next year you have to modify forms and reports? Are you retaining multi-year data?

[Seanter]

I don't think I'll have to modify the reports. I have a query that pulls all the data, and the code pulls the time periods I need. This database has been a project I've been working on for a number of months now, and haven't had the need to separate years of data until now. Using code has been a major learning experience.

[Seanter]

Thank you, Paul. I didn't know that; thank you for the tip.

[pbaldy]

Happy to help. June's point is a good one by the way. I probably wouldn't use an option group to choose date, since it would require design changes to the form next year. I'd probably use a table for years and a combo or list box.

[Seanter]

I am retaining multi-year data, yes. A couple of items in my data are only available monthly, whereas others I am able to pull YTD data. I have queries set up to calculate YTD data from the monthly data by averaging/summing up what is available.

(In my original database I had created forms and queries for every reporting instance and search option. I have consolidated all this in my new database by using vba code, which I am extremely new to.)

I did create a table, called DataYear, and input only the years 2011 and 2012 so far, with the datafield being named the same as the table. At your suggestions, I gave up the idea for an option group and created another combo box to pull the years, which is now working.

Your observations have given me pause to think, though, as to how I should proceed. I'm new to working with this kind of database, where I need to provide monthly and YTD information, and recently a request has come to me to provide quarterly information.

Now I just need to work out how to separate the data, especially on the queries calculating YTD information.

I thank you all for your help.

[jzwp11]

Not knowing much about your table structure or your actual data this would be just a guess, but I would probably use a date field that captures the period ending date for the data. You would then have another field that specifies the period. You could actually use that as a foreign key to a table that holds all possible periods: weekly, monthly, quarterly, semi-annually, annually

tblPeriodTypes
-pkPeriodTypeID primary key, autonumber
-txtPeriodTypeName


Main table
-pkRecordID primary key, autonumber
-dtePeriodEnding
-fkPeriodTypeID foreign key to tblPeriodTypes