VBa sql DELETE String Not Working

[CharlesWhiteman]

Hi All, I'm using the following code which throws up a WHERE error. Am a bit stuck and wonder ifd anyone can point me in the right direction? Thanks?

Dim strSql As String
strSql = "DELETE FROM TblPrimaryDataCorCoursesLink WHERE (TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = Forms.FrmPrimaryData.PrimaryDataID) & WHERE (TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = 1) ;"
DoCmd.RunSQL strSql

 

[MarkK]

You can only have one WHERE clause. Maybe replace your "& WHERE" with "AND"

 

[wazz]

try changing
& WHERE
to
AND

you working with copies of your tables i hope.

 

[wazz]

oh mr. bolt ... missed it by seconds ...

 

[boblarson]

oh mr. bolt ... missed it by seconds ...

Gotta type faster Wazz

 

[wazz]

must be the time zone. yeah.

 

[CharlesWhiteman]

Thanks Guy's for your answers. I've been working on this Db solidly for a week now day and night so its all getting a bit gray! I've rebuilt it normalizing it as far as I can see possible.

The code works but seems to delete all the data from the tbl so will have to look at my code a bit more.

I basically have Clients. Clients can be associated to courses SO;

TblPrimaryData
PrimaryDataID (PK)

TblCourses
CoreCourseID (PK)
CourseName

TblCoreCoursePrimaryDataLink
LinkID (PK)
PrimaryDataIDFK (FK)
CoreCourseIDFK (FK)

So will typically have a primary record per enquiry
PK: 1,2,3,4

Course ID's go from 1 to 9

So if enquiry 1 registers interest in course 1 and course 2 and enquiry 2 registers interest in course 3 & 4 then the link tbl will be

1, 1,2
2,3,4

On a form I have chkboxes and if an interest is removed (OnChange Event) then My code should say delete from the link table where the enquiry id matches the primary recordID and also where the cousxeID matches the CoreCourseID

Thats basically it.

 

[CharlesWhiteman]

I've attached an updated Db which I'm rebuilding but trying to be more careful and simple in approch.

 

[gemma-the-husky]

surely you need a * in there

DELETE * FROM TblPrimaryDataCorCoursesLink WHERE etc

 

[MarkK]

No, you don't need to specify fields in a delete query.

 

[dcb]

Ummmm:

Dim strSql As String
strSql = "DELETE FROM TblPrimaryDataCorCoursesLink WHERE (TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = Forms.FrmPrimaryData.PrimaryDataID) & WHERE (TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = 1) ;"
DoCmd.RunSQL strSql

Dim strSql As String
strSql = "DELETE FROM TblPrimaryDataCorCoursesLink " & _ 
WHERE (TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = " [B]&[/B] Forms.FrmPrimaryData.PrimaryDataID [B]& [/B]") " & _ 
"AND (TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = 1) ;"
[B]Debug.Print strSql  [/B]''Will allow you to see your actual query in the immediate pane of the VBE[B]
DoCmd.Setwarnings False[/B]
DoCmd.RunSQL strSql  ''This is a string it will not evaluate [I]Forms.FrmPrimaryData.PrimaryDataID[/I]
[B]DoCmd.SetWarnings True[/B]

Give that a try

I normally use get my data from the form first and then use it
instead of
.... TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = " & Forms.FrmPrimaryData.PrimaryDataID & ") AND ......

I would do a
Dim lngPDIDFK as long
lngPDIDFK = Forms.FrmPrimaryData.PrimaryDataID
Then
.... TblPrimaryDataCorCoursesLink.PrimaryDataIDFK = " & lngPDIDFK & ") AND ......

Just makes it easier to read complex queries

Cheers

 

[MarkK]

And as an aside, if you use the ...

CurrentDB.Execute "DELETE FROM Table WHERE ID = " & someid

...syntax to execute your SQL statements, you don't have turn warnings off and on. This tidies up your code a little bit.
I would express it like this...

  CurrentDB.Execute _
    "DELETE FROM TblPrimaryDataCorCoursesLink " & _ 
    "WHERE (PrimaryDataIDFK = " & Forms!FrmPrimaryData.PrimaryDataID & ") " & _ 
      "AND (PrimaryDataIDFK = 1);"

And that SQL will only ever do anything if Forms!FrmPrimaryData.PrimaryDataID = 1, which can't be right.