ERROR # -2147217913 JET DATABASE ENGINE 1

[JoPaBC]

I have this code:

If (((STD - FD) >= 30) And D15 >= 15) Then 'EMPLOYED MORE THAN 30 DAYS, GETS EXTRA DAY

If JOBID = 20 Then 'STAT HOLIDAY FOR COOKS/KITCHEN STAFF
DR = 1
WTYPE = "S/T"

sSQL = "SELECT ([WHRS]) From WHOURS Where [EMPID]=" & EMPID & " AND [WDATE]=""" & STD & """ AND [WTYPE]=""" & WTYPE & """;"
Set rst3 = New ADODB.Recordset
rst3.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

If rst3.RecordCount = 0 Then

sSQL = "INSERT INTO WHOURS (EMPID, WDATE, WTYPE, WHRS, PCODE, WRATE, WEEKN, PAYPER) VALUES ('" & EMPID & "', '" & STD & "', '" & WTYPE & "', '" & DR & "', '" & JOBID & "', '" & RATE & "', '" & WNUM & "', '" & PPID & "'); "
Set rsAccess = New ADODB.Recordset
rsAccess.CursorLocation = adUseClient
rsAccess.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic, adCmdText

End If
End If
End If

but it is giving me the above error message.
This code supposes to check if an employee doesn't already have statutory holiday for the same date to avoid double entry.
I tried to change adOpenKeyset to adOpenDynamic but no difference.
Any help please?

 

[Andrzejek]

I would try:

 sSQL = "INSERT INTO WHOURS (EMPID, WDATE, WTYPE, WHRS, PCODE, WRATE, WEEKN, PAYPER) VALUES ('" & _
EMPID & "', '" & STD & "', '" & WTYPE & "', '" & DR & "', '" & _
JOBID & "', '" & RATE & "', '" & WNUM & "', '" & PPID & "'); "

Debug.Print sSQL

objAccessConnection.Execute sSQL

And you can show us what's in the Immediate window.

BTW - do you have any fields in your WHOURS table that are defined as DATE ?
Based on your INSERT statement, all fields are defined as Text (?)


---- Andy

There is a great need for a sarcasm font.

 

[JoPaBC]

SELECT ([WHRS]) From WHOURS Where [EMPID]=668 AND [WDATE]="8/06/18" AND [WTYPE]="S/T";
INSERT INTO WHOURS (EMPID, WDATE, WTYPE, WHRS, PCODE, WRATE, WEEKN, PAYPER) VALUES ('668', '8/06/18', 'S/T', '1', '20', '170', '31', '15');


I started getting error after I added this:
If rst3.RecordCount = 0 Then
sSQL = "INSERT INTO WHOURS (EMPID, WDATE, WTYPE, WHRS, PCODE, WRATE, WEEKN, PAYPER) VALUES ('" & EMPID & "', '" & STD & "', '" & WTYPE & "', '" & DR & "', '" & JOBID & "', '" & RATE & "', '" & WNUM & "', '" & PPID & "'); "
Set rsAccess = New ADODB.Recordset
rsAccess.CursorLocation = adUseClient
rsAccess.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic, adCmdText
End If

Yes, WDATE is defined as DATE

Thanks

 

[Andrzejek]

If WDATE is defined as DATE, and this is ACCESS, try:

sSQL = "INSERT INTO WHOURS (EMPID, [blue]WDATE[/blue], WTYPE, WHRS, PCODE, WRATE, WEEKN, PAYPER) VALUES ('" & _
EMPID & "', [blue]#[/blue]" & STD & "[blue]#[/blue], '" & WTYPE & "', '" & DR & "', '" & _
JOBID & "', '" & RATE & "', '" & WNUM & "', '" & PPID & "'); "

Debug.Print sSQL
[blue]
objAccessConnection.Execute sSQL [/blue] [green]' <-- Try this instead [/green]

---- Andy

There is a great need for a sarcasm font.

 

[JoPaBC]

sorry for my mistake, I am getting this error after adding this code:

sSQL = "SELECT ([WHRS]) From WHOURS Where [EMPID]=" & EMPID & " AND [WDATE]=""" & STD & """ AND [WTYPE]=""" & WTYPE & """;"
Set rst3 = New ADODB.Recordset
rst3.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

If rst3.RecordCount = 0 Then
.....

End If

when I remove this SELECT and ' If rst3.RecordCount = 0' it runs OK but some people are getting duplicates because their names are twice in the time cards (they do multiple jobs or work for multiple rate)

yes, it is MS Access

debug.print for SELECT
SELECT ([WHRS]) From WHOURS Where [EMPID]=668 AND [WDATE]="8/06/18" AND [WTYPE]="S/T";

 

[Andrzejek]

OK, then [blue]try[/blue]:

sSQL = "SELECT * From WHOURS Where EMPID=" & EMPID & " AND WDATE= [blue]#[/blue]" & STD & "[blue]#[/blue] AND WTYPE=""" & WTYPE & """;"

Debug.Print sSQL

 Set rst3 = New ADODB.Recordset
 rst3.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic

---- Andy

There is a great need for a sarcasm font.

 

[JoPaBC]

Excellent, no error!

SELECT * From WHOURS Where EMPID=653 AND WDATE= #8/06/18# AND WTYPE="S/T";

Thank you very much!

 

[Andrzejek]

---- Andy

There is a great need for a sarcasm font.