setting a default year in a table with multiple years

[Doug Glancy]

I have a table that contains a list of fiscal years - like "2008-09". It
also has a YearID field, and a IsCurrentYear field of type "Yes/No". As we
enter a new year I want them to be able to specify a new current year. After
some reading, I think the best way may be a continuous form that responds to
the AfterUpdate event of a option button whose ControlSource is
IsDefaultYear. In the event, I think, I'd turn all the other option buttons
off. I can see various problems with this, what I really want is an option
group that forces the user to pick one, and only one, default year.

Thanks in advance,

Doug

 

[Allen Browne]

Couple of suggestions, Doug.

Firstly, consider using a Number field for your fiscal year, rather than a
Text field such as "2008-09". This will simplify the math enormously.
Perhaps call the field YearStarting if it stores the 2008, or YearEnding if
you prefer to call it 2009.

Next, would it be possible to programmatically deduce the current year, or
do you really need to store it? If it does need storing, I would be tempted
to store it in a table of configuration values rather than as a check box in
this table.

Now we come to assigning this as the default year. As you know, you can
type:
1/1
into a text box bound to a date/time field, and Access will assign the
current year. It would be possible to use the AfterUpdate event procedure of
the text box to assign a different year if the user didn't specify one. The
Text property of the control will let you know if the user typed a year or
not. You can then use DateSerial() to assign the same month and day, but
change the year from this year to your default year.

The only problem I can think of is Feb 29th. The user would need to type
this date in full to avoid the error message that it did not apply to the
current year.

(Of course, you would need your code that assigns the year to respect your
fiscal year boundary.)

 

[Doug Glancy]

Allen,

Thanks for the ideas. Maybe I should just figure it programatically, which
is easy enough, I think. The good thing about having the user set it is
that I don't have to worry about the Text or Number field issue that you
mentioned, because I'd just work with the YearID field.

What does a table of configuration values look like? I'd think that mixing
dates, yes/no's etc. would be tricky.

As you know, you can type:1/1into a text box bound to a date/time field,
and Access will assign the current year.

I'm pretty sure I never knew that <g> but I do now, thanks.

I appreciate your help,

Doug

 

[Allen Browne]

The data type of the config. table is tricky, as you say. What I tend to do
is use a table with fields like this:
- VarName Text (24) Primary key. What name you want to look
up.
- VarValue Text (255) The value to return when you look it up.
- DataType Number (Long) See below
- Descrip Text (255) Explanation of what this variable is
for.

The DataType is one of the vbVarType values, displayed as a combo (yes, I
know: combos in tables!) with properties:
RowSourceType: Value List
RowSource:
2;"Integer";3;"Long";5;"Double";6;"Currency";7;"Date";8;"String";11;"Boolean";17;"Byte"
ColumnCount: 2
ColumnWidths: 0

With this arrangement, you can use the BeforeUpdate event procedure of the
form where you set the configuration to verify that the data can be treated
as the correct type.

Hopefully the control's AfterUpdate (examining its Text, not its Value),
will give you what you need.

 

[Doug Glancy]

Allen,

In case you are interested, this is the code I came up with. It's on a
continuous form and runs when the option button with the ControlSource of
IsCurrentYear is changed. I might still just go with calculating the
current year on startup, but was curious how to create something like a
Group of option buttons on a continuous form. Feel free to fire away with
any comments!:

Private Sub optIsCurrentYear_AfterUpdate()

Dim lngFirstRow As Long
Dim rstYears As Recordset
Dim lngChangedRecord As Long

Set rstYears = Me.Recordset
lngChangedRecord = rstYears.AbsolutePosition

With Me
.Painting = False
lngFirstRow = .SelTop
End With
With rstYears
.MoveFirst
Do While Not .EOF
If .AbsolutePosition <> lngChangedRecord Then
If .Fields("IsCurrentYear").Value <> False Then
.Edit
.Fields("IsCurrentYear").Value = False
.Update
End If
End If
.MoveNext
Loop
End With
Set rstYears = Nothing
With Me
.Requery
.SelTop = IIf(lngFirstRow - 2 < 1, 1, lngFirstRow - 2)
.Painting = True
End With

End Sub

Doug

 

[Allen Browne]

Okay, that will work.

So you don't need to walk the form through all the records and then find the
original one again, you could use its RecordsetClone:

Dim rstYears As DAO.Recordset
If Me.Dirty Then Me.Dirty = False 'Save edits
Set rstYears = Me.RecordsetClone
With rstYears
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
If (!ID <> Me.ID) AND (!IsCurrentYear.Value) Then
.Edit
!IsCurrentYear = False
.Update
End If
.MoveNext
Loop
End If
End With
Set rstYears = Nothing

Alternatively (possibly less attractively), you could operate directly on
the table if you prefer. Aircode:

dim db As DAO.Database
Dim strSql As String
Dim lngID As Long
If Me.Dirty Then Me.Dirty= False
It Not Me.NewRecord Then
lngID = Me.ID
strSql = "UPDATE Table2 SET IsCurrentYear = False " & _
"WHERE (MyFK = " & Me.SubID & ") AND (ID <> " & lngID" & _
") AND (IsCurrentYear <> False);"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.Requery
With Me.RecordsetClone
.FindFirst "ID = " & lngID
If Not .NoMatch Then
Me.Bookmark = .LastModified
End If
End With
End If
End If

 

[Doug Glancy]

Allen,

Sweet! The form no longer flickers and it fixes the problem I had kludgily
semi-solved of the topmost row changing position. I hadn't done anything
with a recordsetclone before, but I can see how it's useful in this regard,
and in other situations.

I really appreciate your help.

Doug