Check for duplicates in unbound fields

[LEXCERM]

Hi there,

I have five unbound fields on a form which a user will populate with file names using the open dialog box.

Before executing the routine, I want some form of code that loops all five fields and checks if the same file has been selected more than once. I've used tags for the fields.

Thanks in advance.

Paul.

 

[vbaInet]

How did you name the controls? If you have something like txtFileName1, txtFileName2 etc... then it would easy to do.

If that's not the case then you can check each of the controls (except the active control) in the After_Update event.

 

[LEXCERM]

Hi vbaInet and thanks for replying.

How did you name the controls? If you have something like txtFileName1, txtFileName2 etc... then it would easy to do.

Yes, I have named the controls very similar as to what you have suggested.

Could you please provide an example of the code.

Many thanks in advance,
Paul.

 

[vbaInet]

By the way, I meant Before_Update, not After_Update

So in the Before_Update event of EACH of the controls, you could do something like:

    For i = 1 To 5
        If Me.ActiveControl.Name <> Me.Controls("txtFileName" & i) Then
            If Me.ActiveControl.value = Me.Controls("txtfilename" & i) Then
                Cancel = True
                Me.ActiveControl.value = ""
                MsgBox "This file has already been chosen.", vbExclamation, "File has been chosen"
                Exit For
            End If
        End If
    Next

 

[LEXCERM]

Hi vbaInet,

Becuase the textboxes are being populated by filenames using the following code, the before update/on change events are not triggering:-

''''>OpenFileDialogBox
'set MICROSOFT OFFICE OBJECT 12.0 OBJECT LIBRARY

Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select file and click OK"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "All Files", "*.*", 1
.InitialFileName = ""
If .Show = -1 Then
strFilename = .SelectedItems(1)
Else
Exit Sub
End If

End With
''''>OpenFileDialogBox

Thanks in advance.
Paul.

 

[vbaInet]

Where in your code do you set the SelectedItem (i.e. strFileName) to a textbox?

 

[LEXCERM]

Where in your code do you set the SelectedItem (i.e. strFileName) to a textbox?

Me.txt_FileName_1 = strFilename

 

[vbaInet]

In what event? How does it get set. Can you show the code?

 

[LEXCERM]

It is on the "on click" event of a command button. So, 5 command buttons corresponding to five textboxes.

To capture whether the same file has been selected in another textbox.

P.

''''>OpenFileDialogBox
'set MICROSOFT OFFICE OBJECT 12.0 OBJECT LIBRARY

Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select file and click OK"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "All Files", "*.*", 1
.InitialFileName = ""
If .Show = -1 Then
strFilename = .SelectedItems(1)
Else
Exit Sub
End If

End With
''''>OpenFileDialogBox

Me.txt_FileName_1 = strFilename 
Set dlg = Nothing

 

[DCrake]

You really need to create a seperate sub routine that evaluates the contents of each textbox and compares it to the contents of the remaining textboxes. This would be called on the after update event of each textbox.

Sub TestNames()

If Nz(Me.Text1,"") <> "" Then
   If Me.Text1 =  nz(Me.Text2,"") Then
      Failed
   End If
End If

Repeat for all text boxes

End Sub

Or you could put all textbox contents into an array and compare the array.

There are various ways of doing this. It all depends on your VBA skills

 

[vbaInet]

What you do is setfocus to the text box and change its TEXT property:

...
txt_FileName_1.setfocus
txt_FileName_1.text = strFilename 
Set dlg = Nothing

That will cause the BeforeUpdate to fire