Unbound Combo on form to show a Values dependent on another combo

[d9pierce1]

Views: 20 Size: 515.4 KB">Company - New.zip

I need some assistance in figuring this out?
I have attached a db and what I am trying to do is:

A company can have more than one location and a location can have more than one address and phone.
The data is there and davegri gave me some assistance on it prior in a post but I am so stuck and need some assistance.
Everything somewhat works except going from one record to the next. It just doesn't show up in the combo unless I select it.
I need to figure out how to make these work correctly between records.
Could be my on current events code or maybe I need a select statement? I just don't know.

Thanks
Dave

[ranman256]

If a co has > 1 location, then youd have that many choices in the tCo table:
CoKey, CoName, CoAddr...
123, Acme, 123 Main St
144, Acme, 444 W 9th ave
888, Acme, 888 Oak St

youd need either:
a combo to see all co names and addrs

Acme, 123 Main St
Acme, 444 W 9th ave
Acme, 888 Oak St

BoA , 54436....

then upon choosing, the CoKey would get inserted

OR
combo to pick Co Name:
Acme
BoA

combo 2 to pick WHICH Acme

123 Main St
444 W 9th ave
888 Oak St

Then the CO TYPE combo would be based off CoName and its choices set in Company2CompanyTypeTbl

[d9pierce1]

I beleive I have the cbo’s As you spoke all ready there? The CboLocation is dependent on the company I’d, and the CboAddressType and CboPhoneType are dependent on the CboLocation. The issue I am having is displaying and switching from record to record.

I can’t get the combos to requery on current and next records... but the data is there...

[ssanfu]

A company can have more than one location and a location can have more than one address and phone.

I'm really confused after looking at the relationship window. I can't see the combo box that is not changing.

I did notice that CompanyTbl and LocationTbl have a 1-to-1 relationship, not a 1-to-many like you stated. I don't see AddressTbl in the relationship window nor any FK field to LocationTbl

[d9pierce1]

Hi ssanfu, Thank you,
Now I am sort of confused? I don't have any 1-1 relationships, just 1- many and many - many in my relationship window. The Cbo's I am speaking of are Unbound on the form, not in the tables.
The AddressTbl is not in the window yet as I haven't started using it as of yet, just the AddressTypeTbl, I have not went that far yet as I couldn't get this to work as of yet so no real reason to add more
to forms before I figure this out. Basically I want my CboLocation to be dependent on the CompanyID, and the CboAddressType and CboPhoneType to be dependent on the CboLocation. I recently took some
access coarses but didn't cover all of this. Also, I was taught you didn't need the FK and to just use the PK as you were not ever going to use it. Not sure if that is correct but it worked for the teacher?

[ssanfu]

I opened the relationship window and hid all but 2 tables.

PK linked to PK????
Sure looks like a 1-to-1 relationship to me (but without RI)

Also, I was taught you didn't need the FK and to just use the PK as you were not ever going to use it. Not sure if that is correct but it worked for the teacher?

The PK field links to a FK field. That is how tables/records are linked.
If you don't have a FK field, you cannot link records in tables.
One company (PK) can have many locations (FK), but one location has one company. 1-to-many relationship.
This is how relational databases work.....


I would recommend that you step back and re-evaluate your table designs and relationships......

[d9pierce1]

Thanks, that must have been a mistake as yes, that won’t link! Sorry for confusion as I missed that