Sql Delete syntax

[brianv]

i have a list box, List11, which has 3 fields

Name, FoodCategoryId, RecipeId

the fields FoodCategoryId and RecipeId are hidden. when i double click the
Name i want to delete the record associated with it. so i have the dbl click
event run a Macro which has this sql


Delete * From RecipeFoodCategories where
FoodCategoryId=List11.FoodCategoryId and RecipeId=List11.RecipeId;

It prompts me for the List11.FoodCategoryId and List11.List11.RecipeId
fields. what is the correct syntax to pick these fields up from the list box?

thanks
brian

 

[Ofer]

Change the double click event from macro to code, and run this SQL

Docmd.RunSQL "Delete * From RecipeFoodCategories where FoodCategoryId= " &
Me.List11.column(1) & " and RecipeId= " & Me.List11.column(2)


If the values are string, then you need to add a single quote before and after

Docmd.RunSQL "Delete * From RecipeFoodCategories where FoodCategoryId= '" &
Me.List11.column(1) & "' and RecipeId= '" & Me.List11.column(2) & "'"

===============================
The column number start with 0

 

[brianv]

thanks, this helped a lot. i thought you had put in the wrong column numbers
but you did not. i guess it starts counting from zero. now i have another
question. i am getting a syntax error on this sql:

Private Sub List16_Click()

Rem This will insert the relationship rcd between Recipe and
FoodCategories.
DoCmd.RunSQL "Insert into RecipeFoodCategories (FoodCategoryId,
RecipeId) Values(" & Me.List16.Column(1) & "," & Me.Combo13.Column(0) & ")"


Rem This will refresh the form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub


can you tell me what is wrong? the variables are numbers not text data type.

thanks
brian

 

[Ofer]

It look fine, but just make sure that it is in one line and not 2

DoCmd.RunSQL "Insert into RecipeFoodCategories (FoodCategoryId, RecipeId)
Values(" & Me.List16.Column(1) & "," & Me.Combo13.Column(0) & ")"

To make it two lines, it should be
DoCmd.RunSQL "Insert into RecipeFoodCategories (FoodCategoryId, RecipeId)" &
_
" Values (" & Me.List16.Column(1) & "," & Me.Combo13.Column(0) & ")"