Control Source property not allowing a dropdown selection

[ottomatic11]

Hello all,

I am pretty new to creating access forms and especially to data entry forms among multiple tables. Here is my situation:

• I have two tables, General and Company
• General has fields: ID, Program, CompanyID where ID is the primary key for the table.
• Company has fields: CompanyID, CompanyName where CompanyID is the primary key to the table.

As you can probably see, the General table only shows a companyID and the company table is the name of the company, so i have linked these tables on companyID. I now want a data entry form that will allow me to add a new program in the general table.

My record source for the form (this is where i think my error is) is

SELECT [General].[Program], [Company].[CompanyName] FROM [Company] INNER JOIN [General] ON [Company].[CompanyID] = [General].[CompanyID];

I have set the form data entry to yes and have two comboboxes on the form

• Program: RowSource - SELECT [General].[Program] FROM [General] GROUP BY [General].[Program] ORDER BY [General].[Program];
• Company: RowSource - SELECT [Company].[CompanyName] FROM [Company] GROUP BY [Company].[CompanyName] ORDER BY [Company].[CompanyName];

Other than the rowsource the comboboxes are exactly the same. When i show in form view i can see the programs in the dropdown for program, but i cannot choose one, it is like the combobox is locked, when it isn't. I can however, select a company name from the company dropdown box. When i remove the record source on the form they both work.

I am looking for a way to add a program in the general table, but i don't want to add the actual company name, i only want the CompanyID. I hope all of this makes sense, if not i can certainly give it another try. Thanks in advance for any assistance.

[orange]

It might be easier for readers to understand if you could provide a description in plain English of WHAT your database is suppose to support.
Before jumping into Access and database jargon, what exactly is the "Business issue or opportunity" you are trying to support with this database?

[ottomatic11]

Orange,

In my business we deal with people each selling a single product, programs. Each of those people is associated with a larger organization, company. More than one program can be associated with one company. So the program table will contain another column with product in it. The program table will track the products being sold by each program and the company table will be a list of all possible companies that we deal with. Does that make sense?

[orange]

Better, but still don't see what differentiates a Program and Product. Forget tables for the moment.

In my business we deal with people

are these people part of your Company or a client of your company?

So, what I'm seeing:

Each People(person) is associated with a Company/Organization
1 or Many Programs can be associated with a Company/Organization

products being sold by each program

???

[June7]

A form can do data entry for only one table. What is purpose of form - data entry to which table? BOUND comboboxes for both company and program makes no sense.

An INNER JOIN Requires related records in both tables for record to show.

A company can have more than one program. Can a program be associated with more than one company? If yes then this is a many-to-many relationship and a third table is required. This would be a junction table for associating companies with programs.

[ottomatic11]

orange,

Think of my company at the top level of a tree. There are companies below mine called programs, each of them have employees that sell one item. The companies have multiple employees, each selling one item, so the company sells many items through all of its employees.

Speaking in terms of tables, the general table contains all of the employees (Program) and which company they work for. The Company table only contains all of the companies that are below mine.

June7,

I am only trying to update the Program table. When the user wants to add a program to the table he needs to be able to select which company they work for. Using the ID is not ideal so i wanted the company dropdown to show the names of the companies associated with the programs. Then when the user adds the program, instead of putting in the name of the company it places the ID of the associated company.

I am not sure what you are saying in your inner join statement

A program will never be associated with more than one company.

[June7]

If you are entering a new program into General table then why would you be selecting existing program from a list?

The Company ID is not included in the combobox RowSource therefore it will not save the ID into record. Review: http://www.datapigtechnologies.com/f...combobox3.html

Remove Company table from the form RecordSource. Bind form directly to General table or use a simple SQL statement: SELECT * FROM General;

[orange]

Seems June has a better idea of what you're describing than I do, so I'll opt out.
Good luck.

[ottomatic11]

Orange,

Thank you very much for attempting. I am sorry that I was unable to explain my problem in a more precise manner.

June,

Your solution worked perfectly. I took out the company table from the record source and watched the video, which is exactly what I was doing, and it worked perfectly. Thank you so much for all of your help!