Search in 2 tables for a duplicate

attman

Registered User.
Local time
Today, 05:58
Joined
Jun 25, 2013
Messages
29
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
 
Last edited:
ID's usually are number fields, try removing the ' as they are only needed for text fields

FYI List5, keeping default control names is a bad idea, better to give your controls a proper name and prevent the headaches that will eventually come from keeping the default names.
 
I really am too careless sometimes - many thanks! (I'll take on board your name critique)
 

Users who are viewing this thread

Back
Top Bottom