Delete rows in table from Module or Form

[michelle]

Hi fols
I've been trying to delete som rows from a table:
in a form - wont work
in a module - wont work either
in a query that's called from a module - and ofcoursre , wont work
---
tried this in the module
Set db = CurrentDb()

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' & "" ;"

CurrentDb.Execute SQLstr

and this:

DoCmd.RunSQL "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' & "" ;"

---
Tried with a query that's called from the module:
code in module:

'stDocname = "delMagQuery"
'DoCmd.OpenQuery stDocname, acNormal, acEdit

code in query:

DELETE * FROM Photos WHERE [boxId]=" & myBox & " And [magasineId]='" &
myMagsineId & "' & "";

And nothing works can PLEASE someone help?
Thanks in advance
/Michelle
(e-mail address removed)

 

[Douglas J. Steele]

What does "won't work" mean?

Do you get an error message? If so, what's the error? If you don't get an
error, are you saying that the SQL runs, but the data's still there? How
have you confirmed that the SQL was run?

 

[michelle]

What does "won't work" mean?

Do you get an error message? If so, what's the error? If you don't get an
error, are you saying that the SQL runs, but the data's still there? How
have you confirmed that the SQL was run?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi fols
I've been trying to delete som rows from a table:
in a form - wont work
in a module - wont work either
in a query that's called from a module - and ofcoursre , wont work

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' & "" ;"

CurrentDb.Execute SQLstr

and this:

DoCmd.RunSQL "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' & "" ;"

'stDocname = "delMagQuery"
'DoCmd.OpenQuery stDocname, acNormal, acEdit

code in query:

DELETE * FROM Photos WHERE [boxId]=" & myBox & " And [magasineId]='" &
myMagsineId & "' & "";

And nothing works can PLEASE someone help?
Thanks in advance
/Michelle
(e-mail address removed)- Dölj citerad text -

- Visa citerad text -

Hi again

Thanksfor answering me.

I better start telling you that all the varaiables in the module gets
the correct values.
When Iexecuten this line
SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagasineId & "' & "" ;"

everything looks ok.
Then I execute:
CurrentDb.Execute SQLstr

After that I have these lines in the module:
Proc_Exit:
Set db = Nothing

Which are skipped, I end up
Err_RunTheDeleteMag:
MsgBox "Error " & Err.Number & vbCrLf & Err.sSQL, vbOKOnl
'Resume Exit_RunTheChangeBox

And I get a message thats look like this:
'object does not support the propery or method'

Perhaps a lousy translation from swedish to english but I hope it's
understandable.

You recognise the error?
Can you (or someone give me a hint what to change)?

best regards
/Michelle

Thanks in advance

 

[Douglas J. Steele]

Actually, the quotes in your code to generate the SQL look incorrect.

In the first case, it should be

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' ;"

(although the semi-colon isn't actually mandatory)

The second case really doesn't make sense to me: you can't put references to
variables in the SQL property of a QueryDef object like that.

If the correction above doesn't help, directly before the CurrentDb.Execute
SQLstr line in your code, insert the following:

Debug.Print SQLstr

Once the code's run, go to the Immediate window (Ctrl-G), and look at the
actual SQL that's being run. Does it look correct? (paste the SQL into your
reply if you're still having problems)

Your query assumes that boxId is a numeric field, and that magasineId is a
text field. Is that correct?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What does "won't work" mean?

Do you get an error message? If so, what's the error? If you don't get an
error, are you saying that the SQL runs, but the data's still there? How
have you confirmed that the SQL was run?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hi fols
I've been trying to delete som rows from a table:
in a form - wont work
in a module - wont work either
in a query that's called from a module - and ofcoursre , wont work

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' & "" ;"

CurrentDb.Execute SQLstr

and this:

DoCmd.RunSQL "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' & "" ;"

'stDocname = "delMagQuery"
'DoCmd.OpenQuery stDocname, acNormal, acEdit

code in query:

DELETE * FROM Photos WHERE [boxId]=" & myBox & " And [magasineId]='" &
myMagsineId & "' & "";

And nothing works can PLEASE someone help?
Thanks in advance
/Michelle
(e-mail address removed)- Dölj citerad text -

- Visa citerad text -

Hi again

Thanksfor answering me.

I better start telling you that all the varaiables in the module gets
the correct values.
When Iexecuten this line
SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagasineId & "' & "" ;"

everything looks ok.
Then I execute:
CurrentDb.Execute SQLstr

After that I have these lines in the module:
Proc_Exit:
Set db = Nothing

Which are skipped, I end up
Err_RunTheDeleteMag:
MsgBox "Error " & Err.Number & vbCrLf & Err.sSQL, vbOKOnl
'Resume Exit_RunTheChangeBox

And I get a message thats look like this:
'object does not support the propery or method'

Perhaps a lousy translation from swedish to english but I hope it's
understandable.

You recognise the error?
Can you (or someone give me a hint what to change)?

best regards
/Michelle

Thanks in advance

 

[michelle]

Actually, the quotes in your code to generate the SQL look incorrect.

In the first case, it should be

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' ;"

(although the semi-colon isn't actually mandatory)

The second case really doesn't make sense to me: you can't put references to
variables in the SQL property of a QueryDef object like that.

If the correction above doesn't help, directly before the CurrentDb.Execute
SQLstr line in your code, insert the following:

Debug.Print SQLstr

Once the code's run, go to the Immediate window (Ctrl-G), and look at the
actual SQL that's being run. Does it look correct? (paste the SQL into your
reply if you're still having problems)

Your query assumes that boxId is a numeric field, and that magasineId is a
text field. Is that correct?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


On 1 Mar, 19:46, "Douglas J. Steele"

Well,well

your row looks pretty much the same as min.
And it doesn't work.

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagasineId & "' & ;"
CurrentDb.Execute SQLstr

Any ideas??

best regards
/Michelle

 

[Douglas J. Steele]

Actually, the quotes in your code to generate the SQL look incorrect.

In the first case, it should be

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagsineId & "' ;"

(although the semi-colon isn't actually mandatory)

The second case really doesn't make sense to me: you can't put references
to
variables in the SQL property of a QueryDef object like that.

If the correction above doesn't help, directly before the
CurrentDb.Execute
SQLstr line in your code, insert the following:

Debug.Print SQLstr

Once the code's run, go to the Immediate window (Ctrl-G), and look at the
actual SQL that's being run. Does it look correct? (paste the SQL into
your
reply if you're still having problems)

Your query assumes that boxId is a numeric field, and that magasineId is
a
text field. Is that correct?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


On 1 Mar, 19:46, "Douglas J. Steele"

Well,well

your row looks pretty much the same as min.
And it doesn't work.

SQLstr = "DELETE * FROM Photos WHERE [boxId] = " & myBox & " And
[magasineId] = '" & myMagasineId & "' & ;"
CurrentDb.Execute SQLstr

Yours is different than mine. You've got the extra space, ampersand and
space in the end. That means that if myBox has a value of 1 and myMagasineId
has a value of A, your string will be:

DELETE * FROM Photos WHERE [boxId] = 1 And [magasineId] = 'A' & ;

whereas mine will be

DELETE * FROM Photos WHERE [boxId] = 1 And [magasineId] = 'A';

It's the ampersand in there that's killing you.