Remove duplicate entry in combobox

MS Office Access


  • 1. OT: public vs private
    hi Jamie, onedaywhen wrote: > Firstly and first of all, there are two kinds of posters in Access > newsgroups: > - a) those who think there are 2 types of code modules; > - b) those who think it's not that simple ;-) <g> but it is a good pragmatical approach. mfG --> stefan <--
  • 2. Command button to find record
    Is it possible to code a command button that is like the find/replace button but with out the "replace"? Basically, I want the user to click on the button and have it prompt to enter the ID# of the record. I created the find/replace command button through the wizard, however I just want the user to do a search on 1 specific field (Id#) and not allow them to search in any other fields. Also, I do not want to give the user the option to "replace" anything. Thanks.
  • 3. really silly filter problem
    I'm sure I'm just tired, but I can't get this. I have a table, "sale_information" containing field "sale_dateID" as number and "sale_current" as Yes/No I have it joined to table "sale_dates" containing "sale_date_ID" as number and "sale_date" as date I have a query filtering them to only return the current sale: SELECT table_sale_information.sale_current, table_sale_dates.sale_date_id FROM table_sale_dates INNER JOIN table_sale_information ON table_sale_dates.sale_date_id = table_sale_information.sale_date WHERE (((table_sale_information.sale_current)=True)); and now I have a form in which I enter sales of items, which I would like to store the current date id in the field "clerking_date_ID", but I can't figure out how to make the current date in the query become the default value for the form field. The current date's ID value right now is 1, but everything I try either brings up a 0, or an error. I have tried filtering the "clerking_date_id" field by the query, creating a new text box containing the query result, creating a new combo containing the result, but I can't get that 1 to show up. What am I missing?

Remove duplicate entry in combobox

Postby TWVudGFsRHJvdw » Sat, 08 Jul 2006 04:38:02 GMT

I'm BaaAAaaack!

Well, thanks to all the folks who have helped me by posting their questions 
and getting answers etc, I can now set up a basic search using a combobox to 
pick the criteria. :-)
The problem I am running into now is that if I try to set up a combo box 
based on entries in a sub-table, I get duplicate entries in the combo box.  
When I pick one of the entries, I only get the report based on that one 
entry.  Example:  I have a database that tracks deliveries to my area.  The 
main form is for entering the driver's information and is the "parent" table. 
 The "child" table is one that tracks the destination of the delivery.  I 
have multiple drivers/vendors that deliver to the same places.  I want to be 
able to generate a report based on the number of deliveries to the facility.  
When I open the report and get the unbound form/combo box if I have multiple 
deliveries to a facility by one or more drivers, I get multiple entries of 
the same facility in the combo box.
My question is is there a way to set this up with a combo box or other means 
without prompting the user to type in the location?  The field name is 
Destination so if you have something that will work, please use that as the 
field name.  Thanks again for everyone's help to this point.


RE: Remove duplicate entry in combobox

Postby S2xhdHV1 » Sat, 08 Jul 2006 05:01:02 GMT

Make your rowsource for your combo something like this(with the correct 
names, of course):

"SELECT DISTINCT Destination FROM MyChildTable;"

It will then return only one occurance of each destination in the table.

RE: Remove duplicate entry in combobox

Postby TWVudGFsRHJvdw » Sun, 09 Jul 2006 03:41:02 GMT


