Syntax error? missing operator? sql statement assistance

[Billp]

Hi,

I am trying to delete from a table, before then, inputting new information

I have the following,

Private Sub Cust_Alias_ID_AfterUpdate()
Dim strSQLDelete As String 'SQL Statement to delete any old records
before appending new ones.
Dim strOtherFields As String

strOtherFields = ",LastName,FirstName,ContactName,Title,Region" _
& ",WorkPhone,WorkDDI,WorkExtension,MobilePhone,FaxNumber" _
& ",EmailName,Has_Left,Corp_Brochure,Calendar_Sent,Diary" _
& ",Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up" _
& ",ContactNotes,ContactsInterests,Birthdate,Hometown" _
& ",HomePhone,MaritalStatus,SpouseName,SpousesInterests" _
& ",ChildrenNames"

strSQLDelete = "DELETE [tblCustomerContacts]" _
& "(CustomerID" & strOtherFields & ") " _
& "WHERE '" & CustomerID & "' = '" & Me.CustomerID & "'"


DBEngine(0)(0).Execute strSQLDelete, dbFailOnError

I have tried

"WHERE CustomerID = 'Me!CustomerID'"
"WHERE CustomerID = '" & Me.CustomerID & "'"

To know avail at present

And I am getting currently

error 3075 - Syntax error (missing operator) in query
'[tblCustomerContacts](CustomerID) WHERE "WWWBPW' = "WWWBPW'

The customerID are linked in 2 tables.
On the main form the field "customerID" shows a six digit customer code.
The user can update customer contact lists based on a combo cboCustomerID.
When the user has selected a new contact from the combo I want the old
contacts relating to the current form "CustomerID" to be deleted for the
subtable before the new contacts are added . And do you think for the life of
me I can get the delete part to work?????????????

Ant suggestions gratefully accepted and willingly implemented.
With Thanks
Bill

PS: The weekend is oh so near - just minutes away

 

[[MVP]]

Hi,

I am trying to delete from a table, before then, inputting new information

I have the following,

Private Sub Cust_Alias_ID_AfterUpdate()
Dim strSQLDelete As String      'SQL Statement to delete any old records
before appending new ones.
Dim strOtherFields As String

strOtherFields = ",LastName,FirstName,ContactName,Title,Region" _
    & ",WorkPhone,WorkDDI,WorkExtension,MobilePhone,FaxNumber" _
    & ",EmailName,Has_Left,Corp_Brochure,Calendar_Sent,Diary" _
    & ",Last_Visit_Date,Last_Contact,Enquiry_Status,Follow_Up" _
    & ",ContactNotes,ContactsInterests,Birthdate,Hometown" _
    & ",HomePhone,MaritalStatus,SpouseName,SpousesInterests" _
    & ",ChildrenNames"

strSQLDelete = "DELETE [tblCustomerContacts]" _
                & "(CustomerID" & strOtherFields & ") " _
                & "WHERE '" & CustomerID & "' = '" & Me..CustomerID & "'"

DBEngine(0)(0).Execute strSQLDelete, dbFailOnError

I have tried

"WHERE CustomerID = 'Me!CustomerID'"
"WHERE CustomerID = '" & Me.CustomerID & "'"

To know avail at present

And I am getting currently

error 3075 - Syntax error (missing operator) in query
'[tblCustomerContacts](CustomerID) WHERE "WWWBPW' = "WWWBPW'

The customerID are linked in 2 tables.
On the main form the field "customerID" shows a six digit customer code.
The user can update customer contact lists based on a combo cboCustomerID..
When the user has selected a new contact from the combo I want the old
contacts relating to the current form "CustomerID" to be deleted for the
subtable before the new contacts are added . And do you think for the life of
me I can get the delete part to work?????????????

Ant suggestions gratefully accepted and willingly implemented.
With Thanks
Bill

PS: The weekend is oh so near - just minutes away

Hi,

CustomerID is a Text type field?

It is enough to state only one field to delete whole record. If its
Long Integer type (AutoNumber), then this works (assuming you had
CustomerID field on the form where you run this code):

Dim strSQL AS String
strSQL = "DELETE * FROM tblCustomerContacts WHERE CustomerID = " &
CustomerID
CurrentDB.Execute strSQL

But if its a text type, you can try with:

Dim strSQL AS String
strSQL = "DELETE * FROM tblCustomerContacts WHERE CustomerID Like '" &
CustomerID & "'"
CurrentDB.Execute strSQL

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Douglas J. Steele]

There's no need to list the fields: Delete deletes entire rows.

If CustomerID is text with no apostrophes, use:

strSQLDelete = "DELETE * FROM [tblCustomerContacts] " & _
"WHERE CustomerID = '" & Me.CustomerID & "'"

Exagerated for clarity, that's

strSQLDelete = "DELETE * FROM [tblCustomerContacts] " & _
"WHERE CustomerID = ' " & Me.CustomerID & " ' "

If it's numeric, leave out the single quotes:

strSQLDelete = "DELETE * FROM [tblCustomerContacts] " & _
"WHERE CustomerID = " & Me.CustomerID

Note, too, the space after the closing square bracket around the table name.

 

[Vorlon]

hi, if you r just trying to delete a record, with 1 criteria try this

If MsgBox("Are you sure, you want to Delete this record ?", vbYesNo,
"Info") = vbNo Then
Exit Sub
Else
DoCmd.RunSQL "DELETE suborder.*" & _
" FROM suborder" & _
" WHERE suborderid = " & Me.SubOrderID
Me.Requery
End If