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