468,133 Members | 1,203 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,133 developers. It's quick & easy.

SQL delete statement -

I'am new here so I'd like to say hello and this seems to be a great site it has already helped me a few times. I just got an assignment to correct and modify a simple access application. First some background info:. I added a delete button on a subform to remove an item from that forms list. The subfrom is populated from a table that has a primary key composed of two number fields. I have assigned varaibles to the column values for the selected row. Then with a a sql delete statement i would like to delete that row from the table. This is the delete statement:
strSQL = " DELETE * FROM [Assembly Breakdown] " & _
" WHERE ([ProductId] = '" & Itemident1 & "')" & _
" AND ([SubProductId] = '" & Itemident2 & "')" & _
" AND [SubProductName] = '" & ProductName & "' " & _
" AND [SubPartNumber] = '" & ProductNumber & "' " & _
" AND [SubLECNumber] = '" & LECNumber & "' ;"
DoCmd.RunSQL strSQL
Itemident1 and Itemident2 are the two number fields that represent the key on the table. I get a type mismatch error when the code is executed. This makes sense since the two varaibles are enclosed in quotes. No matter what I do as far as screwing with the syntax around these two variables I can't get the delete statement to recognize Itemident1 and Itemident2 as enumerated data types(long).
I even tried to use their test box names from the subform (i.e "ComponentsSubform.ProductId" and "ComponentsSubform.SubProductId') to no avail. Of course I haven't found any reference that states that this is even doable. Any help or insight would be greatly appreciated. Once again this is a great forum.
Jul 10 '07 #1
3 1906
Rabbit
12,511 Expert Mod 8TB
You have to get rid of the single quotes.
Expand|Select|Wrap|Line Numbers
  1. "NumberID = '" & lngID & "'"
  2. "NumberID = " & lngID
  3.  
1. This will see lngID as text no matter what lngID is and it will assume NumberID is text.
2. The opposite is true for this one.
Jul 10 '07 #2
Thanks Rabbit, I could have sworn I tried that at least once with all the attempts at changing the statement around. I guess I couldn't see the forest because of the trees! Thanks again.
Jul 10 '07 #3
Rabbit
12,511 Expert Mod 8TB
Thanks Rabbit, I could have sworn I tried that at least once with all the attempts at changing the statement around. I guess I couldn't see the forest because of the trees! Thanks again.
Not a problem, good luck.
Jul 10 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by php newbie | last post: by
4 posts views Thread by Stefan Strasser | last post: by
16 posts views Thread by robert | last post: by
4 posts views Thread by AlanAylett | last post: by
1 post views Thread by Matt | last post: by
12 posts views Thread by yufufi | last post: by
8 posts views Thread by Michel Esber | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.