SQL in Access

Jim Hull
01-15-2004, 08:50 AM
I have a table with appointments in. I want to be able to use a sql to see if a record is already present. This is what I am using


Private Sub Command1_Click()

Dim sSQL As String
Dim db As DAO.Database
Dim tblRst As DAO.Recordset

Set db = CurrentDb
Set tblRst = db.OpenRecordset("Bookings", dbOpenTable)

sSQL = "Select * From Bookings Where DateBooked= #" & Format(Calendar.Value, "yyyy-mm-dd") & "# " _
& " And SessionBooked= '" & SessionBooked.Value & "' And CustomerName= '" & txtName.Value & "'"

If tblRst.EOF And tblRst.BOF Then

MsgBox "OK TO BOOK"
Else
MsgBox "ALREADY BOOKED"
End If
End Sub


If there is no record in the table, when I run the code for any date the msgbox with 'ok to book' comes up like it should. However, if I add a record into the table, say for example, 01/01/2004 and run the code, every single date I choose from the calendar comes up with the msgbox 'already booked' when that date is not already booked!!
Am I doing somthing wrong with the code above?

I need to be able to add other dates to table when there are different dates booked. So If the .EOF and .BOF and set to False which they will be because there is already data in the table, then this will not work, will it?

Shurik12
01-20-2004, 05:08 PM
Hi,

I find this part of code a bit strange:



Set tblRst = db.OpenRecordset("Bookings", dbOpenTable)

sSQL = "Select * From Bookings Where DateBooked= #" & Format(Calendar.Value, "yyyy-mm-dd") & "# " _ & " And SessionBooked= '" & SessionBooked.Value & "' And CustomerName= '" & txtName.Value & "'"



You first open a recordset and then do sSQL="...".
What the point of doing it?

Also could you show the code where you add records?


Regards,
Shurik.

Jim Hull
01-21-2004, 09:42 AM
Yes it was strange!!! I realised what I have done now. Ive got it working. This is what I am using:

Private Sub Command1_Click()
Dim db As DAO.Database
Dim tblRst As DAO.Recordset

Set db = CurrentDb
Set tblRst = db.OpenRecordset("Select * From Bookings Where DateBooked= #" & Format(Calendar.Value, "yyyy-mm-dd") & "# " _
& " And SessionBooked= '" & SessionBooked.Value & "' And CustomerName= '" & txtName.Value & "'")

If tblRst.EOF And tblRst.BOF Then
MsgBox "ALREADY BOOKED"
Else

With tblRst
.AddNew
.Fields("DateBooked") = Calendar.Value
.Fields("SessionBooked") = SessionBooked.Value
.Fields("CustomerName") = txtName.Value
.Update
End With
End If
tblRst.Close
End Sub

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum