filter to VBA for email addresses (1 Viewer)

[mdemarte]

I have a form on which I find vendors. The form is called frmVendorFind and the record source is qryVendorFind. I have many fields on the form, which I might use to filter the form, such as a vendor type (vendtype) and mailing city (mailcity). Once I have filtered the form for the records that I want, then I want to gather the email address (email1) of each record, separate them by commas and put them into a Lotus notes e-mail as the ‘To’. The trick seems to be in getting the VBA code to recognize the filter. I am using Access 2000.

I adapted notes from wh00t from 12/3/02 to get the following, which gives me the e-mail address of the first record:

A button on the form whose on-click event is

SendMail Me![email1]

And then a Public Procedure -

Public Sub SendMail(strEmail As String)

Dim objNotesWS As Object
Dim notesdb As Object

Set objNotesWS = CreateObject("Notes.NotesUIWorkspace")
Set notesdb = objNotesWS.COMPOSEDOCUMENT(, , "memo")

notesdb.FIELDSETTEXT "EnterSendTo", strEmail

Set objNotesWS = Nothing
Set notesdb = Nothing

End Sub

TIA

 

[jal]

Personally i would construct a query like this:
Dim SQL as string
SQL = "SELECT * FROM tblVendors WHERE "
if Len(vendtype & "") > 0 then SQL = SQL & "vendType = @vendType AND "
'Do the same for each field / filter - just repeat the above
'When you're done, remove the last AND
if Right(SQL, 5) = " AND " Then
SQL = Left(SQL, Len(SQL) - 5)
End if
'Remove the WHERE if the user did not filter
if Right(SQL, 7) = " WHERE " Then
SQL = Left(SQL, Len(SQL) - 7)
End if
dim qDef as DAO.QueryDef
on error resume next
Set qDef = CurrentDb.QueryDefs("qryFilter")
on error goto 0
if qDef is nothing then set qdef = CurrentDb.CreateQueryDef("qryFilter")
qDef.SQL = SQL
'Now add the parameter values
if Len(vendtype & "") > 0 then parameters("@vendType") = vendType
'Do the same for each param.
'Now you can loop through your recordset to build your email
dim rs as DAO.RecordSet
set rs = qDef.OpenRecordset
Do until rs.EOF
Dim emailAddress as string
emailAddress = rs("Email")
.....
rs.MoveNext
Loop
'To make the subform show the results onscreen
Set Me.subform1.Form.RecordSet = RS

 

[mdemarte]

So, if I had 100 fields on this find screen, then I would need to put in SQL code for each one?

 

[jal]

So, if I had 100 fields on this find screen, then I would need to put in SQL code for each one?

I didn't realize you had so many columns in your table. You might be able to build your SQL in a loop, for instance if you name each of your textboxes after the column name.

Dim ctrl as Variant
For each ctrl in Me.Controls
If ctrl.ControlType = Access.acTextbox Then
SQL = SQL & ctrl.Name & " = @" & ctrl.Name & " AND "
end if
Next ctrl

There's no easy way to do this but you might be able to get it to work.

 

[Rabbie]

So, if I had 100 fields on this find screen, then I would need to put in SQL code for each one?

If you have 100 fields on your form it is not going to be very user-friendly. If you have 100 fields in the table then perhaps you need to look carefully at your design to see if you have got it right. That many fields suggests a normalization issue.

 

[mdemarte]

I work for County Government - which means LOTS of State and Federal forms to fill out and/or info to track. So, yes, there very well could be 100 fields on a form - not in one table Rabbie - but a parent table linked to child tables. For example, imagine a daily ticket tracking the work on a road which includes the portion of the road, the equipment used, the personnel and the supplies. Also, if the form is broken down into sections of data with related fields and it is all labelled then it is quite user-friendly.

Every database that I have worked on so far, I learn something in one that I use in the next one. So, this particular form might only have 35-40 fields, but I am looking ahead to the next one. Imagining having to change the SQL if a field is added and imagining the time it would take to make sure ALL of the fields are spelled and tested correctly. That's why I feel there has to be a better way and if not say

 

[Rabbie]

Mdemarte, I hear what you are saying. Sorry I got the impression from Post 4 that the fields were all in one table.

 

[mdemarte]

I finally cracked the last piece this morning. Not sure if this is the best way to do this, but here is one way.

In order to get the filter into VBA, it had to be saved. So, while on my form, I ran Filter by Form, then File, Save Query As and called it qryFilter.

Next, I setup a macro to do this for the user. I used SendKeys, even though some Access forums suggest avoiding it:

mcrSaveQuery

Set Warnings No
RunCommand FilterbyForm
Set Warnings No
SendKeys qryFilter{Enter}
Set Warnings No
RunCommand SaveAsQuery
Set Warnings No
RunCommand ApplyFilterSort
Set Warnings Yes

On my form, I created a button. On Click or Double-click, the button runs VBA code (which runs the macro first).

So, the user runs Filter by Form and finds the data that they want. Then, they click the button, which updates the qryFilter, then runs the VBA Code to pull in the filter and apply it to the recordset:

Set rst = db.OpenRecordset("qryFilter", dbOpenSnapshot)

Then it loops thru the records and pulls in the e-mail addresses, skipping the blank ones.