SQL update record not working

[Guest]

Hello All
an unbound contact form with an update command button, which is not updating
records in my contact table, when i select the contact name on the form it
shows the name with all the data (City, email, phone, floor, etc..)
but not updating,
DB was done by a different IT person, i'm trying to modify it
novice programmer,
please advice,
here my form code...
Option Compare Database
Private Sub cboContact_AfterUpdate()

Dim ContactID As String
Dim rst As DAO.Recordset
ContactID = "SELECT * FROM Contacts " & " WHERE ContactName = '" _
& Me!cboContact.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactID, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!Telephone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!Email
Me!TbxAcctMgr = rst!Manager
End If

Set rst = Nothing

End Sub
Private Sub Close_Click()
DoCmd.Close
End Sub
Private Sub Form_Load()

'Set Record Source for Forms as Contacts Table
Forms!Contacts.RecordSource = "Contacts"

End Sub
Private Sub Update_Click()

Dim QrySQL As String

On Error GoTo Err_Handler

QrySQL = "SELECT * FROM Contacts " & " WHERE ContactName = '" _
& Me.cboContact.Value & "', " & _
"SET Company = '" & Me.TbxComp & "', " & _
" Street = '" & Me.TbxStreet & "', " & _
" Floor = '" & Me.TbxFloor & "', " & _
" CityStateZip = '" & Me.TbxCityStateZip & "', " & _
" Telephone = '" & Me.Telephone & "', " & _
" Fax = '" & Me.TbxFax & "', " & _
" Manager = '" & Me.TbxAcctMgr & "', " & _
" Email = '" & Me.TbxEmail & "' " & _
" UPDATE Contacts " & " ' "

txtValue = Me.cboContact

MsgBox QrySQL, , "Testing"

' DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
' DoCmd.SetWarnings True


Err_Handler:
If Err.Number = 2501 Then
Exit Sub
End If

MsgBox "Update Was Completed !!!"

End Sub

 

[Rick Brandt]

Hello All
an unbound contact form with an update command button, which is not updating
records in my contact table, when i select the contact name on the form it
shows the name with all the data (City, email, phone, floor, etc..)
but not updating,
DB was done by a different IT person, i'm trying to modify it
novice programmer,
please advice,
here my form code...
Option Compare Database
Private Sub cboContact_AfterUpdate()

Dim ContactID As String
Dim rst As DAO.Recordset
ContactID = "SELECT * FROM Contacts " & " WHERE ContactName = '" _
& Me!cboContact.Value & "'"
Set rst = CurrentDb.OpenRecordset(ContactID, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
Call MsgBox("The Contact Name you entered does not exist")
Else
Me!Telephone = rst!Telephone
Me!TbxFax = rst!Fax
Me!TbxComp = rst!Company
Me!TbxFloor = rst!Floor
Me!TbxStreet = rst!Street
Me!TbxCityStateZip = rst!CityStateZip
Me!TbxEmail = rst!Email
Me!TbxAcctMgr = rst!Manager
End If

Set rst = Nothing

End Sub
Private Sub Close_Click()
DoCmd.Close
End Sub
Private Sub Form_Load()

When editing data in a RecordSet you need to first have a line at the
beginning...

rst.Edit

....and then a line at the end...

rst.Update

This would be better done with a simple update query in my opinion. It is
seldom necessary to use a RecordSet for doing updates.

 

[Douglas J. Steele]

The SQL you're trying to use is invalid.

Update queries have a syntax of:

UPDATE table
SET newvalue
WHERE criteria;

Your code should be something like:

QrySQL = " UPDATE Contacts "
"SET Company = '" & Me.TbxComp & "', " & _
" Street = '" & Me.TbxStreet & "', " & _
" Floor = '" & Me.TbxFloor & "', " & _
" CityStateZip = '" & Me.TbxCityStateZip & "', " & _
" Telephone = '" & Me.Telephone & "', " & _
" Fax = '" & Me.TbxFax & "', " & _
" Manager = '" & Me.TbxAcctMgr & "', " & _
" Email = '" & Me.TbxEmail & "' " & _
" WHERE ContactName = '" _
& Me.cboContact.Value & "'"

 

[Guest]

thank you guys,
question?
what if i have Two contacts with the same name? would it update both of it?

 

[Guest]

Rick Brandt

can you please expand on your suggestion, or give few examples?

Thank you

 

[Rick Brandt]

Rick Brandt

can you please expand on your suggestion, or give few examples?

In your original post you need...

rst.Edit

....right after the Else and...

rst.Update

....right before the End If

Setting field values in an open Recordset does nothing without including these
lines of code.