setting a default year in a table with multiple years

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 wrote in message

> 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.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - [URL="http://allenbrowne.com/tips.html"]http://allenbrowne.com/tips.html[/URL]
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Doug Glancy" wrote in message

>>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
>
 >> Stay informed about: setting a default year in a table with multiple years 

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.
 >> Stay informed about: setting a default year in a table with multiple years 

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 wrote in message

> 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.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - [URL="http://allenbrowne.com/tips.html"]http://allenbrowne.com/tips.html[/URL]
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Doug Glancy" wrote in message

>>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
>
 >> Stay informed about: setting a default year in a table with multiple years 

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
 >> Stay informed about: setting a default year in a table with multiple years 

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

"Allen Browne" wrote in message

> 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 wrote in message

>> 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
>
 >> Stay informed about: setting a default year in a table with multiple years