Question Access Run-Time Error 3464 (1 Viewer)

[ronmola]

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 error 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

 

[boblarson]

First, you should use code tags when posting code here so it formats better and easier to read.

Second, change this line:

valSQL = valSQL & "where CarNumber = " & Me.CarNumber

to this:

valSQL = valSQL & "[CarNumber] = " & Me.CarNumber


and by the way:

 

[ronmola]

Thanks for the help and quick response. This kinda worked. The form now allows me to update the table but it now tells me that it is updateing 3 rows. I click yes and then i get Access can't update all the records in the update query. I checked the table and only the correct row updates.

 

[ronmola]

Also I have just noticed that the CarNumber nows changes by itself. This is a manually entered number. When I update the 2nd, 3rd, 4th, etc. row it changes all previous rows to the same info.

 

[ronmola]

I have made the change that was suggested. The form now updates but updates everything that is above therow I want. Also the CarNumber above the row I want to update changes. Any more ideas?

 

[boblarson]

I don't have time to dig deep into your SQL but you need to limit it to the record that is selected, it would seem. So make sure that one of the criteria is the primary key of the record selected.

 

[ronmola]

Bob thanks for the help the problem was I didnt assign a primary key.

 

[boblarson]

That would do it. Glad you figured it out.