By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Deleting Details From A Form Based On A Query

P: 3
Hi,

I have a problem whereby I need to delete a record from a form that is based on a Query that has two tables (1 to Many) relationship.

I have the following sample of what I have tried:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command63_Click()
  2.  
  3. Dim CurrHospID As String
  4. Dim strSQL As String
  5. Dim intAnswer As Integer
  6.  
  7. CurrHospID = Me.HospID
  8.  
  9. strSQL = "DELETE * FROM Patients WHERE Patients.HospID = " & CurrHospID
  10.  
  11. 'strSQL = "DELETE * FROM Patients WHERE QRY_Record.HospID IN (SELECT HospID FROM Patients) = " & CurrHospID
  12.  
  13. intAnswer = MsgBox("Do you want to delete " & Me.HospID & ", " & Me.Forename & " " & Me.Surname & "?", _
  14.    vbYesNo Or vbDefaultButton2, "Confirm Delete Patient")
  15.  
  16. If intAnswer = vbYes Then
  17.  
  18. DoCmd.RunSQL strSQL
  19.  
  20. End If
  21.  
  22. DoCmd.Requery
  23.  
  24. Exit_Command63_Click:
  25. Exit Sub
  26.  
  27. End Sub
So the structure I have is a Form based on QRY_Record
QRY_Record is built from two tables "Patients" & "TBL_Categories"
The Patients being 1 and TBL_Categories being Many.

I have a button on each record of a continuous form to delete the record, but this isn't working. The message box pops up, and I click yes, and then I get the folllowing error:

Data type mismatch in criteria expression.

I am little lost. The primary field is HospID which is Text, so I set this as String in the code.

Where am I going wrong? Any help would be very much appreciated.

Richard.
Oct 22 '08 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 634
Hi

Try this

strSQL = "DELETE * FROM Patients WHERE Patients.HospID = '" & CurrHospID & "'"

For string/text fields the parameter need to be enclosed in single quotation marks (so the compiler can differentiate the query parameter from the rest of the query string).

HTH


MTB
Oct 22 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
As MTB says, you need the single quotes in the string that is passed through to the SQL Engine.

The SQL Engine gets a string passed. That string is what has been prepared in your earlier code, but when it is passed there is nothing but the eventual string. The way the SQL Engine knows whether to treat the data as string or otherwise is by the quotes, in the string, that surround the data.

You may find Quotes (') and Double-Quotes (") - Where and When to use them helpful.

Welcome to Bytes!
Oct 22 '08 #3

P: 3
Thank you very much for your help, I'll give it a go.

Bytes is great, hopefully I can help a few people along the way, too.

Thanks again.
Oct 22 '08 #4

P: 3
Thank you very much for your help, I'll give it a go.

Bytes is great, hopefully I can help a few people along the way, too.

Thanks again.


Yep that one worked, thanks very much, and for the link to ' and "

Rich.
Oct 22 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
You're welcome Rich :)
Oct 22 '08 #6

Post your reply

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