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
- Private Sub Command63_Click()
- Dim CurrHospID As String
- Dim strSQL As String
- Dim intAnswer As Integer
- CurrHospID = Me.HospID
- strSQL = "DELETE * FROM Patients WHERE Patients.HospID = " & CurrHospID
- 'strSQL = "DELETE * FROM Patients WHERE QRY_Record.HospID IN (SELECT HospID FROM Patients) = " & CurrHospID
- intAnswer = MsgBox("Do you want to delete " & Me.HospID & ", " & Me.Forename & " " & Me.Surname & "?", _
- vbYesNo Or vbDefaultButton2, "Confirm Delete Patient")
- If intAnswer = vbYes Then
- DoCmd.RunSQL strSQL
- End If
- DoCmd.Requery
- Exit_Command63_Click:
- Exit Sub
- End Sub
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.