Results 1 to 10 of 10

Thread: frustrated

  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    frustrated

    hello all
    i have a combo box (referencing another table) in a table that shows an employee's name and then his/her employee ID number. ex. Barnett, Tom 0001
    i want to be able to pick by the name (type "b" and Barnett would highlight) but enter "0001" in the table. i should be able to have name as column 1 and id as column 2, and have column 2 set to be the bound column... this is not working. somebody please help!

    thanks in advance

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: frustrated

    Is the id field the primary key for the employee table? You should be able to use the lookup wizard in the second table to create a link between the two tables with the primary key field hidden. Here's how the properties should look for your lookup field in the lookup tab:

    Display Control=Combo Box
    Row Source Type=Table/Query
    Row Source=Your SQL Statement
    Bound Column=2 or whichever one is the ID
    Column Count=2
    Column Heads=No
    Column Widths=1";0"


    Default the rest

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: frustrated

    try column 0 and column 1
    instead of 1 and 2
    HTH

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: frustrated

    Hi Jerry,
    Although you would generally use Column(0) and Column(1) in VBA for a 2 column listbox, in the properties dialog in form design you would use 1 and 2 respectively to refer to them. You've gotta love that consistency! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: frustrated

    i have employee name as column 1, id as number 2. if i put bound column to 0, all fields are blank. if i use column 1 as bound, it puts the name in the field (which is expected).
    if i put column 2 as bound, it changes all the fields to have the name in the field - ARRRGGHH!! (it should insert the ID number) why isn't it doing that????

    thanks for your help

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: frustrated

    No, you bind column #1, which is the same as column(0). That's what Rory meant. When you reference the columns collection, you start with column(0) but when you specify the column to bind, you start with the ordinal position of the column.

    Your real problem is that you appear to be confusing what you see on the screen and what is actually stored in the field. If you want to store the value in column 1 but see the value in column 2, you bind column 1 and set its visible property to false. Then the value in the field will be the value in the first column, but the display will show the value in the second column, i.e., the first visible column in the combobox. If you bind column 2, then that is the value that will be inserted into the field, which doesn't sound like what you want to do.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: frustrated

    I think you're trying to do something that comboboxes can't do. A combobox displays the first visible column when you select a value. It doesn't matter which field is bound, it's the first visible column that gets displayed.

    If you want to do something different, have the combobox list the names with the bound employee number hidden by setting it to zero width BUT have a textbox bound to the same field underneath the combobox. Make the combobox's backstyle transparent. That way, you'll see and select from the employee name list, but when you move off of the combobox, the underlying textbox will become visible and display the employee number.

    Wait a minute. Are we talking about forms or tables? If you're trying to do it in a table lookup, you're out of luck. If you're trying to do it on a form, use the technique I just described.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: frustrated

    well, i was talking about tables... looks like i am out of luck.. thank you all for your help..

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: frustrated

    Don't give up yet. I was able to create a field in a table related to an employee table which displays the name in datasheet view from the first column but stores the employee id in the table from the second column. What may be tripping you up is that even though the id is being stored in the table you still see the name displayed in datasheet view. You can test if the id is being stored by opening a new report in design view and base it on the table where the name is displayed but the id is stored. Add a textbox - not a combobox - to the report and set it's control source property to the name field. Then preview the report and you should see the id displayed on the report and not the name.

  10. #10
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: frustrated

    thanks paul, but i think i will just worry about it when i design the form... i will have more control over it then...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •