Reverse Y2k??

speakers_86

Registered User.
Local time
Today, 07:44
Joined
May 17, 2007
Messages
1,919
So the code that I spent all night on works, unless the year is before 2000. Can anyone tell me why? I prob won't need to actually use any time before 2000, but this could just be a but and not some glitch. Combo2 is a dropdown that holds the year. This value is also stored in vYear.

Code:
Option Compare Database
Public Suffix As String
Public vYear As String
Public Function FullSER() As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This is shared for all four controls
FullSER = "000-" & Suffix & "Q" & vYear
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Function

Public Function ReportSQL() As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Changes the rowsource of combo144 and disables irrelevant pages
Dim sql As String
sql = "SELECT [tblReportType].ReportTypeID, [tblReportType].ReportTypeText, [tblReportType].ReportTypeAbbr FROM tblReportType WHERE (((tblReportType.ReportTypeText)='Quarterly')) ORDER BY [ReportTypeID]; "
[Forms]![frmser]![Combo144].RowSource = sql
[Forms]![frmser]![Combo144].Requery
[Forms]![frmser]![Page177].Enabled = False
[Forms]![frmser]![Page178].Enabled = False
[Forms]![frmser]![Page226].Enabled = False
[Forms]![frmser]![Page189].Enabled = False
[Forms]![frmser]![Page209].Enabled = False
[Forms]![frmser]![Page188].Enabled = False
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Function

Public Function SeriesCheck()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Creates a new ser series for quarterly (one series for each year)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblserseries")
rst.AddNew
rst("serseries") = 0
rst("seropen") = 0
rst("issue") = "Quarterly"
rst("siteid") = 1
'If user creates quarterly ser for a previous year, the dateserieslogged field needs to have that year in it.
'This code uses an arbitrary date of 1/1/ & whatever year is selected.  This value is stored in tblYear, and
'referenced via column(1)
If Right(Date, 4) = Right(Me.Combo2.Column(1), 4) Then
    rst("seriesdatelogged") = Date
    Else
    rst("seriesdatelogged") = Me.Combo2.Column(1)
End If
rst.Update
Set rst = Nothing
Set db = Nothing

End Function

Public Function SuffixCheck()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Creates a new suffix
Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
Set db1 = CurrentDb
Set rst1 = db1.OpenRecordset("tblsersuffix")
rst1.AddNew
rst1("serseriesid") = DLookup("[serseriesid]", "qryQuarterlySerCheck", "[SeriesDate] =" & [Combo2])
rst1("sersuffix") = Suffix
rst1("reporttypeid") = 6
rst1.Update
Set rst1 = Nothing
Set db1 = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Function

Public Function OpenForm()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Setfocus so that the cursor doesnt stay on the control
Me.Combo2.SetFocus
'Determine the year for purposes of determining the final ser number
If Nz(Me.Combo2, Right(Date, 4)) = Right(Date, 4) Then
    vYear = ""
    Else
    vYear = "(" & Right(Me.Combo2, 2) & ")"
End If
MsgBox vYear
'If necessary, creates a new series.  One series for each year.
If IsNull(DLookup("[serseries]", "qryQuarterlySerCheck", "[SeriesDate] =" & [Combo2])) Then
    Call SeriesCheck
End If
'Determine if new suffix needs to be created
If IsNull(DLookup("[fullser]", "qryser2", "[fullser]='" & FullSER & "'")) Then
    Call SuffixCheck
End If
'Open frmSER
DoCmd.OpenForm "frmser", , , "[fullser]='" & FullSER & "'"
'Sets combo 144 to only quarterly
Call ReportSQL
[Forms]![frmser].TabCtl176.Pages.Item("page186").SetFocus
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Function

Private Sub Text11_Click()
Suffix = "04"
Call OpenForm
End Sub

Private Sub Text5_Click()
Suffix = "01"
Call OpenForm
End Sub

Private Sub Text7_Click()
Suffix = "02"
Call OpenForm
End Sub

Private Sub Text9_Click()
Suffix = "03"
Call OpenForm
End Sub
 
Understanding someone else's code is not a trivial matter. Can you indicate where the code fails and the nature of the failure?
Cheers,
 
Hmmmkay, lol. The form is simple enough. It is a little pop up with 5 controls. One is a dropdown, combo2, based on tblYears. Column(0) contains the year as data type. Column(1) contains Jan 1, XXXX as date.

The other four controls are simply unbound textboxes that, when clicked, set the variable "Suffix" and call function OpenForm.

Openform is the central object. It is responsible for opening frmser where FullSER equals the correct value. The correct value is "000-"XX "Q" (YY). The x's are either, 01, 02, 03, 04, depending on which control the user clicks. The y's are only the last 2 of the year, and it is only there if it is a previous year.

Openform can also call 2 other important functions when it needs to. Essentially there is a composite key. SeriesID is one key, and there is one per year. Its existence is checked for, and when it is not there, it calls SeriesCheck, which adds the series.

Suffix is the other one, and its existence is checked, when it does not exist it is added with SuffixCheck. There are four suffix per year, one for each quarter, or one for each of the unbound textbox controls.


What is going wrong is that any year 2000 or higher is fine. Any year below and the code tries to pull up frmser with an incomplete fullser. Msgbox says fullser is storing the correct value, but frmser isn't opening right.

There is definitely a rhyme and reason to this. I am only showing one side of the project right now, that's why it seems weird.


I'm definitely going to look more into this, but I'm out of time for the day. :(
 
I haven't he time to study in depth besides it's difficult on the IPad :D but I 'm going to guess that it's to do with the fact that you are only using the last two digits and 99 is not prior to 00

Brian
 
I haven't he time to study in depth besides it's difficult on the IPad :D but I 'm going to guess that it's to do with the fact that you are only using the last two digits and 99 is not prior to 00

Brian


Agree.

How would the code know if 99 relates to 1999 or 2099?

equally, it won't be able to handle 2100 or above.

2 digits will only work for a 100 year period (00 to 99)
 
I found the issue. It was not in this code. The query that frmser is based on compared years using the last 2 digits. Thanks!
 

Users who are viewing this thread

Back
Top Bottom