Using a form to filter two tables 1

[arentango]

I'm trying to use a form with checkboxes and a dropdown combobox to filter a table and pull results from a table taht I built a relationship between, and then pull results from one of the columns in that table into an array that I can use as a distribution list to send emails to.

So, I want to be able to have the companies sorted by approvals (checkboxes) and then delivery site (dropdown combobox), and then pull the email addresses from the second table for all of the employees at those companies.

TABLE 1:
Customer ID (relationship between tables) [primary key]
Company info
Approvals
Delivery Sites

TABLE 2:
Customer ID (relationship between tables)
Employee Name
Employee Title
Employee Email address [primary key]

I can't get the form to pull up the table and then query to pull just the reults I want.

Thank you ahead of time for any help!

 

[LittleSmudge]

First off look at the FAQ faq700-2190 about using space characters in Access objects.

So IF I understand you :-
TABLE1.Approvals is a YesNo field
and you have an unbound tick box on the form ( Called say tickApprovals )

TABLE1DeliverySites is a Text(x) field

The combo box ( Called say cboDeliverySites ) lists these.
So the Rowsource must be something like
SELECT DISTINCT DeliverySites FROM TABLE1

So you tick ( or not ) the tickApprovals and you pick a Delivery site from the combo box.


All you then need is a command button that opens a recordset on the result you need
and use SendObjects to send the email.

rst.Open "SELECT Employee " _
       & "FROM TABLE2 INNER JOIN TABLE1 " _
       & "ON TABLE2.CustomerId = TABLE1.CustomerID " _
       & "WHERE Approvals = " & tickApprovals &  " _  
       & "AND DeliverySites = '" & cboDeliverySites & "'"

While Not rst.EOF

    DoCmd.SendObjects .. .. etc
    
    rst.MoveNext
Wend

'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[arentango]

I feel a little dense with Access

Are you using SQL with the SELECT statement, Employee being a table column? Then there are three different approvals that I've used checkboxes to represent Yes (checked), No (unchecked) [each a seaperate column) and then the combobox is for either ALL, Delivery Site 1, or Delivery Site 2.

Can I create a Query that would put the vales all into one query and then use the sendobject for one of the columns in the query table?

 

[LittleSmudge]

Yes,

I'm very confused over your terminology.
Employee is a field within the table ( You said ) so I'm using it in the SELECT statement yes.
The term 'Column' is 'Server Speak - what do you mean in Access terms ?

You now say there are three approvals but you original only listed one in TABLE1. erh ?
Are you really using a YesNo field to store YES and ANOTHER YesNo field to store NO !!!!
What's the other one for - "Don't Knows" ?

How do you store the DeliverySite information in the Schema ? - Is the field DeliverySite a number field 1,2,3 etc ?



You can't use SendObjects on a table or query directly.
You need to index through it an extract the data one record ata time.
However, if you want to lump all of the recipients together and send out onew email then you could modify the above While loop to

Dim strCircList as String
While Not rst.EOF

    strCircList = strCircList & "; " & rst!Employee

    
    rst.MoveNext
Wend
strcircList = Mid(strCircList,2) ' To strip off leading ;

DoCmd.SendObjects .. .. etc

However. Be aware that using this approach means that every recipient will be able to see the email address of every other recipient.



'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.