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

data validation on a combination of two fields

P: 59
Hello Bytes/Scripts folks!

I am trying to implement validation for a combination of two fields in a table (through a form). I have a form “frmAddAddr2” that is bound to the table “tblLKUPAddr2”. frmAddAddr2 pops-up when a user is entering an address in the main form and does not find their Addr2 option in the main form’s combo box “cboAddr2”. (Addr2 in this case is the second line of an address i.e. Apt 1B or Ste 701).

On the main form cboAddr2 shows the Addr2 values as a concatenation of two fields in tblLKUPAddr2: “Addr2Prefix”, which is the prefix Apt, Ste, Fl part of Addr2, and “Addr2Nm”, which is the number part of Addr2. I made Addr2 two fields because I wanted to control the spelling of the prefix so that I didn’t end up with unintended duplicates such as Suite 3, Ste 3, St 3. This way, you choose the prefix and then the number and they are concatenated in the query for the main form’s cboAddr2.

On the pop-up form, frmAddAddr2, the user chooses the prefix from a combo box cboAddr2Prefix and then enters the number in txtAddr2Nm. What I would like to do is to check that the combination of the values in these fields does not already exist in tblAddr2. I have read some of the data validation threads and got as far as getting the following DLookup code - posted below - to work in the BeforeUpdate event of a single field (Addr2Nm). However, I cannot figure out how to alter the code to work on a combination of both fields.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtAddr2Nm_BeforeUpdate(Cancel As Integer)
  3.   If DCount("[Addr2Nm]", "[tblLKUPAddr2]", "[Addr2Nm] = '" & Trim$(Nz(Me.txtAddr2Nm)) & "'") > 0 Then
  4.      MsgBox "That Addr2 already exists."
  6. End If
  8. End Sub
Any advice is greatly appreciated!

Apr 24 '08 #1
Share this Question
Share on Google+
4 Replies

P: 59
PS. Validating based on two fields seems like it will be useful for other items in my database as well. For example, when adding a street address it is possible there could legitimately be two identical values in the "Addr1" field, but only if the values in the "ZipCode" field are different (e.g. 10 5th St in Brookyn, NY 11231 and 10 5th St in New Rochelle, NY 10801). So, I would want to see if a combination of values in the field Addr1 and ZipCode already exists.

Thanks again for any suggestions!
Apr 24 '08 #2

Expert Mod 15k+
P: 31,760
You need to specify the third parameter of your DCount() function carefully.

The first is probably better off simply as "*". The second is fine. The third needs to select the actual matching records using SQL WHERE syntax.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtAddr2Nm_BeforeUpdate(Cancel As Integer)
  2.   Dim strWhere As String
  4.   strWhere = "[Addr2Prefix]='%1' AND [Addr2Nm]='%2'"
  5.   strWhere = Replace(strWhere, "%1", Me.cboAddr2Prefix)
  6.   strWhere = Replace(strWhere, "%2", Trim(Nz(Me.txtAddr2Nm)))
  7.   If DCount("*", "[tblLKUPAddr2]", strWhere) > 0 Then
  8.      MsgBox "That Addr2 already exists."
  9.   End If
  10. End Sub
Apr 25 '08 #3

P: 59
Thanks for the reply NeoPa. I will try and post back if I have problems.
Apr 25 '08 #4

Expert Mod 15k+
P: 31,760
No worries Bridget :)

I particularly appreciated that all the information I needed was available in the question.
Apr 25 '08 #5

Post your reply

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