Results 1 to 13 of 13
  1. #1
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30

    Question Removing dublicates from a combo box

    Hello all!


    This is my first attempt to create something with Access and I'm having a hard time removing duplicates from a combo box used with a from. Tricky part is that I have used lookup function to the tables and the particular combo box gets the value from a different table than the one that the form is being used to. When I'm trying to edit the row source I see that the selected values (by the form wizard) are the PK along with the value that I want. By removing manually the PK from the row source in order to use DISTINCT with the value that i need, the list gets emptied. Is there any way to correct this?

    Thanks in advance.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    probably, but you need to provide some example data, the result required from that example data and the sql you are using for your combo rowsource

  3. #3
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Thank you very much for your prompt reply. Sure thing!

    Under the DB I have a table named table1 that has values that is taking from table2 via lookup. I have amended the relations to one to many. So when checking the table1 form's in question combo box what i see as row is SELECT Table2.PartNo, Table2.Maker FROM Table2;

    PartNo is a PK for table2 and FK for table1. If i try to change this to SELECT DISTINCT Table2.Maker FROM Table2; then the combo box has nothing to select from.

    Finally contets of Table2 are as per below:

    PartNo Maker Part
    1001 Maker1 Part1
    1002 Maker1 Part2
    1003 Maker1 Part3
    1004 Maker2 Part1
    1005 Maker2 Part2
    1006 Maker2 Part3
    1007 Maker2 Part4
    1008 Maker3 Part1
    1009 Maker3 Part2

    So with the default selection i get multiple times of Maker1 / Maker2 / Maker3 and with DISTINCT option i get no results at all.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    i get no results at all.
    probably because you were hiding the first column (the PK) in your initial query, now you just have the one column - remove any column widths values you have

    I suspect your db is not designed properly - you should have a table for makers and another for parts and a third table to join the two together which would look like your table2 but with FK's for maker and part.

    Then your combo would be based on one of those two tables depending on which you want to select - maker or part

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Your table2 contents suggests your tables aren't normalized. Can you post your database for review? You may have to zip it.

  6. #6
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    And that worked flawlessly! Can't thank you enough since it was driving me mad! As per the DB, let me make some changes and add some tables needed and I'll be sharing it in order for both of you to check and let me know your thoughts over it if you have some time to spare!

    Quote Originally Posted by Ajax View Post
    probably because you were hiding the first column (the PK) in your initial query, now you just have the one column - remove any column widths values you have

    I suspect your db is not designed properly - you should have a table for makers and another for parts and a third table to join the two together which would look like your table2 but with FK's for maker and part.

    Then your combo would be based on one of those two tables depending on which you want to select - maker or part

  7. #7
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Well, seems that I was a bit eager to celebrate... Apparently now I don't have double values but the form is refusing to update the table! Could this be due to the change I performed over the column widths?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Could this be due to the change I performed over the column widths?
    doubt it.

    You are providing no information that enables us to help you. Explain what 'but the form is refusing to update the table' means - you get an error?, wrong value? something else. What is the actual sql to your rowsource now? what field do you want to update? what is its datatype? is this field bound to the combo?

  9. #9
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    You are absolutely right. Sorry about that. I managed to solve this issue by trial and error over the row source code. I also proceeded with creating / renaming tables etc.
    Since most probably both of you are right regarding the design of the DB itself, I'm attaching the latest version. If you have some time to spare pls check and let me know of your thoughts over this. After all if i start off on the wrong foot it will be much more difficult to change it later on.

    Pls bear in mind that this is a very first draft (thus the presentation is not developed at all) and my first attempt over creating something in Access along with the fact that my knowledge is limited to the university projects that go way back!

    Again, thanking you in advance!
    Attached Files Attached Files

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    My Comments are as follows:-

    Every table should have an Autonumber and this should be set as the Primary Key

    If your table is called Vessels then use a naming convention and give all tables the Prefix of "tbl"

    This autonumber Field should have the same name as your tablename is VesselID and some use VesselPKID

    If you have a table that is related to tblVessels then the linked Foreign Key should have the same name ie VesselID or VesselFKID

    Do not use Lookup fields in tables

  11. #11
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Hello again,

    Thank you for your time and your below comments:

    Actually when I created the DB, the autonumber was the default but I encountered a strange issue with the lookup that changed the field types to number (i guess since it was creating a relationship between the field and the PK that was a number). I'll revert back to that model.

    As per the naming that's easy and will do.

    Also will correct the PK and FK as per your comments.

    Finally, I've read that Lookup can create issues but if I remove these from the tables, the only way to go over filling in data is to create the same in forms, correct?


    As per the relationships, did you by any chance had a look? Do they need any changes?

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You asked "Finally, I've read that Lookup can create issues but if I remove these from the tables, the only way to go over filling in data is to create the same in forms, correct?"

    Answer:- If you remove the Lookups from the table Level then you just need to recreate a Combobox on the Form for Data Input that Looks up the value from the specific Table.

    If you carry out all of the changes suggested you will then need to do the following: -

    1. Redefine the Referential Integrity between related tables
    2. Amend any Forms as necessary



  13. #13
    astath is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    30
    Clear. Thank you very much for your help and comments over this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Removing a foreign key value ??
    By ksor in forum Access
    Replies: 7
    Last Post: 05-20-2017, 01:42 PM
  2. Append+Update+Dublicates
    By Akcess in forum Access
    Replies: 1
    Last Post: 12-29-2016, 07:28 AM
  3. Removing Duplicates
    By hefferwolfe in forum Queries
    Replies: 1
    Last Post: 04-30-2014, 04:31 AM
  4. Removing duplicates
    By DAVID W in forum Access
    Replies: 5
    Last Post: 12-21-2011, 03:15 PM
  5. Removing bound property of combo box for certain users
    By CompostKid in forum Programming
    Replies: 4
    Last Post: 07-27-2010, 01:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums