VBA to handle duplicate selections in Subform. (1 Viewer)

[David Ball]

Hi,

I have a form with a subform where the user can select multiple items from the combobox in the subform. I want to make it impossible for the user to select the same value twice from the subform for any given record in the form. I tried to do this by setting up the intermediate table behind the subform with the two fields as a combined primary key. This didn’t work and was quite messy.
I am wondering if VBA would be a better way to handle this.
What VBA code would I use to check if a duplicate value has been selected then show a message, “That value already assigned”, if a duplicate is selected. Then give the user the option of selecting another value or cancelling the operation?
Thanks very much
Dave

 

[MajP]

You can make the rowsource of the query show only those values not already selected. You need to do a Not In query. Without knowing the current rowsource it would be hard to give you the correct syntax. Can you provide the rowsource. This is a much better option

if a duplicate value has been selected then show a message, “That value already assigned”, if a duplicate is selected. Then give the user the option of selecting another value or cancelling the operation?

Imagine you went online and the combo to order a shirt is XS,S,M,L,XL. You pick M and it says not available. You pick L and it says not available. My guess you would be annoyed. Why show it, if you cannot pick it? If only XS and XL are available only show that.

 

[Cronk]

If the selection data is being stored in a table along with the primary key for the main record, you could add a unique compound index on the two key fields so that duplicates will be prevented.

Note, you will have to remove any duplicates before the unique compound index can be applied.

 

[David Ball]

Hi MajP,

The Row Source of the field in the subform is a table, tblWeldProcedures. The field name is Weld Procedure.

Cronk, thanks. I tried using a combined primary key but it gave me nothing but trouble.

Thanks

Dave

 

[MajP]

How is the subform linked to the main form? So the user can select only one weld procedure for each "task". Sorry, would also need the rowsource of the subform. But, pretend the subform is linked to the Mainform by taskID. Then the on enter event of the combo on the subform the code might be something like

Dim strSource as string
strSource = "Select [Weld Procedure] from tblWeldProcedure where [Weld Procedure] not in (Select [Weld Procedure] from SomeTable where TaskID = " & me.Parent.taskID & ")"
me.someCombo.rowsource = strSource

 

[David Ball]

The subform has Linked Master Field = ElectrodeID. Linked Child Field = Electrode.

For "SomeTable" I am using the intermediate table that links tblElectrode and tblWeldProcedures. Is this correct?
Thanks

 

[MajP]

So for a given Electrode you can only have multiple weld Procedures but you cannot repeat the same weld procedure

Try this in the combo on enter procedure. It may be close

Dim strSource as string
strSource = "Select [Weld Procedure] from tblWeldProcedure WHERE [Weld Procedure] NOT IN (Select [Weld Procedure] from IntermediateTableName WHERE Electrode = " & me.Parent.ElectrodeID & ")"
me.someCombo.rowsource = strSource
me.someCombo.requery

If you could paste the current combobox rowsource, and the subform recordsource that would help. Or you could post a stripped down version of the database with that form and a few records.

 

[David Ball]

I have a stripped down database but can't find how to attach anything here?

 

[jdraw]

See this for attaching files to posts.

 

[David Ball]

Sample file attached (I hope).

Thanks for the info on how to do that, jdraw.

 

[Micron]

While reading I'm deducing that this is a multi value field

user can select multiple items from the combobox

Seeing as how someone else has already downloaded the db, I'll wait to see. Just sayin' I would avoid those like the plague.

 

[David Ball]

Micron,

I meant that the user can select a value from the combo box and then select another, etc.
For example, if the field was for available colors for a car, the user could select yellow, then red, then black, etc.

Thanks

 

[moke123]

try something like this in the combo box before update

Private Sub WeldProcedure2_BeforeUpdate(Cancel As Integer)

    If DCount("*", "tblElectrodeWeldProc", "Electrode2 = '" & Me.Electrode2 & "' and WeldProcedure2 = '" & Me.WeldProcedure2 & "'") <> 0 Then
        Cancel = True
        Me.Undo
        MsgBox "Duplicate entry"
    End If
    
End Sub

 

[Cronk]

Re #4

I tried using a combined primary key but it gave me nothing but trouble.

The use of text primary keys is most inefficient and leads to many problems in further database development.
I've modified your database to use Autonumber primary keys which are not displayed in the form. I've also added a unique composite field which removes any need for code to prevent duplicate entry.

 

[David Ball]

Thanks Cronk, when I open frmEnterElectrodeDetails no records appear.

I think MajP's idea of only showing options that have not yet been selected in the combo box is a very elegant way to handle this. (I just don't know how to do it!)

Dave

 

[Cronk]

Select an electrode in the drop down to show the weld procedures.

 

[moke123]

heres your original db with the code from post#13

 

[MajP]

Here is the sql so that you can only select choices not previously selected. (I set the combo up to show the description field and hide the number field, so I returned ProcNo and Description).

Private Sub WeldProcedure2_Enter()
  Dim strSql As String
  strSql = "SELECT ProcNo, Description FROM tblWeldProcedures WHERE ProcNo "
  strSql = strSql & "NOT IN (SELECT WeldProcedure2 FROM tblElectrodeWeldProc WHERE Electrode2 = '" & Me.Parent.ElectrodeID & "')"
  Me.WeldProcedure2.RowSource = strSql
  'Below is Optional if a Message if desired
  If DCount("*", "tblElectrodeWeldProc", "Electrode2 = '" & Me.Parent.ElectrodeID & "'") = DCount("*", "tblWeldProcedures") And Me.NewRecord Then
    MsgBox "All weld procedures have been assigned", vbInformation, "No Available Weld Procedures"
  End If
End Sub

 

[David Ball]

MajP and moke123,

Thanks very much for your time and effort. Both solutions work perfectly.

Dave