Please Help. I am teaching myself on how to right this code and have come into a road block. I have taken this database over from somebody who has left my company. I receive the runtime erroe of 3464 when I try to update my table. Here is the code. The line in red is where I think the problem is because if I remove it the form updates the table. The problem with that is it updates all rows not the individual row I want to change.
Private Sub cmdSubmit_Click()
Dim valCheckChange As Boolean
Dim sql As String, valSQL As String
valCheckChange = CheckChange
valSQL = "update CARs set "
valSQL = valSQL & "StructureType = '" & Me.StructureType & "', "
valSQL = valSQL & "StructureNumber = '" & Me.StructureNumber & "', "
valSQL = valSQL & "LocationType = '" & Me.LocationType & "', "
If Len(Trim(Me.Sector)) > 0 Or Not IsNull(Me.Sector) Then
valSQL = valSQL & "Sector = " & Me.Sector & ", "
Else
valSQL = valSQL & "Sector = NULL, "
End If
If Len(Trim(Me.FrontOfAddress)) > 0 Or Not IsNull(Me.FrontOfAddress) Then
valSQL = valSQL & "FrontOfAddress = '" & Me.FrontOfAddress & "', "
Else
valSQL = valSQL & "FrontOfAddress = NULL, "
End If
If Len(Trim(Me.Onstreet)) > 0 Or Not IsNull(Me.Onstreet) Then
valSQL = valSQL & "Onstreet = '" & Me.Onstreet & "', "
Else
valSQL = valSQL & "Onstreet = NULL, "
End If
If Len(Trim(Me.CrossStreet1)) > 0 Or Not IsNull(Me.CrossStreet1) Then
valSQL = valSQL & "CrossStreet1 = '" & Me.CrossStreet1 & "', "
Else
valSQL = valSQL & "CrossStreet1 = NULL, "
End If
If Len(Trim(Me.CrossStreet2)) > 0 Or Not IsNull(Me.CrossStreet2) Then
valSQL = valSQL & "CrossStreet2 = '" & Me.CrossStreet2 & "', "
Else
valSQL = valSQL & "CrossStreet2 = NULL, "
End If
If Len(Trim(Me.FacilityType)) > 0 Or Not IsNull(Me.FacilityType) Then
valSQL = valSQL & "FacilityType = '" & Me.FacilityType & "', "
Else
valSQL = valSQL & "FacilityType = NULL, "
End If
If Len(Trim(Me.Condition)) > 0 Or Not IsNull(Me.Condition) Then
valSQL = valSQL & "Condition = '" & Me.Condition & "', "
Else
valSQL = valSQL & "Condition = NULL, "
End If
If Len(Trim(Me.Inspector)) > 0 Or Not IsNull(Me.Inspector) Then
valSQL = valSQL & "Inspector = '" & Me.Inspector & "', "
Else
valSQL = valSQL & "Inspector = NULL, "
End If
If Len(Trim(Me.InspectionDate)) > 0 Or Not IsNull(Me.InspectionDate) Then
valSQL = valSQL & "InspectionDate = #" & Me.InspectionDate & "#, "
Else
valSQL = valSQL & "InspectionDate = NULL, "
End If
If Len(Trim(Me.Protest)) > 0 Or Not IsNull(Me.Protest) Then
valSQL = valSQL & "Protest = '" & Me.Protest & "', "
Else
valSQL = valSQL & "Protest = NULL, "
End If
If Len(Trim(Me.OpeningTicket)) > 0 Or Not IsNull(Me.OpeningTicket) Then
valSQL = valSQL & "OpeningTicket = '" & Me.OpeningTicket & "', "
Else
valSQL = valSQL & "OpeningTicket = NULL, "
End If
If Len(Trim(Me.PermitApplication)) > 0 Or Not IsNull(Me.PermitApplication) Then
valSQL = valSQL & "PermitApplication = " & Me.PermitApplication & ", "
Else
valSQL = valSQL & "PermitApplication = NULL, "
End If
If Len(Trim(Me.PermitAppDate)) > 0 Or Not IsNull(Me.PermitAppDate) Then
valSQL = valSQL & "PermitAppDate = #" & Me.PermitAppDate & "#, "
Else
valSQL = valSQL & "PermitAppDate = NULL, "
End If
If Len(Trim(Me.PermitNumber)) > 0 Or Not IsNull(Me.PermitNumber) Then
valSQL = valSQL & "PermitNumber = " & Me.PermitNumber & ", "
Else
valSQL = valSQL & "PermitNumber = NULL, "
End If
If Len(Trim(Me.PermitExpiration)) > 0 Or Not IsNull(Me.PermitExpiration) Then
valSQL = valSQL & "PermitExpiration = #" & Me.PermitExpiration & "#, "
Else
valSQL = valSQL & "PermitExpiration = NULL, "
End If
If Len(Trim(Me.PermitStips)) > 0 Or Not IsNull(Me.PermitStips) Then
valSQL = valSQL & "PermitStips = '" & Me.PermitStips & "', "
Else
valSQL = valSQL & "PermitStips = NULL, "
End If
If Len(Trim(Me.Contractor)) > 0 Or Not IsNull(Me.Contractor) Then
valSQL = valSQL & "Contractor = '" & Me.Contractor & "', "
Else
valSQL = valSQL & "Contractor = NULL, "
End If
If Len(Trim(Me.ContractorDate)) > 0 Or Not IsNull(Me.ContractorDate) Then
valSQL = valSQL & "ContractorDate = #" & Me.ContractorDate & "#, "
Else
valSQL = valSQL & "ContractorDate = NULL, "
End If
If Len(Trim(Me.PavingContractor)) > 0 Or Not IsNull(Me.PavingContractor) Then
valSQL = valSQL & "PavingContractor = '" & Me.PavingContractor & "', "
Else
valSQL = valSQL & "PavingContractor = NULL, "
End If
If Len(Trim(Me.PavingContractorDate)) > 0 Or Not IsNull(Me.PavingContractorDate) Then
valSQL = valSQL & "PavingContractorDate = #" & Me.PavingContractorDate & "#, "
Else
valSQL = valSQL & "PavingContractorDate = NULL, "
End If
If Len(Trim(Me.CoverType)) > 0 Or Not IsNull(Me.CoverType) Then
valSQL = valSQL & "CoverType = '" & Me.CoverType & "', "
Else
valSQL = valSQL & "CoverType = NULL, "
End If
If Len(Trim(Me.NumOfCovers)) > 0 Or Not IsNull(Me.NumOfCovers) Then
valSQL = valSQL & "NumOfCovers = " & Me.NumOfCovers & ", "
Else
valSQL = valSQL & "NumOfCovers = NULL, "
End If
If Len(Trim(Me.VentedCover)) > 0 Or Not IsNull(Me.VentedCover) Then
valSQL = valSQL & "VentedCover = '" & Me.VentedCover & "', "
Else
valSQL = valSQL & "VentedCover = NULL, "
End If
If Len(Trim(Me.ParkingDayRestriction)) > 0 Or Not IsNull(Me.ParkingDayRestriction) Then
valSQL = valSQL & "ParkingDayRestriction = '" & Me.ParkingDayRestriction & "', "
Else
valSQL = valSQL & "ParkingDayRestriction = NULL, "
End If
If Len(Trim(Me.ParkingTimeRestriction)) > 0 Or Not IsNull(Me.ParkingTimeRestriction) Then
valSQL = valSQL & "ParkingTimeRestriction = '" & Me.ParkingTimeRestriction & "', "
Else
valSQL = valSQL & "ParkingTimeRestriction = NULL, "
End If
If Len(Trim(Me.AccountNumber)) > 0 Or Not IsNull(Me.AccountNumber) Then
valSQL = valSQL & "AccountNumber = '" & Me.AccountNumber & "', "
Else
valSQL = valSQL & "AccountNumber = NULL, "
End If
If Len(Trim(Me.ProblemDate)) > 0 Or Not IsNull(Me.ProblemDate) Then
valSQL = valSQL & "ProblemDate = #" & Me.ProblemDate & "#, "
Else
valSQL = valSQL & "ProblemDate = NULL, "
End If
If Len(Trim(Me.CompletedDate)) > 0 Or Not IsNull(Me.CompletedDate) Then
valSQL = valSQL & "CompletedDate = #" & Me.CompletedDate & "#, "
Else
valSQL = valSQL & "CompletedDate = NULL, "
End If
If Len(Trim(Me.Comments)) > 0 Or Not IsNull(Me.Comments) Then
valSQL = valSQL & "Comments = '" & Me.Comments & "', "
Else
valSQL = valSQL & "Comments = NULL, "
End If
valSQL = valSQL & "Completed = " & valCheckChange & ", "
valSQL = valSQL & "last_update = #" & Now() & "#, "
valSQL = valSQL & "last_user = '" & sGetUserName & "', "
valSQL = valSQL & "last_machine = '" & sGetComputerName & "' "
valSQL = valSQL & "where CarNumber = " & Me.CarNumber
Debug.Print valSQL
sql = valSQL
DoCmd.RunSQL sql
Call ClearData
End Sub