Reference to an external table from a form

[Linus_99]

Help on the following would be appreciated:

I have a query which returns a list of customer_ID, name, address etc (several hundred records).

I use a form to display the data fields from the query, & it works OK.

There is a table that lists about 50 customer_IDs for customers who do not wish to be contacted.

My problem is that I would like to include a text box on the form that indicates whether a customer is to be contacted or not.

The logic for the text box is basically . . .

For the current record on the form:
Look up the customer_ID in the contact table;
If the customer_ID exists in that table, then set the text box to "don't contact"
If the customer_ID does not exist in that table, set the text box to "OK to contact".

Is this achievable with the expression builder, or with VBA ? Any code would be welcome.

Thanks

 

[Tanis]

Wouldn't it be simpler if you had all the contacts in one table? You could then have a yes/no field to pick out the customers who do not want to be contacted.

 

[Russell Hauf]

You can just bring the other table into your query. When you join the ID fields, click on the line between the 2 tables. Join so that all the records from your original query tables are shown, and only those from the "do not call" table that match are shown. You could also create a new query based on your current query and the "do not call" table, joining as I said above. Base your form on this new query.

Then you can put your check box (or better yet, a label that shows up when the "do not call" field is true and is hidden when false or missing).

Let me know if you need more help,

Russell
This message was edited by Russell Hauf on 2002-12-10 10:34

 

[belzar]

Either way would appear to work but I would have to agree with adding a new Y/N field to your first table to track whether they should be contacted or not. Let's say you add a Y/N field (boolean) to your first table (tbl_A) called DoNotContact. You could then create an update query to mark all customers in tbl_A that are listed in your second table (tbl_B). Just create and run a temporary query like:

UPDATE tbl_A INNER JOIN tbl_B ON tbl_A.Cust_ID = tbl_B.Cust_ID SET tbl_A.DoNotContact = Yes;

Now you only need to add the new field to your original query and add a checkbox linked to the new Y/N field... tbl_B is no longer needed if it was only used to store who not to contact...

Hope this helps...

Robert Hall
This message was edited by belzar on 2002-12-10 11:04
This message was edited by belzar on 2002-12-10 11:05

 

[Linus_99]

Thanks to all. Russell's first method works well for this requirement.

Can't keep the information in the original table, because it comes from a different source & needs to be maintained separately.

I really appreciate the help in this forum for things that might be simple to experts, but appear really obscure to newbies in Access.