J
Julie
You open a form that has this in it's open event:
Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "fr6MonthRev", , , , , acDialog
Dim techid As String
Dim begindate As String
Dim enddate As String
tech = [Forms]![fr6MonthRev]![cmbTechID]
begindate = [Forms]![fr6MonthRev]![cmbBeginDate]
enddate = [Forms]![fr6MonthRev]![cmbEndDate]
Dim trash As String
trash = SixMonRev(techid, begindate, enddate)
DoCmd.Close acForm, "fr6MonthRev"
End Sub
When you open this form, the fr6MonRev form becomes visible and you enter
the three parameters into it (tech, begindate, enddate) at which point the
following function should fill in the textboxes on the first mentioned form
with the results from this function:
Function SixMonRev(techid As String, begindate As String, enddate As String)
Dim temp As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
temp = "SELECT tblWorkload.TechID, Sum(tblWorkload.WorkloadTotalGynSlides)
AS SumTotalGyn, " & _
"Sum(tblWorkload.WorkloadTotalNGSlides) AS SumTotalNG," & _
"Sum([workloadtotalgynslides]+[workloadtotalngslides]) AS SumTotalSlides," & _
"Sum(tblWorkload.WorkloadQC) AS SumQC,
Sum(tblWorkload.WorkloadQCDiscrepancies) AS SumQcDis," & _
"Count(tblWorkload.WorkloadDateScreened) AS NumberDays" & _
" FROM tblWorkload WHERE (((tblWorkload.WorkloadDateScreened) Between " &
begindate & " And " & enddate & ")) " & "GROUP BY tblWorkload.TechID " & _
" HAVING (((tblWorkload.TechID)= '" & techid & "'));"
rst.Open temp, CurrentProject.Connection
Forms![frqry6MonRev]![txtTechID] = techid
Forms![frqry6MonRev]![txtBeginDate] = begindate
Forms![frqry6MonRev]![txtEndDate] = enddate
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
Forms![frqry6MonRev]![txtSumNG] = rst("SumTotalNG")
Forms![frqry6MonRev]![txtSumTotalSlides] = rst("SumTotalSlides")
Forms![frqry6MonRev]![txtSumQC] = rst("SumQC")
Forms![frqry6MonRev]![txtSumQCDis] = rst("SumQCDis")
Forms![frqry6MonRev]![txtDailyAve] = rst("SumTotalSlides") \ rst("NumberDays")
Dim temp1 As String
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
temp1 = "SELECT tblFiveYearReview.FiveYearReviewTechID," & _
"Sum(tblFiveYearReview.FiveYearReview) AS Sum5YrRev," & _
"Sum(tblFiveYearReview.FiveYearReviewDiscrepancies) AS Sum5YrRevDis" & _
"FROM tblFiveYearReview" & _
"WHERE (((tblFiveYearReview.FiveYearReviewDateScreened) Between " &
begindate & " And " & enddate & "))" & _
"GROUP BY tblFiveYearReview.FiveYearReviewTechID" & _
"HAVING (((tblFiveYearReview.FiveYearReviewTechID)= '" & techid & "'));"
Forms![frqry6MonRev]![txt5YrRev] = rst1("Sum5YrRev")
Forms![frqry6MonRev]![txt5YrRevDis] = rst1("Sum5YrRevDis")
Forms![frqry6MonRev]![txtFNF] = ((rst('SumQCDis") + rst1("Sum5YrRevDis")) /
(rst("SumQC") + rst1("Sum5YrRev"))) * 100
End Function
So, when I open the first form, the second becomes visible, I enter my
parameters and then i get an error message that says "runtime error 2113: the
value you entered isn't valid for this field, " and the line that is
highlighted is in the Function SixMonRev(). It's the line that says:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
And the debugger says "Forms![frqry6MonRev]![txtSumGyn] = Null"
Any clue what I'm missing? Thanks.
Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "fr6MonthRev", , , , , acDialog
Dim techid As String
Dim begindate As String
Dim enddate As String
tech = [Forms]![fr6MonthRev]![cmbTechID]
begindate = [Forms]![fr6MonthRev]![cmbBeginDate]
enddate = [Forms]![fr6MonthRev]![cmbEndDate]
Dim trash As String
trash = SixMonRev(techid, begindate, enddate)
DoCmd.Close acForm, "fr6MonthRev"
End Sub
When you open this form, the fr6MonRev form becomes visible and you enter
the three parameters into it (tech, begindate, enddate) at which point the
following function should fill in the textboxes on the first mentioned form
with the results from this function:
Function SixMonRev(techid As String, begindate As String, enddate As String)
Dim temp As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
temp = "SELECT tblWorkload.TechID, Sum(tblWorkload.WorkloadTotalGynSlides)
AS SumTotalGyn, " & _
"Sum(tblWorkload.WorkloadTotalNGSlides) AS SumTotalNG," & _
"Sum([workloadtotalgynslides]+[workloadtotalngslides]) AS SumTotalSlides," & _
"Sum(tblWorkload.WorkloadQC) AS SumQC,
Sum(tblWorkload.WorkloadQCDiscrepancies) AS SumQcDis," & _
"Count(tblWorkload.WorkloadDateScreened) AS NumberDays" & _
" FROM tblWorkload WHERE (((tblWorkload.WorkloadDateScreened) Between " &
begindate & " And " & enddate & ")) " & "GROUP BY tblWorkload.TechID " & _
" HAVING (((tblWorkload.TechID)= '" & techid & "'));"
rst.Open temp, CurrentProject.Connection
Forms![frqry6MonRev]![txtTechID] = techid
Forms![frqry6MonRev]![txtBeginDate] = begindate
Forms![frqry6MonRev]![txtEndDate] = enddate
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
Forms![frqry6MonRev]![txtSumNG] = rst("SumTotalNG")
Forms![frqry6MonRev]![txtSumTotalSlides] = rst("SumTotalSlides")
Forms![frqry6MonRev]![txtSumQC] = rst("SumQC")
Forms![frqry6MonRev]![txtSumQCDis] = rst("SumQCDis")
Forms![frqry6MonRev]![txtDailyAve] = rst("SumTotalSlides") \ rst("NumberDays")
Dim temp1 As String
Dim rst1 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
temp1 = "SELECT tblFiveYearReview.FiveYearReviewTechID," & _
"Sum(tblFiveYearReview.FiveYearReview) AS Sum5YrRev," & _
"Sum(tblFiveYearReview.FiveYearReviewDiscrepancies) AS Sum5YrRevDis" & _
"FROM tblFiveYearReview" & _
"WHERE (((tblFiveYearReview.FiveYearReviewDateScreened) Between " &
begindate & " And " & enddate & "))" & _
"GROUP BY tblFiveYearReview.FiveYearReviewTechID" & _
"HAVING (((tblFiveYearReview.FiveYearReviewTechID)= '" & techid & "'));"
Forms![frqry6MonRev]![txt5YrRev] = rst1("Sum5YrRev")
Forms![frqry6MonRev]![txt5YrRevDis] = rst1("Sum5YrRevDis")
Forms![frqry6MonRev]![txtFNF] = ((rst('SumQCDis") + rst1("Sum5YrRevDis")) /
(rst("SumQC") + rst1("Sum5YrRev"))) * 100
End Function
So, when I open the first form, the second becomes visible, I enter my
parameters and then i get an error message that says "runtime error 2113: the
value you entered isn't valid for this field, " and the line that is
highlighted is in the Function SixMonRev(). It's the line that says:
Forms![frqry6MonRev]![txtSumGyn] = rst("SumTotalGyn")
And the debugger says "Forms![frqry6MonRev]![txtSumGyn] = Null"
Any clue what I'm missing? Thanks.