Help With What Is (Probably) A Simple Problem

[shoelesscraig]

Ok, I need some help if someone out there is willing. I've created a database for my company to keep track of projects/proposals that we do. Before we begin, let me first say that I'm very new to Access and have taught myself what I know through trial and error and reading this forum. I'm sure there is a better way to do what I'm doing, but for now I'd like to just forget that and see if I can accomplish what I'm trying without reinventing the entire thing. I am the ONLY person who will ever use this database...it's just for me to track my projects. Since it originally had company information in it with real names and information, I've have changed the name of everyone to "Person A", "Person B" etc. I don't think it's a big deal if this info were to be posted, but I'd hate to find out my company thinks otherwise. So I'll post the database for anyone to help if they will.

Here's an overview of the database:

1. We have account managers that work for us. I put their names in a table (tblAM).


2. We have different companies (clients) that we do work for. I put those in a table as well (tblCompanyInfo).
3. Each account manager is responsible for some of the client companies we do work for. Each client company deals with ONLY 1 of our account managers. Same account manager every time. So, I created foreign keys to designate how client companies and account managers relate to one another.
4. We have 4 branches for our company, I put those in a table (tblBranch).
5. Each account manager works out of 1 of the 4 branches. I used a foreign key to designate which branch each account manager works from.
6. Lastly, at each client company, we have certain contacts we deal with regularly. I put their names in a table (tblCoContacts) and used foreign key to designate which company they work for. For example, if you look in the tables, "Contact A", "Contact F" and "Contact K" all work for Company A. When we do a project for Company A, we could be working with Contact A, F, K or a combination of the 3.

I give this information just as a overview of how it works. I've got all of this done with no problem...Next, I created a form that enters info into another table (tblProjectProposalSummary). It has a million boxes for all kinds of data and I enter each record from this form. Works good.

However, part of the form has a drop down where I select which client company the project is for. I figured out (with help from people on this forum) how to make that combo box have multiple columns (only 1 of which is visible). In the combo box I select the name of the company we are doing the project for...for example "Company A". It does a query and looks up the account manager for that company (which is Manager A) and what branch that account manager works for (which is Branch A). I display these columns from the combo box in 2 other text boxes on the form. This part works fine as well.

Here's where I need someone way smarter than me...In addition to the above (it needs to stay the same), I have 3 combo boxes at the bottom right of the form. What I would like is, when I select the company up top, for example Company A, in the 3 combo boxes down below, it ONLY shows me the names of contacts that work for Company A...which in this example would be Contact A, Contact F, and Contact K. If I had chose Company B up top, then I would want it to show me the contacts for that company, which would be B, G, and L. Most of the time, we only work with 1 contact from the company, so the 2nd and 3rd drop downs would be unused. I put them there just in case.

I can't figure it out! I've tried and tried and tried. I've Googled stuff. I've YouTubed stuff. Can't get it. Seems simple, and I'm sure it is...but I can't do it. Is there anyone that will show me what I'm missing? I'm pretty good at learning most things on my own, but I'm missing something...

[pbaldy]

Can't look at the sample right now. Does this help?

http://www.baldyweb.com/CascadingCombo.htm

[LaughingBull]

when you created the select combo box did you select to get the info from this form or from a table? if you select from the form it will bring all the info on that record I noticed when I select a record it changes the 2 boxes on the right and nothing else

[shoelesscraig]

when you created the select combo box did you select to get the info from this form or from a table? if you select from the form it will bring all the info on that record I noticed when I select a record it changes the 2 boxes on the right and nothing else

Honestly, I'm too much of a noobie to be able to answer that question...I mean, here is the Row Source for that first dropdown, the one where I choose the company name from...

SELECT tblCompanyInfo.ID, tblCompanyInfo.CoName, [tblAM].AMLastName, [tblAM].AMFirstName, tblBranch.Branch FROM tblBranch RIGHT JOIN (tblAM RIGHT JOIN tblCompanyInfo ON [tblAM].ID=tblCompanyInfo.CoAM) ON tblBranch.ID=[tblAM].AMBranch ORDER BY tblCompanyInfo.[CoName];

Keep in mind that this part works just like I want it to...for each record, when I select the name of the client company that I'm working for (I'm talking about this dropdown above ^), when the record is created in my main table (tblProjectProposalSummary) it puts the CoName into the table as a number (the ID number of the company from the tblCompanyInfo). This is fine. So I guess I need the 3 comboboxes down below in the form to say: For THIS record, the CoName is "1", so in the table tblCoContacts, ONLY show the contacts that have a value in the ContactCo field that is equal to "1". Does this make sense?

Sorry, I don't know how to explain it better.

[shoelesscraig]

Can't look at the sample right now. Does this help?

http://www.baldyweb.com/CascadingCombo.htm

I seriously appreciate the response, but honestly no...doesn't help me. I get how to create cascading boxes for something like country, state, city...I guess I'm just having a hard time relating that to my fields. I think part of my issue comes from the fact that my first combo box isn't just as simple as "country" from a table full of country names. My first box is a combo with multiple columns. I guess to an expert, this is pretty much the same thing and probably a no-brainer...but it's got me stumped.

[June7]

So you have a table that associates contacts with companies, like:

tblCompanyContacts
ContactID (PK)
CompanyID (FK)
ContactFirstName
ContactLastName
etc

The RowSource for the Contacts combobox could be like:

SELECT ContactID, ContactLastName & ", " & ContactFirstName AS ContactName FROM tblCompanyContacts WHERE CompanyID = [cbxCompany];

[shoelesscraig]

