Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11

    Why is a lookup defaulting to number not text

    Hi



    I have included a sample of the problem I am encountering.

    I have a look up table but it is defaulting to a number when I select it.

    I have asked this question before and people have said dont use look up tables. This is fine but I do not know what do use as an alternative.

    This maybe something really simple.

    Anyway in the example I want it to be created in table 1 (Database) which works.
    I then want it to pull through into table 2 (Make Live) which works
    Then on table 3 (book course) I want it to pull through the course name from the "Make Live" table.

    I can see that on the table 2 (Make Live) it has already defaulted to number.

    Whatever I do it will always default to number. I have deleted the relationships and restarted.

    Any help would be appreciated on this frustrating but I am sure simple problem.

    Many thanks in advance

    Rachel Trial1.accdb

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you have database.coursename (set as text)
    but you have tMakeLive.CourseName as numeric. You should either make it text to store the name or use it to store the CourseID (numeric)

    Don't just call an autonumber field ID, give it a little more description: CourseID


    (a table called 'database'?)

  3. #3
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11
    I do change it to text but it reverts back to number.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the field type is NUMBER. change it to TEXT

  5. #5
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11
    I have changed it to text! I have deleted all the relationships, redo the look up and it changes back to number. Try it yourself, I wish I know what the problem was

  6. #6
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11
    Trial2.accdb

    I have changed everything to text and still on the booking table the course name is still a number the id number even though the id number is set to text!

    See new attachment

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

    Can you explain what you are trying to do with this database?

  8. #8
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11
    Yes I want the drop down on the Book Course Table to pick up the data from the course name column on the make live table.
    As you can see it is just populating he ID number.

    It is very frustrating!!!

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have asked this question before and people have said dont use look up tables.
    you are misunderstanding. What people are saying is don't use them in table design as you are. Perfectly OK to use them in forms and reports (which you are not)

    Tables are for storing data, not presentation which is what a lookup is doing.

    As mike asks, what are you trying to do? Without knowing, not possible to advise you the best way to organise your data

  10. #10
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11
    I am wanting a form with a combo box on it linked to the book course table. When selected I want it to show all the courses that are listed on the make live, course name column.

    What is happening is that it is displaying the ID number from the make live row. Which is obviously no use.

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

    You are doing things all wrong.

    You need to read up on tables and Autonumbers.

    You should never use Lookup fields in table design.

    I have attached a quick example of how you should use Forms for Data Input.
    Attached Files Attached Files

  12. #12
    RikApple is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Location
    Leeds UK
    Posts
    11
    Mike

    Thank you for this, I will have a quick look and see if I understand, if not I will skype you as suggested, thank you!

  13. #13
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    It is because your using lookup fields in the tables which should not be done. You should also not have spaces in field names. You also need to create forms to enter data and not be editing the tables directly.

    I have corrected the lookup fields but not reentered the correct names, etc, I also took out some of the spaces in the field names. I deleted the relationship yoiu had in order to modify the tables so you'll have to put that back in.

    Create forms to enter the data and it should be OK.



    Trial2-Mod.zip

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    PMFJI Dave but why are you advocating a Number Primary Key ??

  15. #15
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Why not? It makes sense to let Access handle the Primary key as a number.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-07-2019, 01:42 PM
  2. Replies: 2
    Last Post: 01-05-2018, 04:35 PM
  3. Replies: 1
    Last Post: 03-31-2015, 07:37 AM
  4. Converting lookup text field to number for primary
    By Ruegen in forum Database Design
    Replies: 4
    Last Post: 09-11-2013, 08:23 PM
  5. Replies: 1
    Last Post: 07-12-2012, 12:09 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