advice on how to update excisting contact on open form

A

AA

Hello All,

i need advice on how to update excisting contact info on form and save it
with my little experance in access programming i'm having trouble,

here is the form code (was done by a different IT person)

Option Compare Database

Private Sub Find_Click()

End Sub

Private Sub Last_Name_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Last_Name_GotFocus()

End Sub

Private Sub Last_Name_NotInList(NewData As String, Response As Integer)

End Sub

Private Sub Buiilding_BeforeUpdate(Cancel As Integer)

End Sub


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 Product_Department_AfterUpdate()

End Sub

Private Sub Product_Department_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Product_Department_Exit(Cancel As Integer)


End Sub

i'm trying to use this statement
not sure if its correct
please advice

Private Sub Update_Contact_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [Contacts] ( Company, Street, Floor, CityStateZip,
Telephone, Fax, Manager, Email)"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Update Was Completed"

End Sub
 
N

Nikos Yannacopoulos

AA,

Your SQL string is incomplete; you are specifying which table/fields to
append to, but not the values to append! You need to change it to
something like:

Private Sub Update_Contact_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [Contacts] ( Company, Street, Floor, " & _
CityStateZip, Telephone, Fax, Manager, Email) " & _
"SELECT '" & Me!TbxComp & "' AS Expr1, '" & _
Me!TbxStreet & "' AS Expr2, " & _
Me!TbxFloor & " AS Expr3, '" & _
Me!TbxCityStateZip & " AS Expr4, '" & _
Me!Telephone & " AS Expr5, '" & _
Me!TbxFax & " AS Expr6, '" & _
Me!TbxAcctMgr& " AS Expr7, '" & _
Me!TbxEmail & " AS Expr8"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Update Was Completed"

End Sub

In the above I have assumed that all fields are text except Floor, which
I assumed to be numeric. Note how all other control references (except
Me!TbxFloor) are enclosed in single quotes, to denote text. Make any
changes required, if different field types than what I have assumed.

Also, there is a lot of unused stuff in your form module, which probably
just confuses you. Wherever there is a:

Private Sub xxxxxxxxxxxxxxxxx

End Sub

with no code in between, you can just delete it (if in doubt, make a
backup copy first!).

HTH,
Nikos
Hello All,

i need advice on how to update excisting contact info on form and save it
with my little experance in access programming i'm having trouble,

here is the form code (was done by a different IT person)

Option Compare Database

Private Sub Find_Click()

End Sub

Private Sub Last_Name_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Last_Name_GotFocus()

End Sub

Private Sub Last_Name_NotInList(NewData As String, Response As Integer)

End Sub

Private Sub Buiilding_BeforeUpdate(Cancel As Integer)

End Sub


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 Product_Department_AfterUpdate()

End Sub

Private Sub Product_Department_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Product_Department_Exit(Cancel As Integer)


End Sub

i'm trying to use this statement
not sure if its correct
please advice

Private Sub Update_Contact_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [Contacts] ( Company, Street, Floor, CityStateZip,
Telephone, Fax, Manager, Email)"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Update Was Completed"

End Sub
Click to expand...
 
A

AA

thank you Nikos
let me play with your code

AA

Nikos Yannacopoulos said:
AA,

Your SQL string is incomplete; you are specifying which table/fields to
append to, but not the values to append! You need to change it to
something like:

Private Sub Update_Contact_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [Contacts] ( Company, Street, Floor, " & _
CityStateZip, Telephone, Fax, Manager, Email) " & _
"SELECT '" & Me!TbxComp & "' AS Expr1, '" & _
Me!TbxStreet & "' AS Expr2, " & _
Me!TbxFloor & " AS Expr3, '" & _
Me!TbxCityStateZip & " AS Expr4, '" & _
Me!Telephone & " AS Expr5, '" & _
Me!TbxFax & " AS Expr6, '" & _
Me!TbxAcctMgr& " AS Expr7, '" & _
Me!TbxEmail & " AS Expr8"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Update Was Completed"

End Sub

In the above I have assumed that all fields are text except Floor, which
I assumed to be numeric. Note how all other control references (except
Me!TbxFloor) are enclosed in single quotes, to denote text. Make any
changes required, if different field types than what I have assumed.

Also, there is a lot of unused stuff in your form module, which probably
just confuses you. Wherever there is a:

Private Sub xxxxxxxxxxxxxxxxx

End Sub

with no code in between, you can just delete it (if in doubt, make a
backup copy first!).

HTH,
Nikos
Hello All,

i need advice on how to update excisting contact info on form and save it
with my little experance in access programming i'm having trouble,

here is the form code (was done by a different IT person)

Option Compare Database

Private Sub Find_Click()

End Sub

Private Sub Last_Name_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Last_Name_GotFocus()

End Sub

Private Sub Last_Name_NotInList(NewData As String, Response As Integer)

End Sub

Private Sub Buiilding_BeforeUpdate(Cancel As Integer)

End Sub


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 Product_Department_AfterUpdate()

End Sub

Private Sub Product_Department_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Product_Department_Exit(Cancel As Integer)


End Sub

i'm trying to use this statement
not sure if its correct
please advice

Private Sub Update_Contact_Click()

Dim QrySQL As String
QrySQL = "INSERT INTO [Contacts] ( Company, Street, Floor, CityStateZip,
Telephone, Fax, Manager, Email)"
DoCmd.SetWarnings False
DoCmd.RunSQL QrySQL
DoCmd.SetWarnings True
MsgBox "Update Was Completed"

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update records, client contact infomation 0
SQL update record not working 4
How to: update existing contact info on form 1
Clear Button does not work 4
Tracking changes to a form 3
Switchboard form no longer fills 1
Before Update event 3
Access 2007 "no current record" on split form 1

Top