Loop through comboboxes, check for duplicate values 2

[MikeGeitner]

Hello,

I have eight comboboxes on an unbound form. You can select person's names to assign them spots in an indoor cycling training facility. These names come from "tblRiders". You might not assign all the spots, but you shouldn't be able to assign more than one spot to the same person. Is there a way to loop through the comboboxes to prevent duplicate values other than nulls, before updating the table?

 

[fneily]

You could create a temptable of names then as one name is selected delete it from the temptable and requery the next comboboxes. Sortof like:
thread702-1426651

But I have a bigger question: You don't have a table with fields like
Spot1, Spot2, Spot3, etc. ??

This is not correct table design. It violates the first normal form. You have duplicate column headings, this will lead to blank cells if all the spots aren't used, it'll cause variable length records which is against normalization, and Spot1, Spot2, etc. looks(and is) a category, not fields. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You should have a table that looks like:
Rider Spot
Bill 1
Joe 2
etc.

 

[MikeGeitner]

The table looks like your example, with a name field and a spot field, among others. The form changes the value in the spot field when that rider sets his bike up in a different position. When you click the command button, it updates the table and outputs a .csv file from a query that the training software reads in and uses for the actual training session. Currently, the user has to manually edit a .csv file to change rider positions. My table looks just like this file.

Thanks for the link. I'll take a look a that.

 

[fneily]

I'm confused. A rider sets a spot, let's say 2. Then he resets it to 4. Are you saying he can't set it back to 2?
I don't understand the setup with eight comboboxes. It sounds like you have one rider choosing 8 spots. That, at most, is two comboboxes - one to select rider and one for the spot.
Sorry.
But somehow it seems like you still want to delete a combobox item. Maybe the reference will help.

 

[MikeGeitner]

Thanks for your help and I know it's confusing. This is only useful because the training software isn't easy to work with.
When the form opens, SQL updates all the POS (spot) fields in tblRiders to "0". There are 8 comboboxes on the form, each one representing a spot, or resistance trainer #, to put your bike in. A person might be in spot #1 one day and spot #4 the next. The training software needs to know who is where at each session. So, if eight people show up to ride you put their names in the comboboxes and click a button. The table gets updated and a .csv file is output to the directory of the training software. The user then "loads" the file through the training software.

I'd like to check all the comboboxes to make sure that a rider isn't showing in more than one before updating the table and outputting the .csv.

 

[fneily]

Ok. That's what I first thought. So, I think for simplicity, create a temptable of the riders names and use that temptable as the rowsource for the comboboxes. Using the temptable won't affect the main table tblRiders. Then when someone selects their name from, let's say, spot3, on the AfterUpdate event of that combobox, delete that name from the temptable and then requery the remaining seven comboboxes. The name will then not show in the remaining comboboxes. The reference should help you write the code.

 

[MajP]

I would think you would want to do what fneily says and limit your choices. If you simply want to check for duplicates you could do the following.

1. set the tag of your controls to "?" (without the quotes)
2. pass the form to the function and check for duplicates

Public Function isDup(frm As Access.Form) As Boolean
  Dim myControls As New Collection
  Dim ctl As Access.Control
  Dim ctl2 As Access.Control
  
  For Each ctl In frm.Controls
     If ctl.Tag = "?" Then
        If Not Trim(ctl.Value & " ") = "" Then
           myControls.Add ctl, ctl.Name
         End If
      End If
  Next ctl
  Set ctl = Nothing
  For Each ctl In myControls
    For Each ctl2 In myControls
      If ctl Is ctl2 Then Exit For
      If ctl.Value = ctl2.Value Then
        isDup = True
        Exit Function
      End If
    Next ctl2
  Next ctl

End Function
[/code

 

[MikeGeitner]

Well, it works pretty good except "#DELETED" shows up in the combobox list once you've selected an item. That is, using Fneily's temptable method.

Any thoughts?

 

[fneily]

There are a couple of ways to set this up. So I created a Delete query with the one field and as criteria:
[Forms]![Delete_From_combo]![combo0].[value]

Then on the AfterUpdate event of the combobox put:
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete_From_Combo_Query"
Me![Combo0].Requery
DoCmd.Echo True
DoCmd.SetWarnings True

So the user selects a name, which will run the Delete query. I don't get the Delete noticed.
Obviously, change names where appropriate. And, of course you'd have more Requeries.

 

[MikeGeitner]

Thanks, I'll try it out!