Solved Run-Time error 13 - Type Mismatch (1 Viewer)

Ashfaque

Student
Local time
Today, 18:06
Joined
Sep 6, 2004
Messages
894
I am going to delete those subform records whose checkmark value is none I mean unchecked and OTID (int) is equal to mainform which is F_EmpOTHeader.

CheckRightOT is a checkmark (Bit type field) and available in each records in Subform.

Code:
Private Sub OMApproval_Click()
Dim Msg As String, Style As Integer, Title As String, Response As Integer
Dim strSQL As String
Msg = "You are about to delete all unchecked entries from sub-form."
Style = vbOKCancel + vbQuestion + vbDefaultButton2
Title = "Continue?"
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then
strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID
CurrentDb.Execute dbFailOnError
Me!SF_EmpOTFooter.Form.Requery
Else
MsgBox "No record deleted", vbOKOnly, "Info"
End If
End Sub

But producing Run-time error 13 - type mismatch on

strSQL = "DELETE * FROM T_EmpOTFooter WHERE CheckRightOT.value = false " And " OTID = " & Forms!F_EmpOTHeader!OTID

Can you please advise where is wrong?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Hi. Try taking out the two double quotes on each side of the word And and try using the number 0 in place of false.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:36
Joined
May 7, 2009
Messages
19,169
remove the:

.value

from strSQL
 

Ashfaque

Student
Local time
Today, 18:06
Joined
Sep 6, 2004
Messages
894
Thanks theDBguy,

This way it removed error 13 :
strSQL = "DELETE * FROM T_EmpOTFooter WHERE OTID = Forms!F_EmpOTHeader!OTID And CheckRightOT.value = 0 "
CurrentDb.Execute dbFailOnError
Me!SF_EmpOTFooter.Form.Requery

but produced new run-time error 3078 - table not found.

My bound subform from where I am trying to delete records is available with full data in it.. the idea is to delete all the records that are unchecked in subform and lock subform for editing. (no data entry, modfication or editing once it is approved (OMApproval_Click())
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:36
Joined
May 7, 2009
Messages
19,169
trSQL = "DELETE * FROM T_EmpOTFooter WHERE OTID = " & Forms!F_EmpOTHeader!OTID & " And CheckRightOT = 0; "
 

Ashfaque

Student
Local time
Today, 18:06
Joined
Sep 6, 2004
Messages
894
trSQL = "DELETE * FROM T_EmpOTFooter WHERE OTID = " & Forms!F_EmpOTHeader!OTID & " And CheckRightOT = 0; "
Thanks Arnel,

But still same error....Can not find input tble or query '128'
 

Ashfaque

Student
Local time
Today, 18:06
Joined
Sep 6, 2004
Messages
894
Below is the result from immedate vba window

DELETE * FROM T_EmpOTFooter WHERE OTID = 2 And CheckRightOT=0;

OTID is 2nd record which is correct but CheckRightOT=0 means it is not satisfying second condition.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:36
Joined
May 7, 2009
Messages
19,169
am i correct to say that CheckRightOT is Bit type in MSSQL?
you should set its Default to (0), so that you will not need
to test if it is Null;

[CheckRightOT] Bit NOT NULL DEFAULT (0)
 

Ashfaque

Student
Local time
Today, 18:06
Joined
Sep 6, 2004
Messages
894
am i correct to say that CheckRightOT is Bit type in MSSQL?
you should set its Default to (0), so that you will not need
to test if it is Null;

[CheckRightOT] Bit NOT NULL DEFAULT (0)
Yes, it is Bit and Allow null = Yes, Default Value or Binding set to (-1) as default on server

I just want to explain here..that CheckRightOT is on current subform and there are multiple records in subform that relates to main form record (one-to-many). So the click event will trigger the above to remove all records from existing current subform which has no checkmarked. And thereafter lock the subform for edit and delete or add.
Please see the pic I just attached. Code will run once I checkmarked (Yellow arrow) on main form and those records from subform shold be deleted which are still uncheked. In this case one record to delete and 2 to remain as they are.
 

Attachments

  • ERROR.jpg
    ERROR.jpg
    48.9 KB · Views: 541
Last edited:

Ashfaque

Student
Local time
Today, 18:06
Joined
Sep 6, 2004
Messages
894
Hi,

I managed it following way and it worked well.
Code:
Dim RunSQL As String
DoCmd.RunSQL "DELETE * FROM T_EmpOTFooter where Verified = 0 And OTID = Forms!F_EmpOTHeader!OTID"
Debug.Print RunSQL

Thanks,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Congratulations! Good luck with your project.
 

Users who are viewing this thread

Top Bottom