So you have a table that associates contacts with companies, like:

tblCompanyContacts
ContactID (PK)
CompanyID (FK)
ContactFirstName
ContactLastName
etc

The RowSource for the Contacts combobox could be like:

SELECT ContactID, ContactLastName & ", " & ContactFirstName AS ContactName FROM tblCompanyContacts WHERE CompanyID = [cbxCompany];

Ok, so I looked at this, but don't understand some things: My tables and fields are named slightly different than your example. They are the following:

tblCoContacts
Contact ID (PK)
ContactCo (FK)
CoContactLN
CoContactFN

My combo box where I select the Company name on the form is called "cboCoName".
The first of the 3 combo boxes that I'm trying to make this happen for is called "cboCoContact1" and it's control source is "CoContact1".

So, based upon your example I tried making the row source for cboCoContact1:

SELECT ContactID, CoContactLN & "," & CoContactFN, AS CoContact1 FROM tblCoContacts WHERE ContactCo = [cboCoName];

When I run it, it pops up a message saying "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".

What am I doing wrong.

[LaughingBull]

I did this with your DB I created a query on the tblProjectProposalSummary only included the fields you want to show on your form. then I created a new form not as pretty as yours just a simple form using the tblProjectProposalSummary. in design view I added a combo box which retrieved the info from the query and all the data shows up in the fields that have data in them as not all records have a contact.Simple Sample Database1.zip

[pbaldy]

SELECT ContactID, CoContactLN & "," & CoContactFN, AS CoContact1 FROM tblCoContacts WHERE ContactCo = [cboCoName];

When I run it, it pops up a message saying "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".

What am I doing wrong.

For educational purposes, the comma before AS is the source of the error.

[shoelesscraig]

For educational purposes, the comma before AS is the source of the error.

Guys, thanks a lot for the responses...much appreciated. Here's where I'm at...

I took out the comma as described above, it works! Not I get my dropdown like I'm supossed to. I modified the combo box and query just a little to give me a space between the last name and first name, only show the columns I wanted, etc. So I'm good there.

Based on some info I read somewhere (don't remember where), in order to make the Company selection combo box update the 3 contact combo boxes, I added the following code:

Private Sub cboCoName_AfterUpdate()


Me.cboCoContact1.Requery
Me.cboCoContact2.Requery
Me.cboCoContact3.Requery


End Sub


This seems to make the 3 contact selection combo boxes update when I select the company name. I can live with this, but I do have one thing I'd like to correct if possible. If I choose "Company A" up top, then my combos down below present me with A, F, and K contacts to choose from like they are supposed to. Let's say I select "A". Then, let's say I realize I made a mistake, I should've selected Company B up top. So I do that, the 3 combos down below clear out and now present me with B, G, and L...again this is correct. Let's say I choose B. If for whatever reason I decide to change the top back to Company A again, it automatically puts Contact A in the box down bottom because thats what had choose the first time around. I'd like the combos down below to start off blank anytime a new company name is choosen, regardless of what was done with them in the past for that record.

I guess what I'm asking is...how do I make the 3 boxes down below CLEAR any selection they may have ever had made for that record when I choose a new Company name up top? In other words, if I choose a new company, clear the 3 down below completely and start over like I've never made a selection on them for that record.

[shoelesscraig]

I did this with your DB I created a query on the tblProjectProposalSummary only included the fields you want to show on your form. then I created a new form not as pretty as yours just a simple form using the tblProjectProposalSummary. in design view I added a combo box which retrieved the info from the query and all the data shows up in the fields that have data in them as not all records have a contact.Simple Sample Database1.zip

I'm thinking you somehow attached the wrong file. Nothing in that database resembles mine at all.

[shoelesscraig]

Ok, maybe I spoke too soon...I realized I have another problem.

When I select a company name and then a contact, for example Company A, contact A. Everything looks good. I can go on to the next record and do another etc. When I come back to the previous record, it works like it should.

However, when I close the form and reopen it, for every record, the 3 combo boxes are blank. But, in the actual table, the fields for those 3 combo boxes do show the ID number for the contact they reference correctly. It's like when I first selected the company and contacts, the info got inserted into the table correctly. However, it just doesn't display it again on the form if I close/reopen.

[shoelesscraig]

Lol, I just keep finding stuff....

So I'll further clarify by saying that I don't have to close/reopen the form. If I go to a record, select Company A and then contact A down below. I does what it should. I go to next record and Select Company B and contact B, still looks like it does what it should. Then if I go back a record, it should have "Contact A" in the combo down below...it doesn't. It shows blank. If I go back up top and select Company A in the dropdown AGAIN, even though it already showed Company A, then all of the sudden "Contact A" will just appear in the combo box like it should have.

Like I said, the correct "Contact IDs" are in the table for every record though. So the info is there...it's just not displaying like it should as I cycle through records.

[pbaldy]

The link you said didn't help showed how to clear the combos.

As to them not showing values, perhaps a new sample that demonstrates that would help. I can't think of why they would do that offhand.

[shoelesscraig]

The link you said didn't help showed how to clear the combos.

Lol, fair enough...can't argue with that!

As to them not showing values, perhaps a new sample that demonstrates that would help. I can't think of why they would do that offhand.

Done...I've attached a new example. You'll see what I mean. Go to a record, make a selection in the Contact Combo. Make go forward a record, make another selection...then go back to the previous record. You'll see what I mean. It shows blank, but the ID for that contact DID make it into the table. Reselecting the same company name from the list up top will cause the contact to appear automatically. Weird...Project & Proposal Summary 2nd Post.zip