S
Shals
I have a form which is getting data from Table Buildings.
Now when I want to update the data on form I'm using cmdUpdate_Click
code on Update Button which is as follows:
`````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
Private Sub cmdUpdate_Click()
On Error GoTo ActionSaveRecordError
Dim strQry As String, curbldg As String
Dim cnn As ADODB.Connection
Dim myRecSet As ADODB.Recordset, rst As ADODB.Recordset
Dim strQryInsert As String, strQry2 As String 'rst1 As
ADODB.Recordset,
Dim intConstruct As Long
Set cnn = CurrentProject.Connection
curbldg = IIf(IsNull(Me.BuildingNumber), "", Me.BuildingNumber)
If IsNull(curbldg) Then
MsgBox "Select a Building first."
DoCmd.Hourglass False
Exit Sub
End If
Set myRecSet = New ADODB.Recordset
myRecSet.Open "SELECT SumOfBasic, SumOfASF, Circulation,
SumOfToilets,
Mechanical,Custodial,SumOfCovered,SumOfSwimmingPools,SumOfParking,Janitorized,sumofunrelated,sumofunfinished
FROM vFDXQrytestBldgFloor WHERE BuildingNumber='" & curbldg & "'", cnn
If myRecSet.RecordCount <> 0 Then
' Structural
Me.BasicGrossArea = IIf(IsNull(myRecSet!SumOfBasic), 0,
myRecSet!SumOfBasic)
Me.CoveredUnenclosedGro = IIf(IsNull(myRecSet!SumOfCovered), 0,
myRecSet!SumOfCovered)
' Me.GSFTotal = Me.BasicGrossArea + Me.CoveredUnenclosedGro / 2
Me.GSFTotal = IIf(IsNull(myRecSet!SumOfBasic), 0,
myRecSet!SumOfBasic) + IIf(IsNull(myRecSet!SumOfCovered), 0,
myRecSet!SumOfCovered / 2)
' Functional
Me.ASFTotal = IIf(IsNull(myRecSet!SumOfASF), 0,
myRecSet!SumOfASF)
Me.Custodial = IIf(IsNull(myRecSet!Custodial), 0,
myRecSet!Custodial)
Me.Circulation = IIf(IsNull(myRecSet!Circulation), 0,
myRecSet!Circulation)
Me.Mechanical = IIf(IsNull(myRecSet!Mechanical), 0,
myRecSet!Mechanical)
Me.Public = IIf(IsNull(myRecSet!SumOfToilets), 0,
myRecSet!SumOfToilets)
Me.Parking = IIf(IsNull(myRecSet!sumofparking), 0,
myRecSet!sumofparking)
Me.Unrelated = IIf(IsNull(myRecSet!sumofUnrelated), 0,
myRecSet!sumofUnrelated)
Me.Unfinished = IIf(IsNull(myRecSet!SumOfUnfinished), 0,
myRecSet!SumOfUnfinished)
' Construction
intConstruct = Me![BasicGrossArea] - Me![Parking] -
Me![Custodial] - Me![Circulation] - Me![Mechanical] - Me![Public] -
Me![ASFTotal]
If intConstruct < 0 Then
Me.Construction = 0
Else
Me.Construction = intConstruct
End If
' Added total Functional field below
Me.FunTotal = Me.ASFTotal.Value + Me.Custodial.Value +
Me.Circulation.Value + Me.Mechanical.Value + Me.PublicToiletArea.Value
+ Me.Parking.Value + Me.Construction.Value
' Special
Me.SwimPools = IIf(IsNull(myRecSet!SumOfSwimmingPools), 0,
myRecSet!SumOfSwimmingPools)
Me.JanitorizedArea = IIf(IsNull(myRecSet!Janitorized), 0,
myRecSet!Janitorized)
Else
Me.GSFTotal = Me.BasicGrossArea + Me.CoveredUnenclosedGro / 2
Me.FunTotal = Me.ASFTotal.Value + Me.Custodial.Value +
Me.Circulation.Value + Me.Mechanical.Value + Me.PublicToiletArea.Value
+ Me.Parking.Value + Me.Construction.Value
Me.Construction = Me![BasicGrossArea] - Me![Parking] -
Me![Custodial] - Me![Circulation] - Me![Mechanical] - Me![Public] -
Me![ASFTotal]
End If
strQry = "SELECT * FROM Buildings WHERE BuildingNumber = '" &
Me.BuildingNumber & "'"
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockBatchOptimistic
rst.Open strQry, cnn
If rst.RecordCount <> 0 Then
With rst
.Fields("BuildingNumber") = Me.BuildingNumber
.Fields("Address") = Me.Address
.Fields("BasicGrossArea") = Me.BasicGrossArea
.Fields("BuildingName") = Me.BuildingName
.Fields("LongBuildingName") = Me.txtLongName
.Fields("CityCode") = Me.CityCode
.Fields("ConditionCode") = Me.ConditionCode
.Fields("CoveredUnenclosedGrossArea") =
Me.CoveredUnenclosedGro
.Fields("DateChanged") = Me.DateChanged
' .Fields("LastModifiedBy") = Environ("username")
' .Fields("DateEntered") = Now()
.Fields("DateOccupancy") = Me.DateOccupancy
.Fields("FunctionalCategoryCode") =
Me.FunctionalCategoryCo
.Fields("JanitorizedArea") = Me.Janitorized
.Fields("MasterPlanCode") = Me.MasterPlanCode
.Fields("Method") = Me.Method
.Fields("MWBSY") = Me.MWBSY
.Fields("Notes") = Me.Notes
.Fields("NumberLevels") = Me.NumberLevels
.Fields("OwnershipCode") = Me.OwnershipCode
' .Fields("SpecialArea") = Me.SpecialArea
.Fields("SpecialArea") = Me.SwimPools
.Fields("UnfinishedGrossArea") = Me.UnfinishedGrossArea
.Fields("UniformBuildingCode") = Me.UniformBuildingCode
.Fields("UnrelatedGrossArea") = Me.UnrelatedGrossArea
.Fields("YearConstructed") = Me.YearConstructed
.Fields("YearLatestImprovement") =
Me.YearLatestImprovement
.Fields("Circulation") = Me.Circulation
.Fields("ConstructionArea") = Me.Construction
.Fields("Coordinate") = Me.CoordinateLocation
.Fields("Custodial") = Me.Custodial
.Fields("MaintainedArea") = Me.MaintainedArea
.Fields("Mechanical") = Me.Mechanical
.Fields("PublicToiletArea") = Me.PublicToiletArea
' .Fields("ByFloorDateChanged") = Me.ByFloorDateChanged
.Fields("SeismicCode") = Me.SeismicCode
.Fields("ECO_RATING") = Me.ECO_RATING
.Fields("ECO_YEAR") = Me.ECO_YEAR
.Fields("PrivateParking") = Me.PrivateParking
.UpdateBatch
.close
End With
Else
' Add New Record
With rst
.AddNew
.Fields("BuildingNumber") = Me.BuildingNumber
.Fields("Address") = Me.Address
.Fields("BasicGrossArea") = Me.BasicGrossArea
.Fields("BuildingName") = Me.BuildingName
.Fields("LongBuildingName") = Me.txtLongName
.Fields("CityCode") = Me.CityCode
.Fields("ConditionCode") = Me.ConditionCode
.Fields("CoveredUnenclosedGrossArea") =
Me.CoveredUnenclosedGro
.Fields("DateChanged") = Me.DateChanged
.Fields("LastModifiedBy") = Environ("username")
.Fields("DateEntered") = Now()
.Fields("DateOccupancy") = Me.DateOccupancy
.Fields("FunctionalCategoryCode") = Me.FunctionalCategoryCo
.Fields("JanitorizedArea") = Me.Janitorized
.Fields("MasterPlanCode") = Me.MasterPlanCode
.Fields("Method") = Me.Method
.Fields("MWBSY") = Me.MWBSY
.Fields("Notes") = Me.Notes
.Fields("NumberLevels") = Me.NumberLevels
.Fields("OwnershipCode") = Me.OwnershipCode
' .Fields("SpecialArea") = Me.SpecialArea
.Fields("SpecialArea") = Me.SwimPools
.Fields("UnfinishedGrossArea") = Me.UnfinishedGrossArea
.Fields("UniformBuildingCode") = Me.UniformBuildingCode
.Fields("UnrelatedGrossArea") = Me.UnrelatedGrossArea
.Fields("YearConstructed") = Me.YearConstructed
.Fields("YearLatestImprovement") = Me.YearLatestImprovement
.Fields("Circulation") = Me.Circulation
.Fields("ConstructionArea") = Me.Construction
.Fields("Coordinate") = Me.CoordinateLocation
.Fields("Custodial") = Me.Custodial
.Fields("MaintainedArea") = 0
.Fields("Mechanical") = Me.Mechanical
.Fields("PublicToiletArea") = Me.PublicToiletArea
.Fields("ByFloorDateChanged") = Me.ByFloorDateChanged
.Fields("SeismicCode") = Me.SeismicCode
.Fields("ECO_RATING") = Me.ECO_RATING
.Fields("ECO_YEAR") = Me.ECO_YEAR
.Fields("PrivateParking") = Me.PrivateParking
.Fields("ByFloorModifiedBy") = Environ("username")
.Fields("ByFloorDateChanged") = Now()
.UpdateBatch
MsgBox "New Building added", vbOKOnly, "Add building"
.Close
End With
End If
DoCmd.SetWarnings False
' Close the Recordset and Database objects created
myRecSet.Close
cnn.Close
Set myRecSet = Nothing
Set cnn = Nothing
Exit Sub
ActionSaveRecordError:
Beep
MsgBox Error$ , 48, "Save Record Error"
Exit Sub
End Sub
`````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
This Procedure is working fine for new record that is added but gives
error when record already exists and gives the error when it comes at
..update statement of the code.
The error I'm getting is " Subquery returned more tan 1 value. This is
not permitted when the subquery follows =,!=,<,<=,>,>= or when the
subquery is used as an expression.
any help in the code would be very appreciated.
Now when I want to update the data on form I'm using cmdUpdate_Click
code on Update Button which is as follows:
`````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
Private Sub cmdUpdate_Click()
On Error GoTo ActionSaveRecordError
Dim strQry As String, curbldg As String
Dim cnn As ADODB.Connection
Dim myRecSet As ADODB.Recordset, rst As ADODB.Recordset
Dim strQryInsert As String, strQry2 As String 'rst1 As
ADODB.Recordset,
Dim intConstruct As Long
Set cnn = CurrentProject.Connection
curbldg = IIf(IsNull(Me.BuildingNumber), "", Me.BuildingNumber)
If IsNull(curbldg) Then
MsgBox "Select a Building first."
DoCmd.Hourglass False
Exit Sub
End If
Set myRecSet = New ADODB.Recordset
myRecSet.Open "SELECT SumOfBasic, SumOfASF, Circulation,
SumOfToilets,
Mechanical,Custodial,SumOfCovered,SumOfSwimmingPools,SumOfParking,Janitorized,sumofunrelated,sumofunfinished
FROM vFDXQrytestBldgFloor WHERE BuildingNumber='" & curbldg & "'", cnn
If myRecSet.RecordCount <> 0 Then
' Structural
Me.BasicGrossArea = IIf(IsNull(myRecSet!SumOfBasic), 0,
myRecSet!SumOfBasic)
Me.CoveredUnenclosedGro = IIf(IsNull(myRecSet!SumOfCovered), 0,
myRecSet!SumOfCovered)
' Me.GSFTotal = Me.BasicGrossArea + Me.CoveredUnenclosedGro / 2
Me.GSFTotal = IIf(IsNull(myRecSet!SumOfBasic), 0,
myRecSet!SumOfBasic) + IIf(IsNull(myRecSet!SumOfCovered), 0,
myRecSet!SumOfCovered / 2)
' Functional
Me.ASFTotal = IIf(IsNull(myRecSet!SumOfASF), 0,
myRecSet!SumOfASF)
Me.Custodial = IIf(IsNull(myRecSet!Custodial), 0,
myRecSet!Custodial)
Me.Circulation = IIf(IsNull(myRecSet!Circulation), 0,
myRecSet!Circulation)
Me.Mechanical = IIf(IsNull(myRecSet!Mechanical), 0,
myRecSet!Mechanical)
Me.Public = IIf(IsNull(myRecSet!SumOfToilets), 0,
myRecSet!SumOfToilets)
Me.Parking = IIf(IsNull(myRecSet!sumofparking), 0,
myRecSet!sumofparking)
Me.Unrelated = IIf(IsNull(myRecSet!sumofUnrelated), 0,
myRecSet!sumofUnrelated)
Me.Unfinished = IIf(IsNull(myRecSet!SumOfUnfinished), 0,
myRecSet!SumOfUnfinished)
' Construction
intConstruct = Me![BasicGrossArea] - Me![Parking] -
Me![Custodial] - Me![Circulation] - Me![Mechanical] - Me![Public] -
Me![ASFTotal]
If intConstruct < 0 Then
Me.Construction = 0
Else
Me.Construction = intConstruct
End If
' Added total Functional field below
Me.FunTotal = Me.ASFTotal.Value + Me.Custodial.Value +
Me.Circulation.Value + Me.Mechanical.Value + Me.PublicToiletArea.Value
+ Me.Parking.Value + Me.Construction.Value
' Special
Me.SwimPools = IIf(IsNull(myRecSet!SumOfSwimmingPools), 0,
myRecSet!SumOfSwimmingPools)
Me.JanitorizedArea = IIf(IsNull(myRecSet!Janitorized), 0,
myRecSet!Janitorized)
Else
Me.GSFTotal = Me.BasicGrossArea + Me.CoveredUnenclosedGro / 2
Me.FunTotal = Me.ASFTotal.Value + Me.Custodial.Value +
Me.Circulation.Value + Me.Mechanical.Value + Me.PublicToiletArea.Value
+ Me.Parking.Value + Me.Construction.Value
Me.Construction = Me![BasicGrossArea] - Me![Parking] -
Me![Custodial] - Me![Circulation] - Me![Mechanical] - Me![Public] -
Me![ASFTotal]
End If
strQry = "SELECT * FROM Buildings WHERE BuildingNumber = '" &
Me.BuildingNumber & "'"
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockBatchOptimistic
rst.Open strQry, cnn
If rst.RecordCount <> 0 Then
With rst
.Fields("BuildingNumber") = Me.BuildingNumber
.Fields("Address") = Me.Address
.Fields("BasicGrossArea") = Me.BasicGrossArea
.Fields("BuildingName") = Me.BuildingName
.Fields("LongBuildingName") = Me.txtLongName
.Fields("CityCode") = Me.CityCode
.Fields("ConditionCode") = Me.ConditionCode
.Fields("CoveredUnenclosedGrossArea") =
Me.CoveredUnenclosedGro
.Fields("DateChanged") = Me.DateChanged
' .Fields("LastModifiedBy") = Environ("username")
' .Fields("DateEntered") = Now()
.Fields("DateOccupancy") = Me.DateOccupancy
.Fields("FunctionalCategoryCode") =
Me.FunctionalCategoryCo
.Fields("JanitorizedArea") = Me.Janitorized
.Fields("MasterPlanCode") = Me.MasterPlanCode
.Fields("Method") = Me.Method
.Fields("MWBSY") = Me.MWBSY
.Fields("Notes") = Me.Notes
.Fields("NumberLevels") = Me.NumberLevels
.Fields("OwnershipCode") = Me.OwnershipCode
' .Fields("SpecialArea") = Me.SpecialArea
.Fields("SpecialArea") = Me.SwimPools
.Fields("UnfinishedGrossArea") = Me.UnfinishedGrossArea
.Fields("UniformBuildingCode") = Me.UniformBuildingCode
.Fields("UnrelatedGrossArea") = Me.UnrelatedGrossArea
.Fields("YearConstructed") = Me.YearConstructed
.Fields("YearLatestImprovement") =
Me.YearLatestImprovement
.Fields("Circulation") = Me.Circulation
.Fields("ConstructionArea") = Me.Construction
.Fields("Coordinate") = Me.CoordinateLocation
.Fields("Custodial") = Me.Custodial
.Fields("MaintainedArea") = Me.MaintainedArea
.Fields("Mechanical") = Me.Mechanical
.Fields("PublicToiletArea") = Me.PublicToiletArea
' .Fields("ByFloorDateChanged") = Me.ByFloorDateChanged
.Fields("SeismicCode") = Me.SeismicCode
.Fields("ECO_RATING") = Me.ECO_RATING
.Fields("ECO_YEAR") = Me.ECO_YEAR
.Fields("PrivateParking") = Me.PrivateParking
.UpdateBatch
.close
End With
Else
' Add New Record
With rst
.AddNew
.Fields("BuildingNumber") = Me.BuildingNumber
.Fields("Address") = Me.Address
.Fields("BasicGrossArea") = Me.BasicGrossArea
.Fields("BuildingName") = Me.BuildingName
.Fields("LongBuildingName") = Me.txtLongName
.Fields("CityCode") = Me.CityCode
.Fields("ConditionCode") = Me.ConditionCode
.Fields("CoveredUnenclosedGrossArea") =
Me.CoveredUnenclosedGro
.Fields("DateChanged") = Me.DateChanged
.Fields("LastModifiedBy") = Environ("username")
.Fields("DateEntered") = Now()
.Fields("DateOccupancy") = Me.DateOccupancy
.Fields("FunctionalCategoryCode") = Me.FunctionalCategoryCo
.Fields("JanitorizedArea") = Me.Janitorized
.Fields("MasterPlanCode") = Me.MasterPlanCode
.Fields("Method") = Me.Method
.Fields("MWBSY") = Me.MWBSY
.Fields("Notes") = Me.Notes
.Fields("NumberLevels") = Me.NumberLevels
.Fields("OwnershipCode") = Me.OwnershipCode
' .Fields("SpecialArea") = Me.SpecialArea
.Fields("SpecialArea") = Me.SwimPools
.Fields("UnfinishedGrossArea") = Me.UnfinishedGrossArea
.Fields("UniformBuildingCode") = Me.UniformBuildingCode
.Fields("UnrelatedGrossArea") = Me.UnrelatedGrossArea
.Fields("YearConstructed") = Me.YearConstructed
.Fields("YearLatestImprovement") = Me.YearLatestImprovement
.Fields("Circulation") = Me.Circulation
.Fields("ConstructionArea") = Me.Construction
.Fields("Coordinate") = Me.CoordinateLocation
.Fields("Custodial") = Me.Custodial
.Fields("MaintainedArea") = 0
.Fields("Mechanical") = Me.Mechanical
.Fields("PublicToiletArea") = Me.PublicToiletArea
.Fields("ByFloorDateChanged") = Me.ByFloorDateChanged
.Fields("SeismicCode") = Me.SeismicCode
.Fields("ECO_RATING") = Me.ECO_RATING
.Fields("ECO_YEAR") = Me.ECO_YEAR
.Fields("PrivateParking") = Me.PrivateParking
.Fields("ByFloorModifiedBy") = Environ("username")
.Fields("ByFloorDateChanged") = Now()
.UpdateBatch
MsgBox "New Building added", vbOKOnly, "Add building"
.Close
End With
End If
DoCmd.SetWarnings False
' Close the Recordset and Database objects created
myRecSet.Close
cnn.Close
Set myRecSet = Nothing
Set cnn = Nothing
Exit Sub
ActionSaveRecordError:
Beep
MsgBox Error$ , 48, "Save Record Error"
Exit Sub
End Sub
`````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
This Procedure is working fine for new record that is added but gives
error when record already exists and gives the error when it comes at
..update statement of the code.
The error I'm getting is " Subquery returned more tan 1 value. This is
not permitted when the subquery follows =,!=,<,<=,>,>= or when the
subquery is used as an expression.
any help in the code would be very appreciated.