Check form textboxes against one another

[akaRose]

Hello,

I can't seem to figure out something that should probably be pretty easy. It just doesn't work, I'm sure its something dumb!

I have an unbound text box with a number of fields of the form concatenated in it (I know its strange not my idea). I need to check that in the each new text box, on the AfterUpdate there aren't duplicates of any of these fields.

So right now my code is:

If Me.A1 Like "*" & Me.[txtConcatenateFields]&"*" Then

MsgBox ("You've already entered this sample")

Endif


Believe me I know that this is a bit odd, but I didn't do the design of the database and it has many issues. I'm just trying to figure out this one last thing.

Thanks for the help

 

[MajP]

I would do this differently.
Hilite all the input textboxes that are involved. Put a question mark in the tag property. No parentheses around the question mark.

In the Before update event on the properties sheet put
=isDupe()
Do not use the after udate event. Do not create event procedures.

put this function on the form

Public Function isDupe() As Boolean
  Dim ctrl As Access.Control
  Dim varVal As Variant
  varVal = Nz(ActiveControl.Value, "")
  If Not Trim(varVal & " ") = "" Then
    For Each ctrl In Me.Controls
     If ctrl.Tag = "?" Then
       If Trim(varVal) = Trim(ctrl & " ") And Not ctrl Is ActiveControl Then
         isDupe = True
         ctrl.Undo
         MsgBox "Duplicate value, enter another"
         DoCmd.CancelEvent
         Exit Function
       End If
      End If
    Next ctrl
 End If
End Function

Give it a test

 

[TheAceMan1]

How are ya akaRose . . .

To answer this effectively we need to know [blue]how your concatenating?[/blue] ... [blue]are you using a form or control event?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[akaRose]

Hello there,

MajP your solution worked wonderfully!!! I've been looking at my form and need to make a few changes to trap all the potential issues. So now I need to put this code with some others and am having a bit of trouble.

In the BeforeUpdate event I call this function so make sure there are no duplicate entries just entered. I also want to check the table and make see if this sample has been entered previously and if so provide the user with some information. I can do this successfully with the following:

Private Sub A1_BeforeUpdate(Cancel As Integer)
Dim LResponse As Integer
If IsNull(A1) Then
LResponse = MsgBox("Are you sure you would like to remove this sample from the box?", vbYesNo, "Continue")
If LResponse = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * FROM tbl_newentryexample_Normalized WHERE [Sample_ID] = '" & Me.A1.OldValue & "'"
DoCmd.SetWarnings False
Else
Me.A1.Value = Me.A1.OldValue
Me.A2.SetFocus
Me.A1.SetFocus
End If
ElseIf Not IsNull(DLookup("[Sample_ID]", "tbl_newentryexample_Normalized", "[Sample_ID] = '" & Me.A1 & "'")) Then
DoCmd.OpenForm "frm_ShowLocation", , , "[Sample_ID] = '" & Me.[A1] & "'"
Me.A1.Value = Me.A1.OldValue
Else: Call isDupe
End If
End Sub

The error that I'm getting (Error 2115) is on the line just before calling the isDupe function. It doesn't like me returning to the oldvalue. This is in the event that the user changes the sample id currently in the textbox (and it happens to be a duplicate) I want to return to the original number. I would use me.undo but if they've entered other information in the other textboxes I don't want to lose all of that. Any ideas?

Thanks again for the fantastic help