Type Conversion Error in Update Query

[Comtech]

Hi again,

I kinda posted this before, and I thought I had found a solution, but it turns out I was wrong. I have a form with 2 subforms. I'm trying to calculate attendance percentages for a cadet corps. When I open the form, I get the following error.


If I click No, it points to the DoCmd.OpenQuery ("QueryUpdateAttendancePercentageCadets")



Here is the code:

Private Sub Form_Open(Cancel As Integer)


On Error GoTo Err_Form_Open


Dim CountCadets As Long
Dim CountList As Long
Dim nbrCadetsPresent As Long
Dim nbrCadetsatTime As Long
Dim nbrOfficersPresent As Long
Dim nbrOfficersatTime As Long
Dim dbTraining As Object
Dim rstPresent As Recordset
Dim AttCadetAverage As Long
Dim strCadetAverage As String

Set dbTraining = CurrentDb
Set rstPresent = dbTraining.OpenRecordset("tblListofActivities")


CountList = DCount("[ActivityID]", "tblListofActivities")
CountCadets = DCount("[RecordID]", "tblCadetMain")


Select Case CountCadets


Case Is > 0


[Forms]![frmAttendance]![sfrmAttendanceCadets].SetFocus
DoCmd.GoToRecord , , acFirst


For I = 1 To CountList
nbrCadetsatTime = DCount("[RecordID]", "tblActivitiesCadets", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceCadets].[Form].ActivityID)
nbrCadetsPresent = DCount("[RecordID]", "tblActivitiesCadets", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceCadets].[Form].ActivityID & "AND [Present]=True")
rstPresent.Edit:
If nbrCadetsatTime = 0 Then
rstPresent("CadetsPresent").Value = 0:
rstPresent("CadetCountatTime").Value = 0
Else
rstPresent("CadetsPresent").Value = nbrCadetsPresent:
rstPresent("CadetCountatTime").Value = nbrCadetsatTime
End If
DoCmd.OpenQuery ("QueryUpdateAttendancePercentageCadets")
rstPresent.Update:
If I <> CountList Then
rstPresent.MoveNext
DoCmd.GoToRecord , , acNext
End If
Next I


DoCmd.GoToRecord , , acFirst


[Forms]![frmAttendance]![sfrmAttendanceOfficers].SetFocus
DoCmd.GoToRecord , , acFirst


For I = 1 To CountList
nbrOfficersatTime = DCount("[RecordID]", "tblActivitiesOfficers", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceOfficers].[Form].ActivityID)
nbrOfficersPresent = DCount("[RecordID]", "tblActivitiesOfficers", "[ActivityID] =" & [Forms]![frmAttendance]![sfrmAttendanceOfficers].[Form].ActivityID & "AND [Present]=True")
rstPresent.Edit:
If nbrOfficersatTime = 0 Then
rstPresent("OfficersPresent").Value = 0:
rstPresent("OfficerCountatTime").Value = 0
Else
rstPresent("OfficersPresent").Value = nbrOfficersPresent:
rstPresent("OfficerCountatTime").Value = nbrOfficersatTime
End If
DoCmd.OpenQuery ("QueryUpdateAttendancePercentageOfficers")
rstPresent.Update:
If I <> CountList Then
rstPresent.MoveNext
DoCmd.GoToRecord , , acNext
End If
Next I


DoCmd.GoToRecord , , acFirst


End Select


Exit_Form_Open:
Exit Sub


Err_Form_Open:
MsgBox Err.Description
Resume Exit_Form_Open


End Sub


The SQL for the "QueryUpdateAttendancePercentageCadets" is:

UPDATE tblListofActivities INNER JOIN tblActivitiesCadets ON tblListofActivities.ActivityID = tblActivitiesCadets.ActivityID SET tblListofActivities.CadetAverage = Round([tblListofActivities].[CadetsPresent]/[tblListofActivities].[CadetCountatTime],2)*"100";

The SQL for the "QueryUpdateAttendancePercentageOfficers" is:

UPDATE tblListofActivities INNER JOIN tblActivitiesCadets ON tblListofActivities.ActivityID = tblActivitiesCadets.ActivityID SET tblListofActivities.OfficerAverage = Round([tblListofActivities].[OfficersPresent]/[tblListofActivities].[OfficerCountatTime],2)*"100";



I have been working on this for this for several days, and any help would be greatly appreciated. Thank you.
Comtech

[davegri]

vities].[CadetCountatTime],2)*"100";

Maybe can't multiply by a string. Try:

vities].[CadetCountatTime],2) * 100;

[Comtech]

Thanks for the reply. I made the change, but it still doesn't work. Any other ideas?

Comtech

[davegri]

Take a closer look at your error message. The problem is not Type Conversion, It's Record Lock. The query is trying up update a table (tblListOfActivities) that's open as a recordset.
Instead of using the update query, maybe you could update like:

rstPresent("CadetAverage")  = nbrCadetsPresent / nbrCadetsAtTime * 100

[Comtech]

I actually tried that first. But I got it working using just the query. Thank you for your help.