By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,323 Members | 1,203 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,323 IT Pros & Developers. It's quick & easy.

fields of table 2 will 'look up' the values in table 1 before allowing entry

P: 2
Is it possible to do the following and if so, how?

In one table, I have a list of email addresses I know are bad and no longer work.

Another table has a list of companies with contact people, fields accommodate up to ten people. There's a corresponding field for each one for their email address. In other words, the table has 10 different fields for email addresses that may or may not have a value.

How would it be possible to PREVENT a known 'bad' email address from being data entered into any of those fields in the first place? In other words, some way that the 10 designated fields of table 2 will 'look up' the values in table 1 before allowing entry?

Solve that and lunch is on me!
Sep 12 '07 #1
Share this Question
Share on Google+
7 Replies

Expert 5K+
P: 6,596
So you have found the geniuses, but haven't found the Access Forum yet, huh?
Sep 12 '07 #2

Expert 5K+
P: 5,821
Moving to the Access of Evil....
Sep 12 '07 #3

Scott Price
Expert 100+
P: 1,384
Well, first off... Your table is designed awkwardly: create a tblContacts table that holds FirstName, LastName, EmailAdd, CompanyID, etc fields. Delete the 10 contact fields with corresponding email addresses from your main table.

There are at least two enormous advantages that this will give us... One is that instead of having to compare 10 fields with your BadAddress table, now we only have to compare 1. Second is that you no longer have a limit to how many people can be associated with a particular company, as you have now an artificial limit of 10... Conversely, since you most likely won't have 10 people associated with every company, you likely have a lot of empty fields placed there just in case you ever needed to add more contacts to a particular company.

As for the other, we'll take a look at that as soon as you get the tables redesigned.

Sep 12 '07 #4

Scott Price
Expert 100+
P: 1,384
To check for a bad email address:

In the table you are storing your bad email addresses in, make sure the Index property of the email address field is set to Yes(No Duplicates). Also, I'm making the assumption that your field type is Text.

Place this code in a standard code module:

Expand|Select|Wrap|Line Numbers
  1. Public Function CompareEmails(EMADD As String) As Boolean
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Set db = CurrentDb
  6. Set rs = db.OpenRecordset("tblBadAddress", dbOpenTable)
  8. With rs
  10.     .Index = "BadAddresses"
  11.     .Seek "=", EMADD
  12.         If .NoMatch = True Then
  13.                 CompareEmails = False
  14.             Else
  15.                 CompareEmails = True
  16.         End If
  17.     .Close
  18. End With
  19. Set rs = Nothing
  20. End Function
Next, in the form that you enter emails into:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtEmail_BeforeUpdate(Cancel As Integer)
  2.     If CompareEmails(Me!txtEmail) = True Then
  3.         MsgBox "Bad email address! Please enter a good address."
  4.         Cancel = True
  5.         Me!txtEmail.SetFocus
  6.     End If
  7. End Sub
Note this code does nothing in the way of testing for a valid email format! It only checks the string you give it against any existing email addresses stored as text in the table I named tblBadAddress in the field I named BadAddresses.

Good luck, and feel free to ask any questions you need to clarify!

Sep 13 '07 #5

P: 2
Scott, thank you and you are 100%correct. The present configuration with everything in a single table is awkward, limited, and rudimentary. I am familiar with the concept of linked tables with a one-to-many relationship but have no experience working with them, doing mass updates, exporting them etc. and so went with what I knew.

Regarding the coding...Wow! That is so far over my head. Question please: Are you available as a consultant and if so, at what rate? If you like you may email me at [email removed]
Sep 13 '07 #6

Scott Price
Expert 100+
P: 1,384
Regarding the coding...Wow! That is so far over my head. Question please: Are you available as a consultant and if so, at what rate? If you like you may email me at [email removed]
Thanks! But no thanks :-)

Post any questions that you need answered here in this forum, and I or someone else will be able to help you out.

To implement the code I gave you, in your database window, press Alt+F11, this will bring up the VBA editor window. Go to the top menu bar Insert>Module. When this opens in the main window, just under where it says Option Compare Database, type in Option Explicit.

Copy the public function code i gave you into that window. Click the Debug menu, click Compile, then save your changes. Go back to the main database window and make sure that your field is indexed (open the bad addresses table in design view, click on the email address field(s) and click on the Indexed line, choose Yes(No Duplicates).

Open your form for entering emails in design view. Double click on the text box that you are entering the email addresses through. Go to the Events tab of the resulting Properties window. On the Before Update event, click the ellipsis (...) to the right of the line. Choose Code Builder. This will bring you back to the VBA editor window. Copy and paste lines 2 through 6 of the second code section I gave you into the lines immediately below where it says Private Sub [textboxname]_BeforeUpdate(Cancel As Integer)

Compile again, save again. Close the vba window, open your form in regular view and test!


PS (this is very bad database practice, but this code WILL work independently of your design issue...)
Sep 13 '07 #7

Expert Mod 10K+
P: 14,534

Have a look at this tutorial.

Database Normalisation and Table Structures.

Fixing the structure of your tables will solve a lot of problems before they arise.
Sep 13 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.