emails from database via VBA script

[mish]

Set rs = db.OpenRecordset("SELECT ID, Firstname, Surname, emailaddress" & _
"FROM Members" & _
"ORDER BY ID;")

error 91 'Object var with block var not set' on compile

 

[mish]

i also need to include a where condition in one of the queries so the statment will be for the month in the between date below, which query should this go into? The field is tdate and it is on the Sales table btw.

WHERE tdate between #11/01/2012 00:00:000# and #11/30/2012 00:00:000#

 

[CJ_London]

Mish,

The error message implies you have not set db - have you got the line

set db = currentdb

above this error line? Also it looks like you might be missing a space before the FROM (don't like " &_)

To include the date filter

Set rs = db.OpenRecordset("SELECT ID, Firstname, Surname, emailaddress" & _
" FROM Members WHERE tdate between #11/01/2012# and #11/30/2012# ORDER BY ID;")

Note: dates enclosed by # need to be in american format which is what you have so no problem but just to be aware...

 

[mish]

yep, I'm all too aware abount the American date issue, i spent soooo long stuck on it.

 

[mish]

Hello

The two lines of the script which continue to throw up errors are

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate From Sales WHERE ID=" & ID)

syntax error missing operator in query expression 'ID='

and

Set rs = db.OpenRecordset("SELECT ID, Firstname, Surname, emailaddress" & _
" FROM Members WHERE tdate between #11/01/2012# and #11/30/2012# ORDER BY ID;")

The above seems to be a problem since tdate doesn't exist in the members' table the error it returns is run time 3036 too few parameters, Expected 1. Once I take the reference to the WHERE tdate the compiler points to the first problematic query.

 

[CJ_London]

OK,

I don't have enough information to resolve this at the moment. I suspect you need to replace some things like ID and TDate with what you actually have in your tables.

Can you provide me with the details of the fields in both sales and members tables - I need to know the name of each field and its datatype (text, date,number etc).

Can you also supply the code you now have in full for emailbodfunc and emailaddressfunc

 

[mish]

Public Function emailbodfunc()

Dim rs As Recordset
Dim db As Database
Dim email As String
Dim BodyStr As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate From Sales WHERE ID=" & ID)
BodyStr = ""

While Not rs.EOF
BodyStr = BodyStr & rst.Fields(0) & "|" & rs.Fields(1) & "|" & rs.Fields(2) & vbCrLf
rs.MoveNext
Wend

If BodyStr <> "" Then BodyStr = "ID " & "Item " & "|" & "Quantity " & vbCrLf & BodyStr 'add a header
emailbodfunc = BodyStr
Set rs = Nothing
Set db = Nothing
End Function

Public Sub emailaddressfunc()

Dim rs As Recordset
Dim db As Database
Dim BodyStr As String
Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT ID, Firstname, Surname, emailaddress" & _
" FROM Members WHERE tdate between #11/01/2012# and #11/30/2012# ORDER BY ID;")

While Not rs.EOF
BodyStr = emailbodfunc(rs.Fields("ID"))
If BodyStr <> "" Then DoCmd.SendObject acSendNoObject, Null, Null, rs.Fields("emailaddress"), Null, Null, "Test subject", BodyStr
rs.MoveNext
Wend

Set rs = Nothing
Set db = Nothing
End Sub

Table 1 'Members'

'ID' AutoNumber
'Firstname' Text
'Surname' Text
'emailaddress' Text
'Title' Text

Table 2 'Sales'

'ID' Autonumber
'MembersID' Number
'bfast' Currency
'lunch' Currency
'desert' Currency
'dwine' Currency
'table' Currency
'bar' Currency
'cellar' Currency
'monthT' Currency (was going to sum monthly total)
'tdate' Date/Time

 

[pbaldy]

The first error indicates that it's not finding a value for ID. Is it on the form? Where are you expecting it to get the value to filter by?

 

[mish]

Both tables have the standard ID field which all . I am not using any forms, just datasheet view as an input method.

 

[pbaldy]

Then the code has no idea what you're referring to (which ID you want).

 

[mish]

Both tables have the standard ID field which all access tables have by design . I am not using any forms, just datasheet view as an input method.

 

[CJ_London]

There are two problems:

Your function is not properly defined - I advised way back when that it should be:

Public Function emailbodfunc(MemID as Long) as string

and your recordset should be:

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate From Sales WHERE MembersID=" & MemID)

I originally advised:

Private Function BuildBody[COLOR=red](MemID as long) as string[/COLOR]
Dim MyDb as Database
Dim Rst as Recordset
Dim BodyStr as string
 
