HELP! How to auto-populate multiple fields using a single combo-box

[tobydobo]

dude if you could improve this, that woudl be awesome! I will remove the data and get it to you asap. Thanks.

[tobydobo]

attached. Thanks.

[June7]

Spent less than 5 minutes reviewing your project and see so many issues.

1. Contacts table
This is to keep track of Techs? One record for each tech. Has similar fields for Manager, Manager2, Manager3. The SupportedSites(s) field is for multiple sites? I abhor multi-value fields. Then how would you indicate each site type in the SiteType field? More similar fields SiteAddress1, SiteAddress2, SiteAddress3, MSO1, MSO2, MSO3. Another multi-value field NSAGroup(s)? Is the company field necessary? These techs are not in your company? If managers are assigned to sites and techs are assigned to sites, then manager info does not belong in tech's record.

2. Sites table
Has fields that replicate Contacts table (or is it vice versa). This is not how a relational database should work. These tables have no relationship. Proper structure of many-to-many data will require 3 tables as I described earlier. I think you have more than one many-to-many relationship involved (sites and techs as well as sites and managers).

3. Settings table
No idea what this is for.

4. Shared Documents and User Information List tables
Something to do with SharePoint. Never used it.

I suggest you do a complete rethinking of what you need this database to do. What is it purpose? To keep track of contacts or to keep track of sites and related data such as who (managers and techs) is assigned to the site? Back up and review the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/. Get a better handle on relational database concepts.

[tobydobo]

Thanks for the analysis!

First of all....I used the Contacts template from MS, which included various tables and queries that I have not used....the 'settings' table for example. I also publish this db to our team's sharepoint, so that everyone can use it over the network, hence the Sharepoint entries. The sites table is the table I manually created to have my form autopopulate, which now it does thanks to you and Jbaldy. As far as the contacts table, that is the table that was created with template, and I just built off that....adding more fields to suit my purposes. To explain the reasons behind the redundancy is basically this:

I am a queue manager that supports multiple remote sites for the same corporation. All my techs are 3rd party dispatch contractors, hence the additional company name field. We have at least 3 techs per site that share the same reporting manager through their company, then 2 additional support managers that service their region (manager 2 and manager 3). Then each site has at least 2 support contacts (MSO's).The SupportSite(s) field will be replaced by my new combo box so that is going away. The NSAgroups has to be a multi value field as some of the tech's support multiple sites. The sites that are grouped together though will all have the same "site type"...so that is not an issue.

....So yea a bit confusing, but now you may have a better understanding of what I need to do. The tech's record is the most important as that is what I work off of mostly. Having all the other data just makes it easier to see everything we need at a glance when we look at one record individually. The contact list form also is helpful since you can sort the techs by sites or managers.

I understand the importance of the relationships, but that is something I dont really know how to do. So far the db seems to be functional enough for what I need to do, and already saves a ton of time compared to the worksheets we were working from. The only challenge I have now, is to keep the data from saving immediately when I select the option from the drop down.

[tobydobo]

Ok one other request....

How do I make the combo box selection itself save to the appropriate field in my table? The combo box is currently unbound but I need the select to be saved along with all the other data being saved in the other fields. I tried to change the control source to the appropriate field that it should save to but all it does is put a number in the field. Assuming thats the number of the selection in the column of the of the combo box. Hope that makes since.

[pbaldy]

It sounds like you did the correct thing. Normal procedure is to save the ID field and not any of the related fields. If you don't want to save the ID field, leave it unbound and use the other technique to save the other fields. You could also change the bound column property of the combo to save some other column.

[tobydobo]

That did it! Thanks Pbaldy! So while I have you....any way to prevent it from saving the moment I close the subform? Maybe another event I need to modify? I want to be able to open the subform, make the selection, have it autopopulate, but then only save when I click the 'save and close' command button.

[pbaldy]

I haven't looked at your database, but probably not. The nature of bound forms is to save when you move off of them. In a form/subform situation, that means the main form will save when you move focus to the subform, and the subform will save when you move focus to the main form. You're looking at some sort of unbound solution if you don't want the subform data to save even if it's closed or focus moves off of it. Unbound forms give you a lot more control, but are a lot more work to set up.

[tobydobo]

admins...please mark this thread as solved...got my combo box off and running.

[tobydobo]

Hello again....

I just created another subform with unbound fields. I just want to use the subform to display information using the same method you described in your first reply. For some reason now though when I go to enter =Combo483.Column(x) in each of the fields, it doesnt do anything...it just displays '#Name?' is each of the fields. Is there something else I am missing? Also to note, I created by copying my other form as a new form, just renamed it and modified the fields. I also removed the 'on load' embedded macro (which was inherited from the original form)for this one as I only want it to display information.

[June7]

Cannot use the column index in controls or Access queries. Only works in code (VBA for sure, don't know about macros).

EDIT: Not entirely correct, see later posts.

[pbaldy]

Did you put in a valid number instead of x?

[pbaldy]

June, you can definitely use the column index in a control source.

[tobydobo]

Yes I put the column number in just like I did before. My other form however is set to save which means I had to put the info in through the code builder. On this new form I just want to display info like you helped me do in the beginning....which only entailed specifying the combo box column in each of the text fields.

[pbaldy]

The error implies Access doesn't understand something there. Double check the name of the combo. If that's not it, can you post the db?