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