set mydb=currentdb
set rst=mydb.openrecordset("SELECT ID, ItemName, Quantity From Sales [COLOR=red]WHERE MemID=" & MemID[/COLOR]) ' change to what you want to report and from what table(s) - try creating in query builder then copy th text to here

 

[mish]

Hello CJ

I made all the changes you suggested and it's still stuck on this line. This includes a declaration of MemID as long at the top of the function.

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM Sales WHERE MemID=" & MemID)

Run- time error '3061'. Too few parameters expected 1.

Thanks again for helping with this.

 

[mish]

I also tried this advice from an earlier post of yours as a possible solution to the error

WHERE memID= '" & memID & "'"

 

[CJ_London]

Mish,

You really must read what I have written - see highlighted in red below

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate From Sales WHERE [B][I][U][COLOR=red]MembersID[/COLOR][/U][/I][/B]=" & MemID

)

 

[mish]

Hello CJ

I have added the code you highlighted in red and it still falls over with the same error message, below is the script thus far. Can you help me understand what this end of the SQL statement is suppose to do and perhaps I can help you to help me. I can see that it's selecting the fields bfast to tdate from the sales table into a recordset called rs the WHERE condition with MembersID=" & MemID I don't get.

"SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID=" & MemID)


Public Function emailbodfunc(MemID As Long) As String

Dim rs As Recordset
Dim db As Database
Dim email As String
Dim BodyStr As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID=" & MemID)
BodyStr = ""


While Not rs.EOF
BodyStr = BodyStr & rst.Fields(0) & "|" & rs.Fields(1) & "|" & rs.Fields(2) & vbCrLf '(linefeed) appreciate only test data but suggest using pipes since spaces could get lost if text either side has spaces -

also means it can easily posted into another db
rs.MoveNext
Wend

If BodyStr <> "" Then BodyStr = "ID " & "Item " & "|" & "Quantity " & vbCrLf & BodyStr 'add a header
emailbodfunc = BodyStr

Set rs = Nothing
Set db = Nothing
End Function



Public Sub emailaddressfunc()

Dim rs As Recordset
Dim db As Database
Dim BodyStr As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Firstname, Surname, emailaddress,ID FROM members;") 'need to add an order by condition

While Not rs.EOF
BodyStr = emailbodfunc(rs.Fields("ID"))
If BodyStr <> "" Then DoCmd.SendObject acSendNoObject, Null, Null, rs.Fields("emailaddress"), Null, Null, "Test subject", BodyStr
rs.MoveNext
Wend

Set rs = Nothing
Set db = Nothing
End Sub

 

[CJ_London]

Hi Mish

Please can you put the code in code boxes, makes it easier to read - use the advanced method of replying

Can you confirm this is what you have in table 2 and the spelling is exactly right

Table 2 'Sales'

'[COLOR=black]ID[/COLOR]' Autonumber
'[COLOR=black]M[/COLOR][COLOR=black]embersID[/COLOR]' Number
'bfast' Currency
'lunch' Currency
'desert' Currency
'dwine' Currency
'table' Currency
'bar' Currency
'cellar' Currency
'monthT' Currency (was going to sum monthly total)
'tdate' Date/Time

This part of the sql statement refers to these fields

SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID="

and they are all there

MemID refers to

Public Function emailbodfunc([COLOR=red]MemID[/COLOR] As Long) As String

SO it should all work. I have just noticed you have used 'table' as a field name which is a reserved word so try changing it to RTable

SELECT ID,bfast,lunch,dwine,bar,desert,cellar,[COLOR=red]RTable[/COLOR],tdate FROM sales WHERE MembersID="

Table 2 'Sales'

'[COLOR=black]ID[/COLOR]' Autonumber
'[COLOR=black]M[/COLOR][COLOR=black]embersID[/COLOR]' Number
'bfast' Currency
'lunch' Currency
'desert' Currency
'dwine' Currency
[COLOR=red]'RTable[/COLOR]' Currency
'bar' Currency
'cellar' Currency
'monthT' Currency (was going to sum monthly total)
'tdate' Date/Time

Give it another go!

 

[mish]

Yes that is an accurate description of the Sales table and I made the changes you suggested and it now runs past that part of the function.

It now falls over at this point - with an object not found error 424


BodyStr = BodyStr & rst.Fields(0) & "|" & rs.Fields(1) & "|" & rs.Fields(2) & vbCrLf
Thanks for the help so far.

 

[CJ_London]

Hi Mish,

Please can you post what you have for the entire emailbodfunc function - I suspect you have missed something above

 

[pbaldy]

Based on your posted code, you declare and set rs then use rst.