473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Deleting Details From A Form Based On A Query

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
5 1691
MikeTheBike
639 Expert 512MB
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
32,556 Expert Mod 16PB
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
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
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
32,556 Expert Mod 16PB
You're welcome Rich :)
Oct 22 '08 #6

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

Similar topics

3
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the...
5
by: Patrick Vanden Driessche | last post by:
Hi All, I'm currently writing an in-house Form validation framework (WinForms) which is based on 'Component'-inheriting object. So basically, I have a small hierarchy. FormValidator +--...
2
by: azmiza | last post by:
Hi everybody, I need your help. I want to view my sql database and its work very well which is display in my web browser but once I want to press button yes, its not working, I check the...
11
by: Yvonne | last post by:
Hi, I'm running Access 2002 and have a problem deleting records on a continuous form. I thought it might be due to relationships with two other tables but having deleted these relationships,...
2
by: davkohne | last post by:
I have two forms a main and sub. On the main form, I want to select a given set of criteria (i.e., "Project"), which pulls from a query and udpates a report number at the same time. I want to...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
1
by: Coll | last post by:
I'm working on a database that someone else created. I have a form - frm_main that is based on a query that is based on two tables. The join is such that the first table tbl_JCN has all records...
1
by: Chezza | last post by:
Hi All, Thanks to those that have helped me before. It’s been a couple of weeks since I posted (I can only play with this during my spare time at work) so here’s the set up again- we receive...
1
by: sphinney | last post by:
All, I'm not sure how to adequately explain my problem in two sentences or less, so at the risk of providing TMI, here's the condensed verion. I have developed an Access 2002 database file that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.