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
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
do yall NOT recommend using lookup for fields at table properties level?
The only benefit of declaring lookups at the table level is that you save a few seconds when adding the control to your form. There are a host of disadvantages, so on balance there is just no good reason to include lookups at the table level.

But your trouble has nothing to with that. Your trouble occurs because you're trying to put a Text value (i.e. "Staff") into a Numeric field (STD_FKStaffName is a Number field). That's certainly not a bug. If I change the Datatype of STD_FKStaffName to Text (after removing the relationship), then I can change the value.

For a humorous take on the matter, see this: http://access.mvps.org/access/lookupfields.htm
0
developingprogrammerAuthor Commented:
whao LSMConsulting -------------------------------------------> SUPERB!!!!

= ))))

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

i'm reading the link now = )
0
developingprogrammerAuthor Commented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

mbizupCommented:
I completely agree with LSM regarding lookup fields...

But i think the issue with your combo box is that IF you decide to use lookup fields in your table, your combo's row source needs to *match* the rowsource of your lookup field.

ie: if you are using a Value List in the table, your combo needs to be a value list combo.

If you are using a table/query rowsource in your table you need to use a table/query rowsource in your combo.

Check out the value list settings here - both in the form and in the table:
Problem--1-.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
... and THAT in itself is yet another reason to steer clear of lookup fields in your tables ;-)
0
developingprogrammerAuthor Commented:
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!! = ))
0
mbizupCommented:
----> 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!
0
developingprogrammerAuthor Commented:
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!!!! = )))    )
0
mbizupCommented:
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.
0
developingprogrammerAuthor Commented:
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! = ))
0
developingprogrammerAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.