Filtering a list for a combo box

[dasadler]

If you have a subform with a combo box having a rowsource that is a query, how can you filter what is displayed in the combo box so that you cannot enter a duplicate record?

If you do add a duplicate record, Access will certainly react but I would like to prevent that by only showing those items in the combo box that are currently not in the table (that is populated by the combo box).

For example, if I were entering students in a course, I would not want to see the same name displayed for the same course after I had already entered that student for that course. I would expect to see that name if I go to a different course.

[Wendell]

In that case you would want to use an Outer Join query as the RowSource for the combo box, and check to make sure you don't already have a record for that Student in your "attending" table. It may get pretty complex depending on your form design and the table structures, but should be doable.

[HansV]

As Wendell indicated, it can be done, but it requires a lot of extra work, including VBA code. I'd leave it as it is. If you wish, you can use the On Error event of the form to replace the built-in error message with a more user-friendly one.

[dasadler]

The benefit may not be worth the cost of complexity. I would not mind changing the error message - the only problem is that there is no error until til you try to enter another record. Is there any way to be 'warned' before a duplicate entry is selected? For example, either show that entry grayed out or have a pop up as the cursor scrolls over that item in the combo box?

[HansV]

Again, it's possible but it is complex. I'd rely on the user's common sense (a dangerous assumption, I know )

[dasadler]

I agree... still, it would be nice if that were a feature of a combo box... to filter out previously used entries. Maybe in Access 2012.

[HansV]

It's not an Access-specific feature, but a general database design problem.

[dasadler]

It seems to me (the non programmer) that checking for a duplicate could be an after update event so as soon as an item is selected from the combo box, the after update event would scan the table for a duplicate entry and, if found, pop up a message. I just don't know how to write the code to do that.

At the risk of being simplistic.... I am not sure I see the complexity of that approach although I can see how previously discussed options would be a lot of work.

[HansV]

See the attached version of the database. I needed to add a unique ID for each record in the participation table, I used an AutoNumber field for that.
There is code in the Before Update event of the StudentID combo box on the sbfStudents subform.

DASM2MTest.zip

[dasadler]

Thank you Hans. I hate to bother you for this but could you explain the code you wrote? I understand that it is counting the existence of records matching the one just entered and if that count is greater than 1 then it displays the message. However, I don't really understand the syntax... and what is "_" used for?

[HansV]

If a line of code is very long, you can break it into several lines for readability. A space followed by an underscore _ at the end of a line indicates that the code continues on the next line. The underscore _ itself is not part of the code, it is merely the continuation indicator.

The DCount function has the syntax

DCount(field, table_or_query, where_condition)

Each of the three arguments is a text string.

In the code in the sample database, I use "*" as fieldname instead of specifying a particular field; this is because I simply want to count records.

The table_or_query argument can be the name of a table or of a stored query. In this example, I use "Participation" because I want to count records from the Participation table.

The where_condition argument uses the syntax of the WHERE clause in a SQL statement without the keyword WHERE. In this example, it is

"CourseID=" & Me.CourseID & " AND StudentID=" & Me.StudentID & " AND Not ParticipationID=" & Me.ParticipationID

Note that this string concatenates fixed parts between quotes and variable parts that refer to fields on the (sub)form. In code behind a form, the keyword Me refers to that form; this is handy because it doesn't depend on the name of the form.

We are looking for other records with the same course and student. By specifying

... AND Not ParticipationID=" & Me.ParticipationID

we ensure that we exclude the current record from the count.