Link to home
Start Free TrialLog in
Avatar of developingprogrammer

asked on

ValueList combobox bound to table field with lookup problem

hey guys!

check out this example database i've attached! = )

in the underlying Standard table, there are 2 fields - FKStaffName and FKStaffName2

FKStaffName in the table properties has a lookup attached to the field.
FKStaffName2 in the table properties has a NO lookup attached to the field.

the reason for the lookup is because FKStaffName (both 1 & 2) are populated with the meaningless PK from the staff table, and for the users i want them to see the name instead of having to key in the meaningless PK which they won't know.

with all the settings the same except for the lookup table,

FKStaffName   ---> doesn't work ---> bound field has a table properties lookup
FKStaffName2 ---> works            ---> bound field does NOT have a table properties lookup

1) how do i use a valuelist combobox bound to a tablefield with lookup property?
2) do yall NOT recommend using lookup for fields at table properties level?

regardless of the answer to question 2 guys, even if yall do NOT recommend using lookups at table level, i really do need or rather hope for an answer to question 1. reason being - if i stay away from using something, i want to know the problem, understand the issue, determine if it really is a bug or why it's not working. that will really help me understand Access better.

thanks guys!! = ))
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of developingprogrammer


whao LSMConsulting -------------------------------------------> SUPERB!!!!

= ))))

is there anyway i can use a valuelist then with a numeric field?

i'm reading the link now = )
ok haha, i read a few sentences of it and decided to ABOLISH ALL MY TABLE LOOKUPS!!

oh LSMConsulting, so sorry, for my combobox, the value list is ";1;2".

it doesn't have the word "Staff" in it. but "Staff" still appears. why is this so? that is causing the problem. i misread thinking that the combobox was taking 1 as a text and thus couldn't internally convert 1 to integer.
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
... and THAT in itself is yet another reason to steer clear of lookup fields in your tables ;-)

then it defeats the entire purpose. argh sigh. you know i wish sometimes i could download all the advice from yall past present and future to my head FIRST before embarking on my silly database.

REWORK!! going to go through everything and remove all the EVIL LOOKUPS!!!!!!

thanks so much mbizup!! = )))) that solved the problem!! = ))
----> then it defeats the entire purpose.


<<  remove all the EVIL LOOKUPS!!!!!!  >>

I can think of a few regulars here who would be really proud of you.  LOL!
ha mbizup, of course i'll follow your advice! i'm seek all your help here = P (unless there are weird things that i'm experimenting to see how many ways i can fail!! as a learning experience!! = ))    )

mbizup i went to think for awhile and i was wondering how come the rowsourceTYPE must be the same? cause when my rowsource was the same, there was still that problem. well this is just more to understand Access and its intricacies - leaving no stone unturned!! = ))

i thought that rowsourcetype table / query is incompatible with rowsourcetype valuelist because valuelist is really one long string visually formatted by column count to impersonate a multicolumn table structure. so i went to try using a single row table / query lookup rowsourcetype so that it will matching with the valuelist but this still couldn't work.

internally, what's causing Access to require this similar rowsourcetype?
is there an internal check to reject different rowsourcetypes due to the possibility that the table / query rowsource could be multicolumn and the valuelist just a long 1 column string internally? (from what i've experienced of Access doesn't seem like the developers put in that many checks ha = P  so sorry any Access developers but i still LOVE Access!!!! = )))    )
From what I understand, the rowsource in the underlying table actually determines in part your combo's rowsource property. (I'm not 100% sure how this works)

Just for kicks, in the sample I uploaded try all of the following:

-  Keep the value list for the combos rowsource type, but remove the actual values altogether.  When you open the form, you'll see the values that were set in the underlying table.

- Again with Value List for your combo's rowsource type, set your table property back to table/query with "Staff" as the rowsource.
('Staff' will appear in the combo's drop-down)

- Set the rowsource property in your table to SELECT STF_StaffPK, STF_Name FROM Staff, setting the form combo to ValueList and see what happens to your drop-down list...

So with that type of interraction, you need to coordinate the combo in your form with any lookups in your table.

By 'Matching' rowsources, I don't mean an exact match all around --- but rather coordination.
ok thanks mbizup! i will definitely do this moving forward (a bit too tight on time and creative energy now!)!! = )) saving this to my knowledge base! = ))
mbizup just to let you know when i tried removing the lookup comboboxes from the table and then tried to use comboboxes on the form for that field, the table was corrupted. it was still phantomly referencing the comboboxes. i created another table with the fields and rows ported over and it worked fine.

don't have time to isolate and do a demo database but ya, i'm quite sure it's messed up!