Results 1 to 7 of 7
  1. #1
    RedEyes is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    14

    Combo Box and VBA Help

    I have created an Access 2016 database and need some advice on Primary Keys and a Combo Box.


    I think I set my database up wrong by using a Combo Box in a table. I did that so I could select a Retailer name while entering records. Trying to get the Retailer name and not the RetailerID on Forms and Reports, it hasn't been that easy. So, I have decided to start a new database, hopefully, the right way this time. I have imported into a new database the Inventory table and the Retailer table.


    With a large Inventory table doing this manually is not an option, it needs some VBA code to transfer the results of the Combo Box to a new field within the same table. I created a new field called Supplier. The Table has a Primary Key and is assigned to each record and each record has a Retailer who supplied the Item.


    Needed is a way to transfer the Retailer name from the Combo Box to the new text field in the table that is associated with that record. This way I can eliminate the Combo Box and all my headaches and build the database the right way.


    Transfer:
    Retailer name from Combo Box to new Supplier text field for each record.


    I have learned a lot by watching videos and reading, thus screen name RedEyes.


    Thank you

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    If I understand you correctly, you have lookup fields in your table.?
    If so, you already have the RetailerID, just that Access looksup the Retailername for you, which confuses the hell out of novices when they start using queries and only get to see the ID field.

    So just remove the lookup attribute and use a combo, where the bound field is the RetailerID, but the field shown is RetailerName. Just hide the first column.

    https://www.techonthenet.com/access/comboboxes/bind_index.php

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    RedEyes is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    14
    Welshgasman
    Thank you for your response, I may have been to wordy trying to explain, sorry for that. You are right about the Combo Box being in my table and I did read the link at Tech On The Net.
    That is exactly what I did some days age to get the Supplier name in the Forms and Reports, it does work ok. What I want to do is get rid of (delete) the Combo Box but before I can do this

    I need to some how move the Supplier names in the Combo Box to a new field Supplier2 (text field) in the same table without losing their references to the record they were assigned to.

    Then I can delete the Combo Box.

    RedEyes

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    But you don't need them in the table as it would be duplicating data, you already have the RetailerID in the table, you can get the Supplier name anytime in a query by joining the RetailerID to your Supplier table (which I assume has SupplierID and SupplierName fields, first one being autonumber and PK).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by RedEyes View Post
    Welshgasman
    Thank you for your response, I may have been to wordy trying to explain, sorry for that. You are right about the Combo Box being in my table and I did read the link at Tech On The Net.
    That is exactly what I did some days age to get the Supplier name in the Forms and Reports, it does work ok. What I want to do is get rid of (delete) the Combo Box but before I can do this

    I need to some how move the Supplier names in the Combo Box to a new field Supplier2 (text field) in the same table without losing their references to the record they were assigned to.

    Then I can delete the Combo Box.

    RedEyes
    No, I'm not sure you understand. the lookup is 'hiding' the fact that the ID is there not a name?
    Make a copy of the table and amend the original. Just change the property for that field and remove the lookup.
    I have one table in all my DB's that has lookups in the table, and that was my very first DB and due to lack of experience. I have never changed it, as it is static and works as it should. However I am aware of it and the limitations.

    Just change the Lookup from combo to Textbox and you will see the IDs immediately.
    I have just tested this with that table and all four lookup fields. The table only has five fields in total, the other being it's RecordID

    See the pics attached.
    Attached Thumbnails Attached Thumbnails lookups.PNG   NoLookups.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    RedEyes is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    14
    Welshgasman
    I have done as you said and I do get the Supplier ID in the text field in the new table not the Supplier name.
    The only reason for the Combo Box was to select a Supplier by Name so there would not be any spelling errors, that's all.
    Being my 2nd database I thought I was making it easier with a Combo Box, but didn't realize it until I started making forms and reports.

    I really want to thank for your help and time you have given me.

    RedEyes

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Now in your forms, you use pretty much the same process with the combo, but hide the ID and show the name.
    I *always* put the ID first, then any other fields after that and bind to that first field, which for me is always the ID of the record.

    However those fields should be numeric as Long Integer to hold the ID, which would be the AutoNumber form another table.?

    Good luck with your project.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 5
    Last Post: 05-31-2017, 08:58 AM
  2. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  3. Replies: 3
    Last Post: 01-29-2016, 08:52 PM
  4. Replies: 2
    Last Post: 10-21-2014, 07:57 AM
  5. Replies: 4
    Last Post: 08-16-2011, 05:54 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