Unbound Combo on form to show a default value

[d9pierce1]

Test Company.zip

Good morning all,
I am trying to get a default value on an unbound combo "CboCompanyType" on my form "CompanyFrm". I don't understand why this isn't working as it works for the first record but not for the next … The data is there if you dropdown, but it doesn't display in the combo. I have the on current set to requery that combo and such. Have been working on this all night and I just cant figure it out? Have tried everything I know of to do this. Have rebuilt the subform several times with no success. The sub form works, the data is in the combo on my primary form, but it doesn't requery for some reason. Would really like some help!
DB is attached.


Thanks
Dave

[davegri]

Modified the relationships as above so that the queries work.
Don't understand the reason for the Company Type combobox. It's unbound and you have no event procedure for it, so it is functionally nothing more than a text box.

[ranman256]

the unbound combo box is limited to the CoID on the form (to pull only those options in the combo query)
co 1 has 2 types, so they show in the combo, and can have a default.
co 3, Paypal has 0 types, thus no items in the CoType query can show.

[d9pierce1]

Hi, I changed the relationships per above example and still does not work? I get the same results as I had before? I do intend to have some event code shortly however, I spent most of last night trying to figure out why this didn't show me what I called for in the default. I just didn't want to write a bunch of events before I got this to work. Any other suggestions.

[d9pierce1]

the unbound combo box is limited to the CoID on the form (to pull only those options in the combo query)
co 1 has 2 types, so they show in the combo, and can have a default.
co 3, Paypal has 0 types, thus no items in the CoType query can show.

Yes, this is correct so why are the ones that have a YES value not showing up in the combo as default? This just baffles me.
Thanks

[davegri]

Hi, I changed the relationships per above example and still does not work?

Maybe I changed something else too, while I was testing. Here's what I wound up with that worked for me:
d9-davegri-v01.zip

[d9pierce1]

Thank you davegri
I see you changed the dlookup to control source! Wow, thank you so much.
Thanks
Dave

[davegri]

I see you changed the dlookup to control source! Wow, thank you so much.

If you later need to bind the combobox, you cannot with that configuration, but you can if you move the dlookup to the form_current event.

[d9pierce1]

Company - Copy.zip

Could I please get davegri or someone to take a peak at this db (Attached)?

davegri looked at it and got me on the right track. I made the necessary adjustments, added some additional info to forms, and the data is there, queries working well, but it just doesn't display and requery as it should? On open, the form displays all the correct info, if I change the CboLocation, it doesn't update the CboAddressType and CboPhoneType dropdowns? If I go from record to record, it doesn't update them all as it should? I have tried everything I can think of to solve so reaching out for some expert knowledge. I think it may be in my current_event in the dlookup statements? Maybe I an not using requery in the correct place? I just don't know????

Thanks you,
Dave

[davegri]

You only want the CompanyID from the form as criteria. Remove the others.
Location2PhoneTypeQry
Location2AddressTypeQry
Company2LocationQry

Again, your comboboxes are not bound, so if you move off a record in the form and move back, the form reverts to the previous stored values.

[d9pierce1]

Thanks davegri
I made those changes and then it gave me all the records to each of the combos per CompanyID. So I removed the Forms!CompanyFrm!CompanyID and put in LocationID

Forms!CompanyFrm!LocationID which gave me all the records per the CompanyID in all locations.

What I am trying to do here is a Company can have more than one location, and locations can have more than one address and phone. I want to filter? the location by the Company and filter the AddressType and PhoneType by the location per the company. I hope this makes sense.

You also mentioned to bind these I need to put the DLookUp in the current_event on the form which I did so is that not what you were referring to as I do want them to change between records to show the one selected to "Preferred" in each combo as like the default. I still need to make an update query and some code to only allow one "Preferred" per Location, Per AddressType. and PhoneType….. but that is in the works along with the dblclick and notinlist events.

Thanks
Dave

[d9pierce1]

Could I possibly need a SQL SELECT Statement to accomplish this rather than a dlookup? I am pulling my hair out here, LOL....