No duplicates in form

[didacticone]

i have a form that lists addresses and also a check box if the address has been given a notice, and then a second check box for two notices and so on up to three. the address field is actually 2 fields, one field (num) is for the house number and the other field (street) is for the street. the street field is a combo box with a source table listing all of the street names that i will use, as to avoid typos and such. what i would like to do, is when i type in a number and street into the two above mentioned fields, if the address already exists in the form, i would like it to popup a message box letting me know if already exists, and perhaps even take me to the record if possible. i have come close but only by combining the two fields (num, street) into one field called 'location' and obviously eliminating the combo box feature, which can now lead to typos. i have used the following code in the after update event of the 'location' field

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
 
Set rsc = Me.RecordsetClone
 
SID = Me.LOCATION.Value
stLinkCriteria = "[LOCATION]=" & "'" & SID & "'"
 
    'Check StudentDetails table for duplicate StudentNumber
    If DCount("LOCATION", "MASTER", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Address " _
        & SID & " has already been entered." _
        & vbCr & vbCr & "You will now been taken to the record.", vbInformation _
        , "Duplicate Information"
        'Go to record of original Student Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
 
Set rsc = Nothing

any idea how i can achieve this with the two separate fields and still maintaining my combo box feature? thanks!

[ADezii]

Here is the General Idea as to how I would proceed on this matter:

1. Create a Private Sub-Routine in the Form's Code Module (ValidateAddress()) that will do all the work of Validating the Address based on both the [num] and [street] Fields. It can be as sophisticated as you wish.
   

   Private Sub ValidateAddress()
   'Make sure that there are entries in both the [num] and [street] Fields, and
   'that the [num] Field is Numeric in nature
   If IsNull(Me![num]) Or IsNull(Me![cboStreet]) Or Not IsNumeric(Me![num]) Then
     MsgBox "The Current Address cannot be added to the Database"
       Exit Sub
   End If
    
   'Check for Duplication in the [num] and [street] Fields
   If DCount("*", "tblAddressess", "[num]=" & Me![num] & " And [street] = '" & _
                                    Me![cboStreet] & "'") > 0 Then
     MsgBox "[" & Me![num] & " " & Me![cboStreet] & "] is a Duplicate Address"
   Else
     'If you get here, you are good to go
   End If
   End Sub

2. Call this Sub-Routine from the AfterUpdate() Events of both the [num] and [street] Fields.
   

   Private Sub num_AfterUpdate()
     Call ValidateAddress
   End Sub

   Private Sub cboStreet_AfterUpdate()
     Call ValidateAddress
   End Sub

3. Any further questions, feel free to ask.

[didacticone]

thanks! that worked well, the only thing i did differently was i only called the sub-routine on the street afterupdate event, otherwise i wasnt able to enter the same house number, which obviously there are many... no one more question for you, is there a way that after the popup box letting me know the address already exists, that it can then take me to that record, so i dont have to go looking for it? thanks again!

[ADezii]

is there a way that after the popup box letting me know the address already exists, that it can then take me to that record, so i dont have to go looking for it?

Yes, just a couple of modifications should do it:

Dim strCriteria As String
Dim rst As DAO.Recordset
'Make sure that there are entries in both the [num] and [street] Fields, and
'that the [num] Field is Numeric in nature
If IsNull(Me![num]) Or IsNull(Me![cboStreet]) Or Not IsNumeric(Me![num]) Then
  MsgBox "The Current Address cannot be added to the Database"
    Exit Sub
End If
 
strCriteria = "[num]=" & Me![num] & " And [street] = '" & Me![cboStreet] & "'"
 
'Check for Duplication in the [num] and [street] Fields
If DCount("*", "tblAddressess", strCriteria) > 0 Then
  MsgBox "[" & Me![num] & " " & Me![cboStreet] & "] is a Duplicate Address"
 
  Set rst = Me.RecordsetClone
      rst.FindFirst strCriteria
        Me.Bookmark = rst.Bookmark
Else
  'If you get here, you are good to go
End If

[didacticone]

worked perfectly! thanks for your help!

[ADezii]

You are quite welcome, glad we got everything to work to your satisfaction.