Simple Insert statement

[brianv]

i am new at access and need help with what i think should be a simple insert
statement. i have a master form with a subform in it. the master form has a
number field called RecipeId and RecipeName. the RecipeId field is not
displayed but it was retrieved by the sql. the subform has a field called
Ingredient and a non-displayed field called IngredientId. it actual displays
all the Ingredient values in the tbl. when i double click the Ingredient
field i want to insert a row into a relationship table with the values of
RecipeId and the IngredientId whose Ingredient was double clicked.

in the subform when i double click an Ingredient i start event "Double
Click" which runs this sql:

INSERT INTO RecipeIngredients(IngredientId, RecipeId) values( select
IngredientId from Ingredients where
Ingredient=[Forms]![subForm]![Ingredient], [Forms]![Master]![RecipeId])

I get a syntax error. any ideas what is wrong? Note if i hard code the
values section it will insert into RecipeIngredients the values which were
hard coded. therefore my problem is somewhere in the Values section of the
sql.

 

[Rick Brandt]

i am new at access and need help with what i think should be a simple
insert statement. i have a master form with a subform in it. the
master form has a number field called RecipeId and RecipeName. the
RecipeId field is not displayed but it was retrieved by the sql. the
subform has a field called Ingredient and a non-displayed field
called IngredientId. it actual displays all the Ingredient values in
the tbl. when i double click the Ingredient field i want to insert a
row into a relationship table with the values of RecipeId and the
IngredientId whose Ingredient was double clicked.

in the subform when i double click an Ingredient i start event "Double
Click" which runs this sql:

INSERT INTO RecipeIngredients(IngredientId, RecipeId) values( select
IngredientId from Ingredients where
Ingredient=[Forms]![subForm]![Ingredient],
[Forms]![Master]![RecipeId])

I get a syntax error. any ideas what is wrong? Note if i hard code
the values section it will insert into RecipeIngredients the values
which were hard coded. therefore my problem is somewhere in the
Values section of the sql.

Your subform should be bound to the table you want to write to (what you are
describing as the relationship table). By using a query or other lookup
mechanism the subform can still display the ingredient name from the table you
are using now. Then propagation of the RecipeID would be handled automatically
by proper entries in the subform's MasterLink and ChildLink properties. No code
and no insert query required at all.

 

[brianv]

Rick I appreciate your help. Like I said I am new at this. I am not sure I
followed what you said and I may not have explained exactly what I had. I
will try to explain again. Since I am new I may be doing everything the hard
way. I am trying to teach this to myself so I can help my daughter with her
high school class. She is trying to learn access.


This part of the application involves 3 tbls

Recipe tbl
RecipeId autonumber
RecipeName text
….

One-to-many

RecipeIngredients tbl

RecipeIngredientId autonumber
RecipeId Number
IngredientId Number


One-to-many

Ingredients tbl
IngredientId autonumber
Ingredient Text
…..


Table Values:

Recipe
RecipeId RecipeName
1 Grilled cheese
2 Chicken soup
…

One-to-many (Recipe 2 RecipeIngredients)

RecipeIngredients
RecipeIngredientId RecipeId IngredientId
1 1 1
2 1 2
3 2 2
4 2 3


One-to-many (Ingredients to RecipeIngredients)

Ingredients
IngredientId Ingredient
1 Salt
2 Pepper
3 Butter
4 milk


My form “Ingredients 2 Recipe Master†has 2 subforms “Ingredients 2 Recipe
Ingredients†and “Ingredients 2 Recipe Relationshipâ€.

Ingredients 2 Recipe Master will display the name of my recipe from the
Recipe Table.
Ingredients 2 Recipe Ingredients will display a list of all my available
ingredients.
Ingredients 2 Recipe Relationship will display what ingredients are in the
current recipe.

So if my recipe was “chicken soup†it would show:

Chicken Soup Ingredients
Pepper salt
Butter pepper
Butter
milk


what I want to do is double click on milk and have it add milk as an
ingredient to recipe chicken soup. Therefore in need to insert row

RecipeIngredients
RecipeIngredientId RecipeId IngredientId
5 2 4


The field RecipeIngredientId is auto so I do nothing with it. The sql I
wrote to execute on event “double click†for subform “Ingredients 2 Recipe
Ingredients†field Ingredient.

INSERT INTO RecipeIngredients(IngredientId, RecipeId)
values( select IngredientId from Ingredients where
Ingredient=[Forms]![Ingredients 2 Recipe Ingredients]![Ingredient],
[Forms]![Ingredients 2 Recipe Master]![RecipeId])


this gets a syntax error when I try to execute it. If you still think I do
not need the sql then great, but I did not understand what you were saying.
Any help would be appreciated.

--
thanks
brian

