Problem with query in VBA (1 Viewer)

BennyLinton · Oct 17, 2018

I'm having a problem with this block of code in a sub, no errors, just not updating my table (complete code included). Thanks!!!:

Code:

        uq = "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = rs.[GCDF No]"
        
        DoCmd.OpenQuery uq

Code:

Private Sub btnEmail_Click()

Dim rs As Recordset
Dim uq As String
Dim oApp As Object

    Set oApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then

         Set oApp = CreateObject("Outlook.Application")
        Started = True
    End If

On Error Resume Next

Set rs = CurrentDb.OpenRecordset("qryContactsEmail", dbReadOnly)
    
  If Not (rs.EOF And rs.BOF) Then
  
  rs.MoveFirst
  
     Do Until rs.EOF = True
         
        Set oItem = oApp.CreateItem(olMailItem)
        With oItem
        
        .From = "GCDF"
        
        .To = rs!fldEmailAddress
    
        .Subject = "Test"
            .Body = "This is a system-generated e-mail, please do not reply"

        .Send
        
        uq = "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = rs.[GCDF No]"
        
        DoCmd.OpenQuery uq
        
            
        End With
        
        rs.MoveNext
        
        Loop
  Else
    MsgBox "There are no records in the recordset."
  End If
    
    rs.Close
    Set rs = Nothing

     Set oItem = Nothing
       If Started Then
           oApp.Quit
    End If
    
End Sub

plog · Oct 17, 2018

Fix your uq variable:

var = "this is inside the variable"

MainString = "This is inside the main string, and " & var & "."

BennyLinton · Oct 17, 2018

You mean something like this which errors?:

Code:

        var = rs.[GCDF No]

            uq = "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = " & var & ""
            
        DoCmd.OpenQuery uq

plog · Oct 17, 2018

Yes like that--of course no need for the var, you can just use rs.[GCDF No]; and no need for the trailing quote marks.

What error?

BennyLinton · Oct 17, 2018

Error is "Method or Data Member not found" pointing to the .[GCDF No]

Code:

            uq = "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = " & rs.[GCDF No] & ""
            
        DoCmd.OpenQuery uq

plog · Oct 17, 2018

That means your Recordset doesn't have any such field in it. Check qryContactsEmail to make sure that field is in it and you've spelled it correctly. It might also just not like that space in its name. I thought it was just rs!FieldName to reference the item like you did with rs!fldEmailAddress

BennyLinton · Oct 17, 2018

qryContactsEmail definitely has [GCDF No], here's the SQL:

SELECT Applicants.[GCDF No], Applicants.toEmail, Applicants.[E-Mail] AS fldEmailAddress, Applicants.[First Name] AS FirstName, Applicants.[Middle Initial] AS MiddleName, Applicants.[Last Name] AS LastName, Applicants.emailed
FROM Applicants
WHERE (((Applicants.toEmail)=True) AND ((Applicants.emailed)=False));

plog · Oct 17, 2018

I would rename it without spaces and reference similar to what you did with fldEmailAddress in your code

BennyLinton · Oct 18, 2018

I can't rename it as there are too many ODBC linked databases dependent on the data structure.

plog · Oct 18, 2018

Then add a new field with a new name but that data.

jsdba · Oct 18, 2018

Try using

Code:

rs.Fields("GCDF No")

I always use rs.Fields when referencing fields from a recordset.

BennyLinton · Oct 18, 2018

I created an alias in my SQL:

Code:

SELECT Applicants.[GCDF No] AS PeopleID, Applicants.toEmail, Applicants.[E-Mail] AS fldEmailAddress, Applicants.[First Name] AS FirstName, Applicants.[Middle Initial] AS MiddleName, Applicants.[Last Name] AS LastName, Applicants.emailed
FROM Applicants
WHERE (((Applicants.toEmail)=True) AND ((Applicants.emailed)=False));

Code:

uq = "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = rs.peopleID "

No errors but the update does not work.

BennyLinton · Oct 18, 2018

I have the UPDATE line right after the .send line so each time it loops through to another record it updates that record:

.Send

uq = "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = rs.peopleID "

DoCmd.OpenQuery uq

jsdba · Oct 18, 2018

BennyLinton said:
I have the UPDATE line right after the .send line so each time it loops through to another record it updates that record:

Try this

Code:

"UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] =" & rs.Fields("peopleID") "

arnelgp · Oct 18, 2018

also try the bang(!)

Code:

rs![GCDF No]

or

rs(0)

and your update query will not work, it needs a Query name not the Query string.
you may however use QueryDefs:

Code:

With Currentdb.CreateQueryDef("", "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = @1")
.Parameters(0)=rs(0)
.Execute
End With

BennyLinton · Oct 18, 2018

This gave a syntax error:

jsdba said:
Try this

Code:

"UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] =" & rs.Fields("peopleID") "

BennyLinton · Oct 18, 2018

Thanks!! This worked!:

arnelgp said:
also try the bang(!)

Code:

rs![GCDF No] or rs(0)

and your update query will not work, it needs a Query name not the Query string.
you may however use QueryDefs:

Code:

With Currentdb.CreateQueryDef("", "UPDATE Applicants set Applicants.Emailed = -1 WHERE Applicants.[GCDF No] = @1") .Parameters(0)=rs(0) .Execute End With

Problem with query in VBA (1 Viewer)

BennyLinton

Registered User.

plog

Banishment Pending

BennyLinton

Registered User.

plog

Banishment Pending

BennyLinton

Registered User.

plog

Banishment Pending

BennyLinton

Registered User.

plog

Banishment Pending

BennyLinton

Registered User.

plog

Banishment Pending

jsdba

Registered User.

BennyLinton

Registered User.

BennyLinton

Registered User.

jsdba

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

BennyLinton

Registered User.

BennyLinton

Registered User.

Similar threads

Users who are viewing this thread