Update form text box w/ SQL result

[mightyteegar]

I am creating a form for 3rd-party service contracts, and I have two tables called tblServiceProviders and tblServiceDescriptions. tblServiceProviders has a 1-to-many relationship with tblServiceDescriptions; i.e., each Provider can have multiple Descriptions (real world: product offerings). So for example, ServiceProvider "Acme Corp" might have ServiceDescriptions "AcmeCare", "AcmePremium" and "AcmeInstall". My database is normalized, so tblServiceProviders has an ID field that is related to a field called ProviderID in tblServiceDescriptions.

Each ServiceContract includes, in addition to customer name and some other stuff, one and only one ServiceDescription. The ServiceDescription field in the ServiceContract form is a drop down box called comboSvcDesc and it contains all of the ServiceDescriptions in the database. I also have a non-editable text field on the form called txtSvcProvider.

What I want is to have the name of the ServiceProvider ("AcmeCorp") automatically populate in txtSvcProvider whenever the comboSvcDesc box is changed. So for example, if I change comboSvcDesc box from "WidgetCare" to "AcmeCare", I want txtSvcProvider to change from "WidgetCorp" to "AcmeCorp". My thought is that I should be running a SQL join query to get the related value "AcmeCorp", but for the life of me I can't figure out how to make this work in VBA, or if there's maybe an easier way to do this. Thoughts?

[pbaldy]

Is this what you're after?

BaldyWeb - Autofill

[mightyteegar]

Yes it is. I had to use a VBA module instead of a Control Source, but it worked perfectly. And I learned about how to hide columns in combo boxes using 0 width, so that was an added bonus. Thank you!

[pbaldy]

No problem, and welcome to the site by the way!