Dim strAddress As String? 1

[cstuart79]

I am attempting to use checkboxes ("Me.CONTACTS.Group Email") to select specific email addresses ("Me.CONTACTS.Email_1") which will then all be added to a message upon clicking a button ("CommandEmail"). The following code gives me an error ('438' Object doesn't support this property or method). Any help here please?


Private Sub CommandEmail_Click()
Dim strHighAddress As String

If Me.[CONTACTS].[Group Email].Value = True Then
strHighAddress = Me.[CONTACTS].[Email__1].Value
Application.FollowHyperlink "mailto:" & "" & strAddress & ""
End If
End Sub

 

[dhookom]

This expression should be part of your code something like:

Private Sub CommandEmail_Click()
  Dim strHighAddress As String
  strHighAddress = Concatenate(...)
  Application.FollowHyperlink "mailto:" & "" & strHighAddress & ""
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

Generates compile error: sub or function not defined.

 

[dhookom]

Did you copy the Concatenate() function into a new blank module and save the module with an appropriate name?

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

moved Concatenate function () to new blank module and get "run-time error 3145 syntax error in WHERE clause" with the following highlighted:

set rs = db.openrecordset (pstrSQL)

 

[randy700]

Make sure your funtion and module do not have the same name.

Randy

 

[cstuart79]

function is named "Concatenate"
module is named "modConcatenate

 

[dhookom]

Share your code, table/field names, and data types.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

tblCLIENTS: main table
- CommandEmail: command button that sits on main table
- Concatenate: function
- modConcatenate: blank module

tblCONTACTS: table containing all contact info fields
- EmailAddress: text field containing email address
- GroupEmail: yes/no field used to select specific email address


Private Sub CommandEmail_Click()
Dim strHighAddress As String
strHighAddress = Concatenate("SELECT EmailAddress FROM tblCONTACTS WHERE GroupEmail = True", ";")
Application.FollowHyperlink "mailto:" & "" & strHighAddress & ""
End Sub


Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pstrLastDelim As String = "") _
As Variant
' Created by Duane Hookom, 2003
' this code may be included in any application/mdb providing
' this statement is left intact
' example
' tblFamily with FamID as numeric primary key
' tblFamMem with FamID, FirstName, DOB,...
' return a comma separated list of FirstNames
' for a FamID
' John, Mary, Susan

' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
' FROM tblFamily
' ============================================

' to get a return like Duane, Laura, Jake, and Chelsey

' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID], ",",", and ") as FirstNames
' FROM tblFamily
' ============================================

' If FamID is a string rather than numeric,
' it will need to be delimited with quotes

' ======= in a Query =========================
' SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =""" & [FamID] & """", ",",", and ") as FirstNames
' FROM tblFamily
' ============================================


'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'length before last concatenation
Dim intLenB4Last As Integer
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
intLenB4Last = Len(strConcat)
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
If Len(pstrLastDelim) > 0 Then
strConcat = Left(strConcat, _
intLenB4Last - Len(pstrDelim) - 1) _
& pstrLastDelim & Mid(strConcat, intLenB4Last + 1)
End If
End If
If Len(strConcat) > 0 Then
Concatenate = strConcat
Else
Concatenate = Null
End If
End Function

 

[dhookom]

What do you see if you type this into the SQL view of a new, blank query:

SELECT EmailAddress FROM tblCONTACTS WHERE GroupEmail = True;

What do you see if you enter this into the debug window (press Ctrl+G)

? Concatenate("SELECT EmailAddress FROM tblCONTACTS WHERE GroupEmail = True", ";")

Isn't there a ClientID or similar field in tblCONTACTS?


Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

ahhhh...found a typo. debug helped..thanks duane!
now to have the value of all entries in the "GroupEmail" yes/no field set to "no/false/null" after the function is performed, do i just add a statement such as the following:

Set GroupEmail.Value = Null

 

[dhookom]

I would create and save an update query that would do this. Then run the query once the command has concluded.

Duane
Hook'D on Access
MS Access MVP

 

[cstuart79]

thanks a mil duane!