Update table through form

[Margie]

Hello again

After getting great help perfecting my little Access 2007 movies database, I've stumbled upon an inconvenience. In my opinion it seems basic, but still I'm not able to solve it. As an example, in my form I have two fields (Production & Distribution). These are linked to 1 table (Companies). Now each time I want to enter a new company, I have to do so manually in the table. What I would like is to be able to enter the new name in the form and that the table gets updated instantly. I think the correct term is populated.

Data Properties of the 2 Combo Boxes Production & Distribution:

Control Source: Production & Distribution
Row Source: Companies
Row Source Type: Table/Query
Bound Column: 1
Limit To List: Yes
Allow Value List Edits: Yes
List Items Edit Form:
Inherit Value List: Yes
Show Only Row Source: No
Input Mask:
Default Value:
Validation Rule:
Validation Text:
Enabled: Yes
Locked: No
Auto Expand: Yes
Smart Tags:

When I go to the main table (Movies) where all the boxes are listed and I look at the Field Properties of the boxes Production and Distribution it says at the row source:

SELECT [Companies].[Companies] FROM Companies; 

Looking for solutions on the internet, I couldn't figure out how to get them to work. For instance the 'INSERT INTO' command, looking at the relationships (one-to-many / many-to-many), bound or unbound (all appear to be bound), ... I tried everything using a newly copied database each time but nothing seems to work.

So yet again I'm here to ask for your help. I hope the information I gave is enough and clear.

Greetings Margie

[puppydogbuddy]

Hello again

After getting great help perfecting my little Access 2007 movies database, I've stumbled upon an inconvenience. In my opinion it seems basic, but still I'm not able to solve it. As an example, in my form I have two fields (Production & Distribution). These are linked to 1 table (Companies). Now each time I want to enter a new company, I have to do so manually in the table. What I would like is to be able to enter the new name in the form and that the table gets updated instantly. I think the correct term is populated.

Data Properties of the 2 Combo Boxes Production & Distribution:

Control Source: Production & Distribution
Row Source: Companies
Row Source Type: Table/Query
Bound Column: 1
Limit To List: Yes
Allow Value List Edits: Yes
List Items Edit Form:
Inherit Value List: Yes
Show Only Row Source: No
Input Mask:
Default Value:
Validation Rule:
Validation Text:
Enabled: Yes
Locked: No
Auto Expand: Yes
Smart Tags:

When I go to the main table (Movies) where all the boxes are listed and I look at the Field Properties of the boxes Production and Distribution it says at the row source:

SELECT [Companies].[Companies] FROM Companies; 

Looking for solutions on the internet, I couldn't figure out how to get them to work. For instance the 'INSERT INTO' command, looking at the relationships (one-to-many / many-to-many), bound or unbound (all appear to be bound), ... I tried everything using a newly copied database each time but nothing seems to work.

So yet again I'm here to ask for your help. I hope the information I gave is enough and clear.

Greetings Margie

Margie,
You just need to code a NotInList event procedure for your combobox. That way if the company name is not on the combobox selection list, your code will add it for you. See his link for an excellent step by step tutorial and code samples for the NotInList event.

http://www.fontstuff.com/access/acctut20.htm

[Margie]

Margie,
You just need to code a NotInList event procedure for your combobox. That way if the company name is not on the combobox selection list, your code will add it for you. See his link for an excellent step by step tutorial and code samples for the NotInList event.

http://www.fontstuff.com/access/acctut20.htm

Thank you puppydogbuddy. That tutorial was perfect and reasonable easy to understand. And the code I used works perfectly on all the combo boxes. This saves me really a lot of time. Thank you for your much needed help!