Updating Records Issue

[graviz]

I'm attemping to update a record in a table "Com_MDU_Table" using a form with a bunch of txt and combo boxes. The record is based upon the Property Name. I have it set as the primary key in my table so it does not create duplicate records of the same property. I've attached the error I'm getting when I try and change one of the other fields and click on the update button (code is below).

Private Sub CMD_Update_Click()
Dim CDB As Object
Dim RS_CPT As Object
Dim PropertyName As String
Dim ActivityStatus As String
Dim RTM As String
Dim Address As String
Dim City As String
Dim State As String
Dim Zip As String
Dim ASM As String
Dim PropType As String
Dim NewExisting As String
Dim NumOfUnits As String
Dim NumOfBuildings As String
Dim NumOfFloors As String
Dim SystemType As String
Dim HSD As String
Dim UserName As String
UserName = fOSUserName
PropertyName = Forms!frm_update_prop!TXT_Prop_Name.Value
ActivityStatus = Forms!frm_update_prop!TXT_Act_Status.Value
RTM = Forms!frm_update_prop!TXT_RTM.Value
Address = Forms!frm_update_prop!TXT_Address.Value
City = Forms!frm_update_prop!TXT_City.Value
State = Forms!frm_update_prop!TXT_State.Value
Zip = Nz(Forms!frm_update_prop!TXT_Zip.Value)
ASM = Forms!frm_update_prop!TXT_ASM.Value
PropType = Forms!frm_update_prop!TXT_Prop_Type.Value
NewExisting = Forms!frm_update_prop!TXT_New_Exist.Value
NumOfUnits = Forms!frm_update_prop!TXT_Num_Units.Value
NumOfBuildings = Forms!frm_update_prop!TXT_Num_Build.Value
NumOfFloors = Forms!frm_update_prop!TXT_Num_Floors.Value
SystemType = Forms!frm_update_prop!TXT_Sys_Type.Value
HSD = Forms!frm_update_prop!TXT_HSD.Value
Set CDB = CurrentDb
Set RS_CPT = CDB.OpenRecordset("Com_MDU_Table")


RS_CPT.Edit
RS_CPT("Property_Name").Value = PropertyName
RS_CPT("Account_Status").Value = ActivityStatus
RS_CPT("RTM").Value = RTM
RS_CPT("Address").Value = Address
RS_CPT("City").Value = City
RS_CPT("State").Value = State
RS_CPT("Zip").Value = Zip
RS_CPT("ASM").Value = ASM
RS_CPT("Property_Type").Value = PropType
RS_CPT("New_Existing").Value = NewExisting
RS_CPT("Num_of_Units").Value = NumOfUnits
RS_CPT("Num_of_Buildings").Value = NumOfBuildings
RS_CPT("Num_of_Floors").Value = NumOfFloors
RS_CPT("System_Type").Value = SystemType
RS_CPT("HSD").Value = HSD
RS_CPT("NT_Login").Value = UserName
RS_CPT.Update

Set RS_CPT = Nothing
Set CDB = Nothing

MsgBox "The property" & " " & PropertyName & " " & "has been updated", vbOKOnly, "Record Updated"

End Sub

 

[pbaldy]

As is, you're opening the recordset on the whole table, so the code is trying to update the first record, which is presumably not the one you're on. The commented out SQL version was probably closer to what you need.

 

[graviz]

As is, you're opening the recordset on the whole table, so the code is trying to update the first record, which is presumably not the one you're on. The commented out SQL version was probably closer to what you need.

I tried it with the SQL and had the same error. Any idea how to adjust my code to get it to work?

 

[pbaldy]

Looks like you edited the post to get rid of the SQL. That would be the way it has to be done, otherwise you're always editing the first record in the table. You have to open the recordset to the correct record. Post the SQL method that gave you the error and we'll see if we can't find the problem.

 

[graviz]

Looks like you edited the post to get rid of the SQL. That would be the way it has to be done, otherwise you're always editing the first record in the table. You have to open the recordset to the correct record. Post the SQL method that gave you the error and we'll see if we can't find the problem.

Dim MySQL as String

MySQL = "SELECT Com_MDU_Table.* FROM Com_MDU_Table WHERE (((Com_MDU_Table.Property_Name)=[forms]![frm_update_prop]![txt_prop_name]));"

I still receive the same error with this.

 

[pbaldy]

Presuming that's a text field, try

