AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk
 asked on

docmd.runsql argument error

I am trying to loop through each client in the tblclients table and for each client delete some records in another table.

I have a string select statement that changes for each client BUT the docmd.runsql method says it needs an argument consisting of an sql string which I though I had passed it

if i add brackets i get the same.
Private Sub BtnRemoveDeletedCWCodesFromClients_Click()
Dim db As Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Dim clientId As Integer
Dim cwcodetounmatch As String
Dim StrSelect As String
Dim strFrom As String
Dim strWhere As String

Dim newsql As String

Dim sqlstr As String





Set rs = db.OpenRecordset("tblclients")

StrSelect = "SELECT * "

With rs
rs.MoveFirst
    Do While Not rs.EOF
   
    Select Case rs.Fields("clientid").Value

        Case 1
            strFrom = "FROM AbiToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = AbiToMvris.[mvris code]))<>False));"
        Case 2
            strFrom = "FROM GlassesToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = GlassesToMvris.[mvris code]))<>False));"
        Case 3
            strFrom = "FROM CW_to_IDS "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CW_to_IDS.[mvriscode]))<>False));"
        Case 4
            strFrom = "FROM CWCode_To_Continental "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Continental.[cwcode]))<>False));"
        Case 5
            strFrom = "FROM HalfordsToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = HalfordsToMvris.[mvriscode]))<>False));"
        Case 6
            strFrom = "FROM CWCode_To_Michelin "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Michelin.[cwcode]))<>False));"
        Case 7
            strFrom = "FROM TechDocToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = TechDocToMvris.[mvris code]))<>False));"
        Case 8
            strFrom = "FROM VividToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = VividToMvris.[mvriscode]))<>False));"
        Case 9
            strFrom = "FROM CapToMvris-CW "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CapToMvris-CW.[mvriscode]))<>False));"

End Select
    
    sqlstr = StrSelect & strFrom & strWhere
    
    DoCmd.RunSQL sqlstr
    
    
    Loop
End With
End Sub

Open in new window

Microsoft Access
Avatar of undefined
Last Comment
PeterBaileyUk
8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
r1sc
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
peter57r
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PeterBaileyUk
ASKER
it was me being stupid I was testing but change the query to a select query which I subsequently discovered docmd.runsql does not run, now its changed to delete ..... its working.
Private Sub BtnRemoveDeletedCWCodesFromClients_Click()
Dim db As Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Dim clientId As Integer
Dim cwcodetounmatch As String
Dim StrSelect As String
Dim strFrom As String
Dim strWhere As String
Dim newsql As String


'Set qd = db.QueryDefs(stringfull)

Dim sqlstr As String
Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("QryDelFromMatchTable")
sqlstr = qd.SQL


'DoCmd.SetWarnings False

Set rs = db.OpenRecordset("tblclients")


' DoCmd.SetWarnings = False
With rs
rs.MoveFirst
    Do While Not rs.EOF
   
    Select Case rs.Fields("clientid").Value

        Case 1
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = AbiToMvris.[mvris code]), * "
            strFrom = "FROM AbiToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = AbiToMvris.[mvris code]))<>False));"
        Case 2
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = GlassesToMvris.[mvris code]), * "
            strFrom = "FROM GlassesToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = GlassesToMvris.[mvris code]))<>False));"
        Case 3
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CW_to_IDS.[mvriscode]), * "
            strFrom = "FROM CW_to_IDS "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CW_to_IDS.[mvriscode]))<>False));"
        Case 4
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Continental.[cwcode]), * "
            strFrom = "FROM CWCode_To_Continental "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Continental.[cwcode]))<>False));"
        Case 5
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = HalfordsToMvris.[mvriscode]), * "
            strFrom = "FROM HalfordsToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = HalfordsToMvris.[mvriscode]))<>False));"
        Case 6
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Michelin.[cwcode]), * "
            strFrom = "FROM CWCode_To_Michelin "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CWCode_To_Michelin.[cwcode]))<>False));"
        Case 7
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = TechDocToMvris.[mvris code]), * "
            strFrom = "FROM TechDocToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = TechDocToMvris.[mvris code]))<>False));"
        Case 8
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = VividToMvris.[mvriscode]), * "
            strFrom = "FROM VividToMvris "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = VividToMvris.[mvriscode]))<>False));"
        Case 9
            StrSelect = "DELETE Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CapToMvris-CW.[mvriscode]), * "
            strFrom = "FROM CapToMvris-CW "
            strWhere = "WHERE (((Exists (Select [mvris code] from QryCWCodesRemoved where QryCWCodesRemoved.[mvris code] = CapToMvris-CW.[mvriscode]))<>False));"

End Select
    
   newsql = StrSelect & strFrom & strWhere
   
'   Debug.Print newsql
'   qd.SQL = newsql
'   DoCmd.OpenQuery ("QryDelFromMatchTable")
    DoCmd.RunSQL newsql
    rs.MoveNext
    Loop
End With
'DoCmd.SetWarnings True
End Sub

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent