Update query fails with error 3219 invalid operation. 2

[Ebenezum]

Sorry if this question has already been answered but I have been unable to find an answer that pertains to my code.
Here it is...I created a select query built on the fly from a field on a table. I need the query to supply information that will be used in an email. I then however want to update another table with a true value. It will mean that I have sent the email to that person. Here is what I have.

strsql = "SELECT tblEmailMaster.[First Name], tblEmailMaster.[Last Name], 
tblEmailMaster.UserType, tblEmailMaster.[Company ID],  
tblEmailMaster.[Email Address], tblFromMasterOfACsToBeEmailed.UserType, 
tblFromMasterOfACsToBeEmailed.[Company ID], tblEmailMaster.ACEmailCheck " & 
_
"FROM tblEmailMaster INNER JOIN tblFromMasterOfACsToBeEmailed ON 
tblEmailMaster.[Company ID] = tblFromMasterOfACsToBeEmailed.[Company ID] " 
& _
"WHERE (((tblEmailMaster.UserType) is not null) AND 
((tblFromMasterOfACsToBeEmailed.[Email Address])='" & varAcEmail & "') AND 
((tblFromMasterOfACsToBeEmailed.[Company ID])='" & varCompanyId & "')) " & 
_
"ORDER BY tblEmailMaster.[Last Name];"

Second sql statement is

strsqll = "UPDATE tblEmailMaster SET tblEmailMaster.Shutdown = True" & _
"WHERE (((tblEmailMaster.[First Name])='" & varOpFirstName & "') AND 
((tblEmailMaster.[Last Name])='" & varOpLastName & "') AND 
((tblEmailMaster.[Company ID])='" & varCompanyId & "'));"

I receive the error here Set rs2 = DB.OpenRecordset(strsqll)

 

[TheAceMan1]

How are ya Ebenezum . . .

The missing space in [red]Red[/red] below will do it:

[blue]strsqll = "UPDATE tblEmailMaster SET tblEmailMaster.Shutdown = True[COLOR=black red] [/color]" & _
"WHERE (((tblEmailMaster.[First Name])='" & varOpFirstName & "') AND 
((tblEmailMaster.[Last Name])='" & varOpLastName & "') AND 
((tblEmailMaster.[Company ID])='" & varCompanyId & "'));"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Woops . . . Hit submitt too soon!

For reference & easier reading, here's a corrected, formatted view of both SQL's:

[blue]   Dim SQL As String
   
   SQL = "SELECT EM.[First Name], " & _
                "EM.[Last Name], " & _
                "EM.UserType, " & _
                "EM.[Company ID], " & _
                "EM.[Email Address], " & _
                "TBE.UserType, " & _
                "TBE.[Company ID], " & _
                "EM.ACEmailCheck " & _
         "FROM tblEmailMaster AS EM " & _
         "INNER JOIN tblFromMasterOfACsToBeEmailed AS TBE " & _
         "ON EM.[Company ID] = TBE.[Company ID] " & _
         "WHERE ((EM.[UserType] Is Not Null) AND " & _
                "(TBE.[Email Address] = '" & varAcEmail & "') AND " & _
                "(TBE.[Company ID])='" & varCompanyId & "')) " & _
         "ORDER BY EM.[Last Name];"

   SQL = "UPDATE tblEmailMaster " & _
         "SET [Shutdown] = True " & _
         "WHERE (([First Name])='" & varOpFirstName & "') AND " & _
                "(t[Last Name])='" & varOpLastName & "') AND " & _
                "([Company ID])='" & varCompanyId & "'));"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Ebenezum]

I am fine AceMan1 and you?

I tried adding the space but it did not work.

The second post is not too clear for me. I see how you broke everything up but it seems the same except that both strings are "SQL =". Does that mean that I should create both queries and put them together into the same string?

I thought I had this answered a few times myself but I keep getting the same error. Driving me crazy.

 

[TheAceMan1]

[blue] . . . it seems the same except that both strings are "SQL =" . . .[/blue]

As I stated the 2nd thread is just for easier reading of the SQL's.

If you look at your post origination you'll see its harder to find/place [blue]SELECT, FROM, INNER JOIN, ON, WHERE, ORDER BY[/blue].

In actuality you only need one variable for SQL. Simulating the single SQL would look something like:

[blue]   Dim db As DAO.Database, SQL As String
   Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
   
   Set db = CurrentDb
   SQL = "SELECT EM.[First Name], " & _
                "EM.[Last Name], " & _
                "EM.UserType, " & _
                "EM.[Company ID], " & _
                "EM.[Email Address], " & _
                "TBE.UserType, " & _
                "TBE.[Company ID], " & _
                "EM.ACEmailCheck " & _
         "FROM tblEmailMaster AS EM " & _
         "INNER JOIN tblFromMasterOfACsToBeEmailed AS TBE " & _
         "ON EM.[Company ID] = TBE.[Company ID] " & _
         "WHERE ((EM.[UserType] Is Not Null) AND " & _
                "(TBE.[Email Address] = '" & varAcEmail & "') AND " & _
                "(TBE.[Company ID])='" & varCompanyId & "')) " & _
         "ORDER BY EM.[Last Name];"
   Set rs1 = db.OpenRecordset(SQL, dbOpenDynaset)
   
   SQL = "UPDATE tblEmailMaster " & _
         "SET [Shutdown] = True " & _
         "WHERE (([First Name])='" & varOpFirstName & "') AND " & _
                "(t[Last Name])='" & varOpLastName & "') AND " & _
                "([Company ID])='" & varCompanyId & "'));"
   Set rs2 = db.OpenRecordset(SQL, dbOpenDynaset)
   [green]'
   other code
   '[/green][/blue]

In any case, try both SQL's above as you see them.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Remou]

As you are running an Update query which returns no records, you need:

DB.Execute strsqll



I hope you will find that once you add the missing space, the above will solve the problem.


-

 

[Ebenezum]

Thanks everyone! I'll try all suggestions. As for now I went the long way and updated a temp table and then updated the the main table by way of external query.
Thanks again. You all are the best.