i am new at access and need help with what i think should be a simple
insert statement. i have a master form with a subform in it. the
master form has a number field called RecipeId and RecipeName. the
RecipeId field is not displayed but it was retrieved by the sql. the
subform has a field called Ingredient and a non-displayed field
called IngredientId. it actual displays all the Ingredient values in
the tbl. when i double click the Ingredient field i want to insert a
row into a relationship table with the values of RecipeId and the
IngredientId whose Ingredient was double clicked.

in the subform when i double click an Ingredient i start event "Double
Click" which runs this sql:

INSERT INTO RecipeIngredients(IngredientId, RecipeId) values( select
IngredientId from Ingredients where
Ingredient=[Forms]![subForm]![Ingredient],
[Forms]![Master]![RecipeId])

I get a syntax error. any ideas what is wrong? Note if i hard code
the values section it will insert into RecipeIngredients the values
which were hard coded. therefore my problem is somewhere in the
Values section of the sql.

Your subform should be bound to the table you want to write to (what you are
describing as the relationship table). By using a query or other lookup
mechanism the subform can still display the ingredient name from the table you
are using now. Then propagation of the RecipeID would be handled automatically
by proper entries in the subform's MasterLink and ChildLink properties. No code
and no insert query required at all.

 

[Douglas J. Steele]

Try:

INSERT INTO RecipeIngredients(IngredientId, RecipeId)
Select IngredientId, [Forms]![Ingredients 2 Recipe Master]![RecipeId]
From Ingredients Where
Ingredient=[Forms]![Ingredients 2 Recipe Ingredients]![Ingredient]



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Rick I appreciate your help. Like I said I am new at this. I am not sure
I
followed what you said and I may not have explained exactly what I had. I
will try to explain again. Since I am new I may be doing everything the
hard
way. I am trying to teach this to myself so I can help my daughter with
her
high school class. She is trying to learn access.


This part of the application involves 3 tbls

Recipe tbl
RecipeId autonumber
RecipeName text
..

One-to-many

RecipeIngredients tbl

RecipeIngredientId autonumber
RecipeId Number
IngredientId Number


One-to-many

Ingredients tbl
IngredientId autonumber
Ingredient Text
...


Table Values:

Recipe
RecipeId RecipeName
1 Grilled cheese
2 Chicken soup
.

One-to-many (Recipe 2 RecipeIngredients)

RecipeIngredients
RecipeIngredientId RecipeId IngredientId
1 1 1
2 1 2
3 2 2
4 2 3


One-to-many (Ingredients to RecipeIngredients)

Ingredients
IngredientId Ingredient
1 Salt
2 Pepper
3 Butter
4 milk


My form "Ingredients 2 Recipe Master" has 2 subforms "Ingredients 2
Recipe
Ingredients" and "Ingredients 2 Recipe Relationship".

Ingredients 2 Recipe Master will display the name of my recipe from the
Recipe Table.
Ingredients 2 Recipe Ingredients will display a list of all my available
ingredients.
Ingredients 2 Recipe Relationship will display what ingredients are in the
current recipe.

So if my recipe was "chicken soup" it would show:

Chicken Soup Ingredients
Pepper salt
Butter pepper
Butter
milk


what I want to do is double click on milk and have it add milk as an
ingredient to recipe chicken soup. Therefore in need to insert row

RecipeIngredients
RecipeIngredientId RecipeId IngredientId
5 2 4


The field RecipeIngredientId is auto so I do nothing with it. The sql I
wrote to execute on event "double click" for subform "Ingredients 2
Recipe
Ingredients" field Ingredient.

INSERT INTO RecipeIngredients(IngredientId, RecipeId)
values( select IngredientId from Ingredients where
Ingredient=[Forms]![Ingredients 2 Recipe Ingredients]![Ingredient],
[Forms]![Ingredients 2 Recipe Master]![RecipeId])


this gets a syntax error when I try to execute it. If you still think I
do
not need the sql then great, but I did not understand what you were
saying.
Any help would be appreciated.

--
thanks
brian

i am new at access and need help with what i think should be a simple
insert statement. i have a master form with a subform in it. the
master form has a number field called RecipeId and RecipeName. the
RecipeId field is not displayed but it was retrieved by the sql. the
subform has a field called Ingredient and a non-displayed field
called IngredientId. it actual displays all the Ingredient values in
the tbl. when i double click the Ingredient field i want to insert a
row into a relationship table with the values of RecipeId and the
IngredientId whose Ingredient was double clicked.

in the subform when i double click an Ingredient i start event "Double
Click" which runs this sql:

INSERT INTO RecipeIngredients(IngredientId, RecipeId) values( select
IngredientId from Ingredients where
Ingredient=[Forms]![subForm]![Ingredient],
[Forms]![Master]![RecipeId])

