Avatar of developingprogrammer
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

Question:
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!! = ))
Problem.mdb
Microsoft Access

Avatar of undefined
Last Comment
developingprogrammer

8/22/2022 - Mon
SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
developingprogrammer

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

= ))))

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

i'm reading the link now = )
ASKER
developingprogrammer

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.
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mbizup

... and THAT in itself is yet another reason to steer clear of lookup fields in your tables ;-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
developingprogrammer

OH MY GOODNESS.

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!! = ))
mbizup

----> then it defeats the entire purpose.

Bingo!

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

I can think of a few regulars here who would be really proud of you.  LOL!
ASKER
developingprogrammer

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!!!! = )))    )
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mbizup

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.
ASKER
developingprogrammer

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! = ))
ASKER
developingprogrammer

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!
Your help has saved me hundreds of hours of internet surfing.
fblack61