MySQL = "SELECT * FROM Com_MDU_Table WHERE Property_Name='" & [forms]![frm_update_prop]![txt_prop_name] & "'"

 

[graviz]

Presuming that's a text field, try

MySQL = "SELECT * FROM Com_MDU_Table WHERE Property_Name='" & [forms]![frm_update_prop]![txt_prop_name] & "'"

No dice. I'm still getting the Runtime 3022 error. I did run the sql from the immediate window and paste that sql in a blank query. When I ran it it seemed to work. It displayed the correct single record. Not sure why I'm still getting the error.

 

[pbaldy]

Can you post the db here? Or at least the full code as it is now?

 

[graviz]

Can you post the db here? Or at least the full code as it is now?

The db is fairly large. Here is the code for the button as it stands right now:

Private Sub CMD_Update_Click()
Dim CDB As Object
Dim RS_CPT As Object
Dim PropertyName As String
Dim ActivityStatus As String
Dim RTM As String
Dim Address As String
Dim City As String
Dim State As String
Dim Zip As String
Dim ASM As String
Dim PropType As String
Dim NewExisting As String
Dim NumOfUnits As String
Dim NumOfBuildings As String
Dim NumOfFloors As String
Dim SystemType As String
Dim HSD As String
Dim UserName As String
Dim MySQL As String
UserName = fOSUserName
PropertyName = Forms!frm_update_prop!TXT_Prop_Name.Value
ActivityStatus = Forms!frm_update_prop!TXT_Act_Status.Value
RTM = Forms!frm_update_prop!TXT_RTM.Value
Address = Forms!frm_update_prop!TXT_Address.Value
City = Forms!frm_update_prop!TXT_City.Value
State = Forms!frm_update_prop!TXT_State.Value
Zip = Nz(Forms!frm_update_prop!TXT_Zip.Value)
ASM = Forms!frm_update_prop!TXT_ASM.Value
PropType = Forms!frm_update_prop!TXT_Prop_Type.Value
NewExisting = Forms!frm_update_prop!TXT_New_Exist.Value
NumOfUnits = Forms!frm_update_prop!TXT_Num_Units.Value
NumOfBuildings = Forms!frm_update_prop!TXT_Num_Build.Value
NumOfFloors = Forms!frm_update_prop!TXT_Num_Floors.Value
SystemType = Forms!frm_update_prop!TXT_Sys_Type.Value
HSD = Forms!frm_update_prop!TXT_HSD.Value
Set CDB = CurrentDb
Set RS_CPT = CDB.OpenRecordset("Com_MDU_Table")

MySQL = "SELECT * FROM Com_MDU_Table WHERE Property_Name='" & [Forms]![frm_update_prop]![TXT_Prop_Name] & "'"

RS_CPT.Edit
RS_CPT("Property_Name").Value = PropertyName
RS_CPT("Account_Status").Value = ActivityStatus
RS_CPT("RTM").Value = RTM
RS_CPT("Address").Value = Address
RS_CPT("City").Value = City
RS_CPT("State").Value = State
RS_CPT("Zip").Value = Zip
RS_CPT("ASM").Value = ASM
RS_CPT("Property_Type").Value = PropType
RS_CPT("New_Existing").Value = NewExisting
RS_CPT("Num_of_Units").Value = NumOfUnits
RS_CPT("Num_of_Buildings").Value = NumOfBuildings
RS_CPT("Num_of_Floors").Value = NumOfFloors
RS_CPT("System_Type").Value = SystemType
RS_CPT("HSD").Value = HSD
RS_CPT("NT_Login").Value = UserName
RS_CPT.Update

Set RS_CPT = Nothing
Set CDB = Nothing

MsgBox "The property" & " " & PropertyName & " " & "has been updated", vbOKOnly, "Record Updated"

End Sub

 

[pbaldy]

You're not using the SQL, you're still opening the recordset on the table. Move the line setting MySQL just above this one and change this one to:

Set RS_CPT = CDB.OpenRecordset(MySQL)

 

[graviz]

You're not using the SQL, you're still opening the recordset on the table. Move the line setting MySQL just above this one and change this one to:

Set RS_CPT = CDB.OpenRecordset(MySQL)

I knew I was missing something. It works perfectly now. Thanks!

 

[pbaldy]

No problem, glad we got it sorted out. Love the Einstein quote by the way, and it's so true!