Recordset

[Alib]

I’m trying to test whether a record exists before entering a new record


I have a recordset that returns a record with 1 field with a value of 0. This isn’t a valid answer to the query so not sure what it is? It does mean I can’t test for .eof and .bof
What would cause this and how can I cater for it?

[ranman256]

what do you want to see? If the record exists, there will be a key. (autoID) Are you using them here?
otherwise ask for the field you DO want with some value. No BOF nor EOF needed.
Dlookup is always a good check too.

[CJ_London]

or use dcount, but if using recordsets, if no records are returned then bof and eof will both be true
Suggest provide your code so we can see what you are trying to do

[Alib]

I think my problem is with the date:

The record that exists is for the date 2nd March 2018 - stored in the TrainingRecords_t table as 02/03/2018

There isn't a TraineeID = 0 but when a record ISN'T found it still returns 1 record with a 0 in the TraineeID field

strCheck returns the date as 03/02/2018

My table is: see image


Course

Here is the code snippet that generates the SQL string for the recordset

dteDate = Format(dtpDate, "dd/mm/yyyy")
'if codes has got to here then ther are no errors
'open a connection and check it is not a duplicate record
'generate select query based on new record being added
strCheck = ""
strCheck = "SELECT TrainingRecords_t.TraineeID "
strCheck = strCheck & "FROM TrainingRecords_t "
strCheck = strCheck & "WHERE TrainingRecords_t.TraineeID= " & cbxTrainee & " AND TrainingRecords_t.CourseDate=#" & dteDate & "# AND TrainingRecords_t.CourseID= " & cbxCourseTitle

[CJ_London]

when using dates in sql you need to use the US date format of month/day/year, so

dteDate = Format(dtpDate, "dd/mm/yyyy")

should be

dteDate = Format(dtpDate, "mm/dd/yyyy")

[Alib]

Thank you Ajax. That seems to solved my issue.

[ssanfu]

RE: your table "TrainingRecords_T" design.

Having a multi-field PK field is the wrong way to go. You should have the autonumber field "TrainingRecordID" as the PK field and "TraineeID", "CourseID" and "CourseDate" as a secondary index to prevent duplicates.
See Microsoft Access Tables: Primary Key Tips and Techniques
Read this several times (I still go back and read it -must be up to 100 times by now). Pay special attention to the last 2 headings.


And Welcome to the forum...

[Alib]

Thank you. Table designs duly changed