I get a syntax error. any ideas what is wrong? Note if i hard code
the values section it will insert into RecipeIngredients the values
which were hard coded. therefore my problem is somewhere in the
Values section of the sql.

Your subform should be bound to the table you want to write to (what you
are
describing as the relationship table). By using a query or other lookup
mechanism the subform can still display the ingredient name from the
table you
are using now. Then propagation of the RecipeID would be handled
automatically
by proper entries in the subform's MasterLink and ChildLink properties.
No code
and no insert query required at all.

 

[brianv]

douglas

thanks. this is getting me very close. i used the sql you suggested and it
works except that it prompts me for the Ingredient name. is there a way for
me to get the name. i am double clicking the name on the form. can i
capture that name and use it so that i do not have to retype it?
--
thanks
brian

Try:

INSERT INTO RecipeIngredients(IngredientId, RecipeId)
Select IngredientId, [Forms]![Ingredients 2 Recipe Master]![RecipeId]
From Ingredients Where
Ingredient=[Forms]![Ingredients 2 Recipe Ingredients]![Ingredient]



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Rick I appreciate your help. Like I said I am new at this. I am not sure
I
followed what you said and I may not have explained exactly what I had. I
will try to explain again. Since I am new I may be doing everything the
hard
way. I am trying to teach this to myself so I can help my daughter with
her
high school class. She is trying to learn access.


This part of the application involves 3 tbls

Recipe tbl
RecipeId autonumber
RecipeName text
..

One-to-many

RecipeIngredients tbl

RecipeIngredientId autonumber
RecipeId Number
IngredientId Number


One-to-many

Ingredients tbl
IngredientId autonumber
Ingredient Text
...


Table Values:

Recipe
RecipeId RecipeName
1 Grilled cheese
2 Chicken soup
.

One-to-many (Recipe 2 RecipeIngredients)

RecipeIngredients
RecipeIngredientId RecipeId IngredientId
1 1 1
2 1 2
3 2 2
4 2 3


One-to-many (Ingredients to RecipeIngredients)

Ingredients
IngredientId Ingredient
1 Salt
2 Pepper
3 Butter
4 milk


My form "Ingredients 2 Recipe Master" has 2 subforms "Ingredients 2
Recipe
Ingredients" and "Ingredients 2 Recipe Relationship".

Ingredients 2 Recipe Master will display the name of my recipe from the
Recipe Table.
Ingredients 2 Recipe Ingredients will display a list of all my available
ingredients.
Ingredients 2 Recipe Relationship will display what ingredients are in the
current recipe.

So if my recipe was "chicken soup" it would show:

Chicken Soup Ingredients
Pepper salt
Butter pepper
Butter
milk


what I want to do is double click on milk and have it add milk as an
ingredient to recipe chicken soup. Therefore in need to insert row

RecipeIngredients
RecipeIngredientId RecipeId IngredientId
5 2 4


The field RecipeIngredientId is auto so I do nothing with it. The sql I
wrote to execute on event "double click" for subform "Ingredients 2
Recipe
Ingredients" field Ingredient.

INSERT INTO RecipeIngredients(IngredientId, RecipeId)
values( select IngredientId from Ingredients where
Ingredient=[Forms]![Ingredients 2 Recipe Ingredients]![Ingredient],
[Forms]![Ingredients 2 Recipe Master]![RecipeId])


this gets a syntax error when I try to execute it. If you still think I
do
not need the sql then great, but I did not understand what you were
saying.
Any help would be appreciated.

--
thanks
brian

brianv wrote:
i am new at access and need help with what i think should be a simple
insert statement. i have a master form with a subform in it. the
master form has a number field called RecipeId and RecipeName. the
RecipeId field is not displayed but it was retrieved by the sql. the
subform has a field called Ingredient and a non-displayed field
called IngredientId. it actual displays all the Ingredient values in
the tbl. when i double click the Ingredient field i want to insert a
row into a relationship table with the values of RecipeId and the
IngredientId whose Ingredient was double clicked.

in the subform when i double click an Ingredient i start event "Double
Click" which runs this sql:

INSERT INTO RecipeIngredients(IngredientId, RecipeId) values( select
IngredientId from Ingredients where
Ingredient=[Forms]![subForm]![Ingredient],
[Forms]![Master]![RecipeId])

I get a syntax error. any ideas what is wrong? Note if i hard code
the values section it will insert into RecipeIngredients the values
which were hard coded. therefore my problem is somewhere in the
Values section of the sql.

Your subform should be bound to the table you want to write to (what you
are
describing as the relationship table). By using a query or other lookup
mechanism the subform can still display the ingredient name from the
table you
are using now. Then propagation of the RecipeID would be handled
automatically
by proper entries in the subform's MasterLink and ChildLink properties.
No code
and no insert query required at all.