I tried what you suggested and cannot seem to get it to work. If I only use
the "Destination" field I get the desired results - one entry in the combo
box. However, when the report opens, it is blank, even for those entries
that occur only once in the entire subtable. If I include the primary key
field (oddly enough it's called [PrimaryKey]) without the Select Distinct I
get a populated report but there are multiple entries in the combo box for
the destinations that have been visited more than once. Each one only pulls
up the information associated with its PrimaryKey.
I also tried using Select on the PrimaryKey and Select Distinct on the
Destination fields but could not get it set up properly. I'm fairly familiar
with a lot of different coding commands but this is one I've never used
before (just like combo boxes). If there is an easier way to input this in
the SQL/Query modules, I'd like to know it as I was actually typing the
statements into the rowsource box of my combo box.
By the way, I don't know if this will make a difference but the Combo box
and the form it is on are both unbound.
If I take the coding out of the report that opens the combo box form I am
prompted for the information. When I manually enter it I get all the parent
records associated with the entry with is on a subtable. For instance, I've
input sample data into the database using forms I created. Lets say I have
Dorm 1 used one time in the Destination field and Dorm 2 is used at least
twice. If I enter Dorm 1 manually, I get the single (parent table record)
associated with it. Dorm 2 yields all the records on the parent table
associated with it. I want the same results using a combo box or something
similar so that typos won't get in the way of record searching.

Thanks for the assist,

"Klatuu" wrote:

RE: Remove duplicate entry in combobox

Postby S2xhdHV1 » Sun, 09 Jul 2006 04:10:02 GMT

think I'm having a little trouble understanding the issue. If you want to
present your report based on a specific location, then the method I propsed
will provide that capability. I don't understand how the primary key figures
into this. The usual method is to filter the report using the value in the
combo using the Where argument of the OpenReport method.

"MentalDrow" wrote:

RE: Remove duplicate entry in combobox

Postby TWVudGFsRHJvdw » Sun, 09 Jul 2006 09:38:02 GMT

hhhhhh (he said as the light went on),

This is the first time I've ever worked with combo boxes and unbound forms.
I'm doing things with this database I've never done before. I'm used to
having the report based on a query where the user (me for the most part) had
to enter the criteria. I'll look up openreport and Where arguement. For the
most part, I see no reason why the help you provided hasn't at the very least
pointed me in the right direction. Thanks as always.


"Klatuu" wrote:

RE: Remove duplicate entry in combobox

Postby TWVudGFsRHJvdw » Wed, 12 Jul 2006 01:37:01 GMT


I thought I would be able to get this to work. Here is the situation I am
running into now.
When I open the unbound form, I get the single entries as opposed to the
duplicate entries I was getting. However, when I select one which activates
the OpenReport with Where command in the AfterUpdate actions, I get a type
mismatch error. The exact error message is

Run-time error '3464':
Data type mismatch in criteria expression

When I try to debug I hover over the where statement and the value assigned
is numerical based on the order of the selections in the combo box. I select
the first the value is 0, second is 1, etc. How do I code the OpenReport
command to utilize the Text/Number "value" that is actually in the combo box
that the user selects (i.e. "Dorm 12" when the user selects Dorm 12 from the
combo box)? I've tried using some of the other variations I see in the
discussion group but I'm not getting anywhere. The code I have in the
AfterUpdate of the Combo Box is

DoCmd.OpenReport "DestinationSingleRPT", acViewPreview, , "[Destination] = "
& Me!DestinationSearchCMB

I've tried using
DoCmd.OpenReport "DestinationSingleRPT", acViewPreview, , "[Destination] =
'" & Me!DestinationSearchCMB & "'" but I don't know what I'm doing wrong.


Thanks again.

"MentalDrow" wrote:

RE: Remove duplicate entry in combobox

Postby TWVudGFsRHJvdw » Wed, 12 Jul 2006 03:10:01 GMT

f anyone else is having the same problem I had regarding data type mismatch
here is an example of how I got the text value vs. numerical value based on
position in combo box.

DoCmd.OpenReport "MyReport", acViewPreview, , "[MyField] = '" &
Me!MyCombo.Text & "'"

Put this in the After Update area of the Combo Box and you should get the
actual "text" from the combo box.


"MentalDrow" wrote:

Similar Threads:

1.combobox - removing duplicates

Is there anyway to remove showing duplicate records ina  subform linked to a

The query just shows NAMES in AZ order from the table, but I dont want the
combo to show duplicate names?



2.Removing choices from combobox query but stilling showing for older entries

3.Duplicate field entries with combobox

I have a form which employs a couple of combo boxes that allow me to
enter Vendor and Model information into a table.  The combo boxes
employ a SELECT DISTINCT query to populate the lists.  I am now trying
to add a warning message if a value that is not in the list is

Private Sub cboUnbound_NotInList(NewData As String, Response As
  Dim cnn As New ADODB.Connection
  Dim strSQL As String
  Dim bytResponse As Byte
  Set cnn = CurrentProject.Connection
  bytResponse = MsgBox("Do you want to add this new item " _
   & "to the list?", vbYesNo, "New Item Detected")
  If bytResponse = vbYes Then
    strSQL = "INSERT INTO Colors(Colors) VALUES('" _
     & NewData & "')"
    Debug.Print strSQL
    cnn.Execute strSQL
    Response = acDataErrAdded
  ElseIf bytResponse = vbNo Then
    Response = acDataErrContinue
  End If
End Sub

The problem is that when I reply 'yes' to the msgbox I get an extra
entry in the destination table with just the one field bound to the
combobox and the new value.  So I get one complete record and another
record with a value in only one field.  What am I doing wrong.

4.How can I remove duplicate Holiday entries?

I have accidentally added duplicate holiday entries on my calender in 
Outlook.  How can I remove them?

5.Letter Merge / Removing Duplicate Entries

How do I remove "duplicate" records prior to the merge

For instance, record 1 Bill Smith / 120 Main Street
              record 2 Jill Smith / 120 Main Street
              record 3 Ron Jackson / 500 Elm Street

I would like only two labels:
(1) The Smith Family / 120 Main Street
(2) The Jackson Family / 500 Elm Street

How can I intercept the input merge file and remove
the duplicate (2nd) record?  Is there an option within
the merge process?

My version of Word is Word 2000.

Thank You.

6. Removing or hiding Items from a Combo Box that have been selected--preventing duplicate entries

7. Can we remove duplicate entries in MS Access

8. Duplicate Query - How to Remove Duplicates from Original Table

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 45 guest