Access form delete query not working but no error message.

[scrapcode]

Hi everyone.

I'm having some bother with a delete query on a form I've built.

On the form is a text box which contains a unique ID. I would like to be able to delete the whole record that corresponds to that unique ID from the table that it is linked to.

So far my code looks like this:

StrQuery = "Delete FROM TblSubmissions " & "WHERE tblSubmissions.[Unique ID] = " _
& Forms("Submit").TxtID & ";"

I've also put in a plain message box after the query to show me it's triggering. The problem is that the sub appears to be working, however the table isn't altered. There aren't any error messages being advised and the message box triggers away fine but the record isn't being deleted.

The Unique ID is lifted directly from the table when the form loads so it's not an error inputting it.

It has me stumped. Any advice you can provide would be much appriciated.

[Rabbit]

If that's all the code you have so far, then it doesn't match your description of the problem.

1) We have no idea how it's triggered.

2) There's no message box in your code that you posted.

3) All you have there is a string, I don't see where you run the query.

[scrapcode]

Thanks for getting back to me. I'll try to clear things up.

The delete query will need to be run in a few different situations so at the moment I just have it triggered from a command button.

As it doesn't form part of a real sub yet I just truncated the code. Here's the full sub as it stands:

Private Sub Command70_Click()
 
Dim StrQuery As String
 
StrQuery = "Delete FROM TblSubmissions " & "WHERE tblSubmissions.[Unique ID] = " _
& Forms("Submit").TxtID & ";"
 
MsgBox StrQuery
 
MsgBox "Test", vbOKOnly + vbExclamation, "Incorrect Reference"
 
End Sub

If it helps the output from the MsgBox is:

Delete FROM TblSubmissions WHERE tblSubmissions.[Unique ID] = RCI1;

RCI1 is the unique referrence that I want to use to delete the record.

Is there anything else I can tell you?

Cheers.

[scrapcode]

Thanks Rabbit but I've figured it out. I realised I was making things far more difficult than was needed.

Instead I've just built the query normally then called it from within the code.

Couldn't see the wood for the trees on that one!

[Rabbit]

Glad you got it working.

But for future reference, the reason it didn't work but you got no error message is that you never ran your query code.

DoCmd.RunSQL StrQuery

Had you run it, you would have gotten an error. Since RCI1 is a string, you needed to surround it in quotes.

StrQuery = "Delete FROM TblSubmissions " & "WHERE tblSubmissions.[Unique ID] = '" _ 
& Forms("Submit").TxtID & "';"