Before I start, I consider myself a programmer who is punching far above his weight! I really struggle with SQL.
I have a database for my school that I'm creating to log books read by Pupils.
I have a Read table linked to both to a Pupil table and a Book table to say that the book has been read.
When I click on a Book in a listbox this adds a record to the Read table for the pupil - this code works great.
I want to check that the book is not already there but my code is not working.
Below the ID should come from the Pupil table and BookID from the Read table.
I think this is the problem....
The code stops on the Set*rs* = *db.OpenRecordset(mysql, *dbOpenSnapshot) with "Data type mismatch in criteria expression".
At this point mysql is "SELECT Count (ID) as Total from Read WHERE ID = '952' and BOOKID = '13';"
Option Compare Database
Private Sub List5_DblClick(Cancel As Integer)
Rem ------ 1st SECTION
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mysql As String
Set db = CurrentDb
'define an SQL string for the recordset
mysql = "SELECT Count (ID) as Total From Read "
mysql = mysql & "WHERE ID='" & ID & "' AND "
mysql = mysql & "BOOKID='" & Me.List5 & "';"
Debug.Print mysql
'open a recordset based on that sql
Set rs = db.OpenRecordset(mysql, dbOpenSnapshot)
'because it counts one row will be returned to grab the value
'and depending on the result of the count set the return value
'logic to to the calling procedure
Debug.Print rs!total
If rs!total > 0 Then
CheckDuplicates = True
MsgBox "Dupe"
GoTo exitit
Else
CheckDuplicates = False
End If
mysql = ""
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Rem ------2 nd SECTION
Set rs = CurrentDb.OpenRecordset("select * from Read")
With rs
.AddNew
!ID = Me.ID
!BOOKID = Me.List5
.Update
MsgBox (Me.List5.Column(1) + " Added!")
.Close
End With
Set rs = Nothing
exitit:
Me!frmWBRsubform.Requery
End Sub
I have a database for my school that I'm creating to log books read by Pupils.
I have a Read table linked to both to a Pupil table and a Book table to say that the book has been read.
When I click on a Book in a listbox this adds a record to the Read table for the pupil - this code works great.
I want to check that the book is not already there but my code is not working.
Below the ID should come from the Pupil table and BookID from the Read table.
I think this is the problem....
The code stops on the Set*rs* = *db.OpenRecordset(mysql, *dbOpenSnapshot) with "Data type mismatch in criteria expression".
At this point mysql is "SELECT Count (ID) as Total from Read WHERE ID = '952' and BOOKID = '13';"
Option Compare Database
Private Sub List5_DblClick(Cancel As Integer)
Rem ------ 1st SECTION
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim mysql As String
Set db = CurrentDb
'define an SQL string for the recordset
mysql = "SELECT Count (ID) as Total From Read "
mysql = mysql & "WHERE ID='" & ID & "' AND "
mysql = mysql & "BOOKID='" & Me.List5 & "';"
Debug.Print mysql
'open a recordset based on that sql
Set rs = db.OpenRecordset(mysql, dbOpenSnapshot)
'because it counts one row will be returned to grab the value
'and depending on the result of the count set the return value
'logic to to the calling procedure
Debug.Print rs!total
If rs!total > 0 Then
CheckDuplicates = True
MsgBox "Dupe"
GoTo exitit
Else
CheckDuplicates = False
End If
mysql = ""
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Rem ------2 nd SECTION
Set rs = CurrentDb.OpenRecordset("select * from Read")
With rs
.AddNew
!ID = Me.ID
!BOOKID = Me.List5
.Update
MsgBox (Me.List5.Column(1) + " Added!")
.Close
End With
Set rs = Nothing
exitit:
Me!frmWBRsubform.Requery
End Sub
Last edited:
