Results 1 to 8 of 8
  1. #1
    MikeEmerald is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    13

    Question Help with forms - beginner problem

    Hi all,

    Looking for a solution to my problem regarding my text box on my form returning a number instead of a value.
    Below are my tables:

    Raw Material
    ID(KEY) Raw Material CAS No. Primary Supplier Suppliers
    1 Apple 001 Laura Laura
    2 Orange 002 Mike Laura, Mike
    3 Strawberry 003 Ryan Laura, Mike, Ryan

    Primary Supplier is a lookup to the table Supplier and Suppliers is a lookup with multiple values to the table Supplier

    Supplier
    ID(KEY) Supplier
    1 Laura
    2 Mike
    3 Ryan

    I then do the exact steps:
    Create Blank Form - Combo Box - Table:Raw Material - Selected Fields: ID, Raw Material, CAS No, Primary Supplier, Suppliers.
    Properties of the Combo Box - Change the column widths to "0cm;2.542cm;0cm;0cm;0cm" so it hides all except Raw Material. Change list width to auto.
    Combo box source: SELECT [Raw Material].[ID], [Raw Material].[Raw Material], [Raw Material].[CAS No], [Raw Material].[Primary Supplier], [Raw Material].[Suppliers] FROM [Raw Material];
    Insert text box, change source to =[Combo9].[column](2) <--- This to show the CAS No
    Insert text box, change source to =[Combo9].[column](3) <--- This to show the Primary Supplier
    Insert text box, change source to =[Combo9].[column](4) <--- This to show the full list of Suppliers.

    However the Primary Supplier for Orange for example shows 2 instead of Mike and the Supplier List shows up as 1,2 instead of Laura, Mike.

    My question is how can I can stop it showing up as the number id associated and instead as the Name.

    Thanks,

    Mike

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "multiple values to the table Supplier"
    THAT is your problem, I think. Check this out http://access.mvps.org/access/lookupfields.htm
    Try Combo9.column(3).Value 99.9% of the time the value property is not required since it's usually the default property of a control. In this case, you're accessing an item number in a multi value field if you don't use it. I suspect you could benefit from a naming convention as well since combo9 is not very helpful.
    https://access-programmers.co.uk/for...d.php?t=225837
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    remember in vb, column# start with zero.
    so if the property in the combo has 3 columns,
    column 2 on the property viewer is column(1) in vb code.

    dont use [Combo9].[column](3)
    use Combo9.column(3)

  4. #4
    MikeEmerald is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    13
    =[Combo9].[column](3).[Value] returns #Error in my text box.

  5. #5
    MikeEmerald is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    13
    Quote Originally Posted by ranman256 View Post
    remember in vb, column# start with zero.
    so if the property in the combo has 3 columns,
    column 2 on the property viewer is column(1) in vb code.

    dont use [Combo9].[column](3)
    use Combo9.column(3)
    If i remove the [ it auto puts back in

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    there are no square brackets around my example (.[Value]), and never should be for a property reference, so if Access is putting them in there, something doesn't make sense about that. What I am fairly certain of is the reason for the values you're getting. If you cannot get the source syntax without .[Value] then you'll likely have to take a step back. The best thing you can do is get rid of the mv fields. Failing that, you might have to use a sql statement as the source. But why make a textbox display something that's already part of a combo's rows? Why not just show that column value in the combo?
    Last edited by Micron; 02-20-2017 at 10:53 AM. Reason: grammar

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Because of the Multi Valued Field (a major no-no, IMHO) you might want to see Allen Browne's site
    http://www.allenbrowne.com/func-concat.html

  8. #8
    MikeEmerald is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2017
    Posts
    13
    Works fine now i removed the relationship lookup. thanks all

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

Similar Threads

  1. Replies: 7
    Last Post: 03-23-2015, 07:36 AM
  2. Inventory Problem Beginner Database
    By joe.ise in forum Database Design
    Replies: 38
    Last Post: 08-27-2013, 12:16 PM
  3. Forms Problem
    By drunkenneo in forum Forms
    Replies: 1
    Last Post: 07-15-2013, 07:26 AM
  4. Beginner thread, 1 problem when using forms
    By c.yyang in forum Access
    Replies: 8
    Last Post: 06-22-2013, 11:26 PM
  5. Problem with tables (beginner)
    By alouest in forum Access
    Replies: 5
    Last Post: 04-05-2012, 12:08 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