data validation on a combination of two fields

[banderson]

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.

Private Sub txtAddr2Nm_BeforeUpdate(Cancel As Integer)
 
  If DCount("[Addr2Nm]", "[tblLKUPAddr2]", "[Addr2Nm] = '" & Trim$(Nz(Me.txtAddr2Nm)) & "'") > 0 Then
     MsgBox "That Addr2 already exists."
 
End If
 
End Sub

Any advice is greatly appreciated!

Bridget

[banderson]

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!
Bridget

[NeoPa]

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.

Private Sub txtAddr2Nm_BeforeUpdate(Cancel As Integer)
  Dim strWhere As String
 
  strWhere = "[Addr2Prefix]='%1' AND [Addr2Nm]='%2'"
  strWhere = Replace(strWhere, "%1", Me.cboAddr2Prefix)
  strWhere = Replace(strWhere, "%2", Trim(Nz(Me.txtAddr2Nm)))
  If DCount("*", "[tblLKUPAddr2]", strWhere) > 0 Then
     MsgBox "That Addr2 already exists."
  End If
End Sub

[banderson]

Thanks for the reply NeoPa. I will try and post back if I have problems.
Bridget

[NeoPa]

No worries Bridget :)

I particularly appreciated that all the information I needed was available in the question.