Simple dependency question

[shoelesscraig]

Ok, I'm new to access AND this forum, so please go easy.



I have a table with 1 column (2 if you count the ID)...it has a list of "Branches". These are names of cities around Atlanta, but let's just call them A-Z. So 26 rows with A through Z.

I have a second table (which I've already created) that has 2 columns (3 if you count the ID). 1st column is "Name" and the second is the "Branch". So for my company, we have account managers that work out of 1 of 26 offices in Atlanta. For each account manager, there is ONLY 1 possible branch they can work out of. So let's say John Doe works from branch A, Bill Smith works from B, etc. I've already entered each account manager's name into the first column and the second column has a drop down box linked to the branches (A-Z) from the second column. So when I created the table, I typed each account managers name in and selected (from the dropdown to the right of it) which branch they work from. There is ONLY 1 possible branch for each account manager...this never changes.

In another table (we'll call it the MAIN table), I have all sorts of info, but particularly, I have a dropdown for account managers names. I already have it set up so that when I click in this field, it gives me a dropdown list of all of the account managers names from the other table I described above. I have it sorted alphabetically, works fine. What I want is...when I select John Doe in this field, the field TO THE RIGHT of it automatically populates with "A", or if I choose Bill Smith, the field to right populates with "B" and so on. A direct 1-to-1 relationship. Basically, it would just look at the other table and pull the branch for that account manager and fill in the field in this table.

Sounds simple to me, and it probably is, but I don't know how to make it happen. I've seen lots of info about making the dropdown list for something dependent on another field's selections, etc. But mine is not that complicated. Whatever I choose in this column needs to populate the field next to it. There is only 1 possible branch for each account manager. I don't want to just select the branch for that account manager in my "Main" table, because I could mess up and choose the wrong one, for example, accidentally saying that John Doe works from branch C. I would like it to be dependent so that I can't ever mess it up.

[ranman256]

In your form you have the combo, the query of which, has 2 fields in the combo. Name and Branch. (you dont have to show the branch, it can have col.width of Zero.)
after update of the combo,update the branch box

sub cboName_afterupdate()
   txtBoxBranch = cboName.column(1)   'fill in the branch
end sub

[shoelesscraig]

I think I see what you're saying...so in my "Main" table I won't see this branch location, but in my form (which I will be creating soon) I will? If so, that's all I really need anyways.

[ssanfu]

It sounds like you have "Look up Fields" in your table. This is the wrong way to design tables (IMO). Unless the dB is for your use only, users should never see a table - they should use forms.

See: The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm



One other thing. "Name" is a reserved word in Access and shouldn't be used for object names. There should be 2 fields for names: "FName" and "LName". It is easier to combine names that to split them.

[shoelesscraig]

Thanks for the replies. Sorry it took me a while to get back here and respond.

Ok, yes, I realize this is not the textbook way to do this...I AM, however, the only one who will ever use this. So that should make things easier. Also, I didn't use the word "Name" in my actual Access project, I just called it that here so as to keep it simple...didn't realize that "Name" is used by Access, good to know. I called it "name" just so I wouldn't have to make things confusing in this thread. Sorry...

Ok, so I don't really understand the code you posted. (And maybe that's because I'm VERY new to this). Let me explain it differently, and maybe easier: Basically, I have a table with names in one column and the "branch" that that person works out of in the column next to it. The "branch" field for each row is a dropdown linked to another table with the names of the branches in it...but I'm not sure if that is relevant information or not.

So, in my form, I want to select a name from a dropdown, and then in a separate box on the form, the name of the branch will appear...the name of the branch that corresponds to that name in the other table.

The table with the names/branches is called "tblAM". Hopefully this makes sense.

[June7]

Make the 'Name' combobox a multicolumn RowSource, like:

SELECT [name field], [Branch] FROM tblAM ORDER BY [name field];

Review http://www.datapigtechnologies.com/f...combobox3.html

Then in a textbox have an expression that refers to the Branch column of combobox by its index. Index begins with 0. So:

=[comboboxname].[Column](1)

No VBA required.

Why save the branch into this table? That is duplication of data. The branch associated with Name can always be retrieved by joining tables in query.

[shoelesscraig]

Why save the branch into this table? That is duplication of data. The branch associated with Name can always be retrieved by joining tables in query.

You're right, I think I am creating extra work...so I've rethought this out, maybe this is a better way...

So my company does business with various other companies, and our company assigns 1 and only 1 account manager to each company. Each of those account managers works out of 1 of 4 possible branches in the area.

So I've created the following:

2. tblAM - This has the ID column, next column is called "AMName" and I've listed all account managers names, next column is called "AMBranch" and I have listed which branch each account manager works from (if it matters, I didn't type each one in, I used a local lookup dropdown. I just typed in the 4 possible branch names so I could select them from dropdown each time we added an account manager, so I could make sure spelling errors and such were eliminated).
3. tblCompanyInfo - In this table, I am trying to correlate each company and who is the account manager responsible for it. So I have the ID column, the next column is called "CoName" and has all of the company names in it, next column is called "CoAMName" and has a lookup dropdown linked to the "AMName" column from the tblAM. So I can assign each company an account manager.

All of that said, now I have a table that has company names and which account manager is responsible for it, and the tblAM table correlates which branch that account manager works from.

This may not even be right or the easiest way...if it's not please set me straight.

So the end goal is this...I have another table that has a million fields in it, and this is our project summary table. I have a form that I created where I type in the name of a project, project #, proposal #, etc. On this form, I would like to be able to select from a combobox the name of a company (linked to my tblCompanyInfo). So that combobox would display the name of company I selected. Then in 2 other text boxes, I would like the name of the account manager associated with that company and the branch that account manager works from to be displayed. When I am done entering all of the info in this form, I hit the "Save Record" button and it enters all of that info into my project summary table.

I've already created this "project summary" table and all of the other fields and buttons in it...and that part works fine. I do know just enough to be dangerous However, I'm not sure how to make these 2 text boxes say "ok, you selected Company A, and in the column next to Company A you put John Doe's name, and in the other table next to John Doe's name you put Branch C). THAT is the correlation I cannot make.

Also, am I even on the right track? Is it ok that my end goal is to make selections in the form and when I click "Save" it creates a new record in this "project summary" table? This means that table contains a LOT of fields. Obviously I'm stupid when it comes to Access, and I don't really know anyone that can teach me...hence me coming here. Thanks guys for any help you're willing to offer.

[June7]

The combobox RowSource can be a query that joins tblCompanyInfo and tblAM.

In post 6 I described method to display the info from the combobox columns in textboxes.

Name parts should be in separate fields - FirstName, Middle, LastName. Then instead of saving name in tblCompanyInfo, save the ManagerID. The autonumber ID field should be primary key in tblAM. Then a number field in tblCompanyInfo should receive the ID as foreign key.

[shoelesscraig]

Can you elaborate on "foreign key"? I did what you said and separated the first name and last names in tblAM. Then in my tblCompanyInfo I put the company name in a column and then in the column next to it, the ID number corresponding to the account manager from tblAM...but I'm confused about foreign key.

[June7]

The field in tblCompanyInfo that holds the tblAM ID value is a foreign key. Tables are normally joined on primary and foreign key fields when building queries. The primary and foreign keys are what allow synchronization of related records in form/subform arrangements for data entry/edit. Understanding PK/FK is critical to understanding relational database concepts. Proceed no further until you do.

[shoelesscraig]

I have figured out most of what I need...here's another question...

In my form (I only have 1) I select the name of the company for a particular project from dropdown...the list of possible company names comes from my tblCompanyInfo. No problem there...However, b/c of the relationship I set up, what appears after I make a selection is the ID for that company. For actual insertion into my main "project" table, the number is fine. But when I'm viewing the Form, the number means nothing to me. I need to actually see the name of the company in text form. How can I make it display the text instead of ID number?

[shoelesscraig]

Actually, what I said was wrong...let me correct myself...

I have figured out how to have my CoName dropdown (on the form) have multiple columns. First column is the name of the company, second is the name of the account manager for that company. When I select a company from the dropdown, the company's name DOES display in the combo box. This is fine.

In a separate text box, I'm trying to display the contents of column 1 from the above dropdown. I have got this to work fine, but it shows me the ID related to the account manager for that company (the ID from the tblAM). While it is diplaying the ID number correctly, I need to see the actual text, not the ID number. In this form, seeing a number is worthless. I need to see actual text.

[June7]

The company combobox should have ID field as first column, set it with 0 width to hide. Then users will see the company name but the ID will save to field.

Reference columns by index. Index begins with 0. So the first column is index 0, the second column is index 1 and so on.

Have you set Lookups in tables? I NEVER do that, especially lookups that have alias. I suspect you have done this in tblCompanyInfo and that is why the combobox shows manager ID instead of name because the actual value in the field is ID. If you want the manager name, the combobox RowSource will have to be a query that joins tables.

[shoelesscraig]

Have you set Lookups in tables? I NEVER do that, especially lookups that have alias. I suspect you have done this in tblCompanyInfo and that is why the combobox shows manager ID instead of name because the actual value in the field is ID. If you want the manager name, the combobox RowSource will have to be a query that joins tables.

Yes I have...and I am now understanding why I should not have. That being said....what should I have done?

So, I have started over so to speak. Here's what I've got:

1. tblBranch: ID field, and then field named "Branch" listing our 4 branches

2. tblAM: ID field, field named "AMLastName", field named "AMFirstName", field named "AMBranch" with the ID number of the branch from the previous table for that account manager

3. tblCompanyInfo: ID field, field named "CoName" with all the companies we deal with, field named "CoAM" with the ID number for the account manager for that company from the previous table.

4. tblProjectSummary: ID field, and then various other fields that will be filled in using a form, then you click "Save Record" and it adds a new record to this table.

No lookup tables are in any of my tables! I would like, in the form, to be able to select a company name for the new record, and in a separate box on the form, the name of the account manager appear, and in another text box, the name of the branch that this account manager works out of. When I select "Save Record", the form would insert this information into my tblProjectSummary. If it enters it into the table as a number, of course that is fine...but on the form I would like to actually see the text "John Doe" and "Branch A" for example.

Thank you for your patience with me...I'm learning...slowly, but definitely learning.

[June7]

You removed lookup from the CoAM field in tblCompanyInfo? Set lookups with alias in table if you want - just be aware that the value you see is not the value in field.

As stated, the company combobox RowSource must be a query that joins tables (tblCompanyInfo and tblAm) so that related data is available.