Query BOF That Isn't...

[Bob Barnes]

An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem ??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

...but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

 

[Dorian]

Date is a reserved word, try ProdData.[Date] wherever you refer to it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Bob Barnes]

Thank you. I agree w/ the [Date], but the BOF I'm expecting still displays
as a "Record" w/ nothing in it.

Date is a reserved word, try ProdData.[Date] wherever you refer to it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem ??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

 

[eighthman11]

Thank you.  I agree w/ the [Date], but the BOF I'm expecting still displays
as a "Record" w/ nothing in it.

Date is a reserved word, try ProdData.[Date] wherever you refer to it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

"Bob Barnes" wrote:

- Show quoted text -

Hey Dorian,
Maybe you are have problems with null data. Did you try the Nz
function. For example nz(ProdData.McnID,0)=163. This gives your data
a default of 0 if the data is null. Might be worth a try. You might
want to do it for each field in your "Where' statement.

Ray

 

[Bob Quintal]

An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID,
ProdData.McnID FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND
((Prod Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a
Single Record) w/ what looks like Nulls (but testing for Len of
those Fields > 0, or IsNull doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a
problem ??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

You do not specify how you are using the SQL. if you are just opening
the query what you think is a blank row is the place where you can
enter data to create a new record.

there is no record until you place the cusror in any of the fields.

 

[Bob Barnes]

Thnak you.

Tried...SELECT [Troduction Data].Date, [Troduction Data].Shift,
[Troduction Data].PartID, [Troduction Data].McnID
FROM [Troduction Data]
WHERE ((([Troduction Data].Date)=#5/31/2009#) AND (Nz([Troduction
Data].Shift,0)=1) AND (Nz([Troduction Data].PartID=600,0)) AND
((Nz([Troduction Data].McnID,0))=163));

....but still the "Blank Record"...

Thank you. I agree w/ the [Date], but the BOF I'm expecting still displays
as a "Record" w/ nothing in it.

Date is a reserved word, try ProdData.[Date] wherever you refer to it.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

"Bob Barnes" wrote:

An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem ??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob- Hide quoted text -

- Show quoted text -

Hey Dorian,
Maybe you are have problems with null data. Did you try the Nz
function. For example nz(ProdData.McnID,0)=163. This gives your data
a default of 0 if the data is null. Might be worth a try. You might
want to do it for each field in your "Where' statement.

Ray

 

[Bob Barnes]

Thank you...I'm checking for the existence of a Mult-PK Record.

Would like to see why this doesn't work...I could Trap Error 3022 for a PK
violation. If I can't find a solution to the BOF, will have to Trap Error
3022.

 

[david]

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)

 

[Bob Barnes]

David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a Query
& manually running the Query. In both instances, I see the "Blank Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

 

[John Spencer MVP]

I duplicated your code and I did not see the same problem. I got the expected
results. I am using Access 2003 SP2

You might try using the recordset's record count *Rs.RecordCount > 0) or
using the DCount function instead. In this case, DCount should be simpler to
implement and I suspect that it will be just as fast (at least from a user's
perspective of the elapsed time).

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ") >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a Query
& manually running the Query. In both instances, I see the "Blank Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)

 

[Bob Barnes]

Thank you John - I pated the code in and there's something wrong w/ the
synatax...although I don't see a syntax error..it turns red..

I duplicated your code and I did not see the same problem. I got the expected
results. I am using Access 2003 SP2

You might try using the recordset's record count *Rs.RecordCount > 0) or
using the DCount function instead. In this case, DCount should be simpler to
implement and I suspect that it will be just as fast (at least from a user's
perspective of the elapsed time).

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ") >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a Query
& manually running the Query. In both instances, I see the "Blank Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)



An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single
Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or
IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem
??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

 

[John Spencer MVP]

Sometimes the newsreader will introduce spurious characters.

Try typing the code in. If that still fails, copy and paste what is failing
and post that.

AND SOMETIMES I make an error like including an extra quote mark at the end

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ) >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

I did assume that
-- [Date] is a date field
-- Shift is a number field
-- PartID is a number field
-- McnID is a number field

If any of those are incorrect, you will get an error.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you John - I pated the code in and there's something wrong w/ the
synatax...although I don't see a syntax error..it turns red..

I duplicated your code and I did not see the same problem. I got the expected
results. I am using Access 2003 SP2

You might try using the recordset's record count *Rs.RecordCount > 0) or
using the DCount function instead. In this case, DCount should be simpler to
implement and I suspect that it will be just as fast (at least from a user's
perspective of the elapsed time).

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ") >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a Query
& manually running the Query. In both instances, I see the "Blank Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

:

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)



An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single
Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or
IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem
??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

 

[Bob Barnes]

John - thank you. Your revised syntax works. But, it's still reading
"DCount" of 1..even though the record has NOT been saved. Plus, this is on
an unbound Form (I know...lot of Developers don't like those, but my Client
LOVES them..).

Don't know if the original design (now 10 years old) of the Field named
"Date" (even though we're referring to it in code as [Date]) has anything to
do w/ this.

Using the code, and saving as a Query will result in the "Blank Record"...I
would expect the Query Run to display a "Gray Line" of ONLY the Field Names
and see NO "white" since, in theory, the Record doesn't exist.

Something is saving the record (again no bound Controls) before the Function
looking for a DupRecord gives its result.

Pretty sure that testing for DupRecord, and just letting Error 3022 will
control what we need.

Your thoughts? TIA - Bob

Sometimes the newsreader will introduce spurious characters.

Try typing the code in. If that still fails, copy and paste what is failing
and post that.

AND SOMETIMES I make an error like including an extra quote mark at the end

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ) >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

I did assume that
-- [Date] is a date field
-- Shift is a number field
-- PartID is a number field
-- McnID is a number field

If any of those are incorrect, you will get an error.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you John - I pated the code in and there's something wrong w/ the
synatax...although I don't see a syntax error..it turns red..

I duplicated your code and I did not see the same problem. I got the expected
results. I am using Access 2003 SP2

You might try using the recordset's record count *Rs.RecordCount > 0) or
using the DCount function instead. In this case, DCount should be simpler to
implement and I suspect that it will be just as fast (at least from a user's
perspective of the elapsed time).

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ") >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob Barnes wrote:
David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a Query
& manually running the Query. In both instances, I see the "Blank Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

:

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)



An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single
Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or
IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem
??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

 

[John Spencer]

Is it possible that there is a record out there that meets the
requirement and all the fields are zero-length strings? If you are
getting a count of 1 then I would say that whatever the record you are
looking for does exist. Have you tried seeing what the values of the
various fields are - using your original code?

Debug.Print Rs!Date, RS.Shift, RS.PartID, RS.McnID, "Data"
If that prints values that match then I would conclude there is a record
somewhere with the values. You might want to make that a bit more
comprehensive with a second Debug.Print right after or right before

Debug.Print Thedate, Partshift, cboPartID, cboMcnID, "Criteria"

Working with unbound forms is a pain and really robs you of many of the
strengths of Access.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John - thank you. Your revised syntax works. But, it's still reading
"DCount" of 1..even though the record has NOT been saved. Plus, this is on
an unbound Form (I know...lot of Developers don't like those, but my Client
LOVES them..).

Don't know if the original design (now 10 years old) of the Field named
"Date" (even though we're referring to it in code as [Date]) has anything to
do w/ this.

Using the code, and saving as a Query will result in the "Blank Record"...I
would expect the Query Run to display a "Gray Line" of ONLY the Field Names
and see NO "white" since, in theory, the Record doesn't exist.

Something is saving the record (again no bound Controls) before the Function
looking for a DupRecord gives its result.

Pretty sure that testing for DupRecord, and just letting Error 3022 will
control what we need.

Your thoughts? TIA - Bob

Sometimes the newsreader will introduce spurious characters.

Try typing the code in. If that still fails, copy and paste what is failing
and post that.

AND SOMETIMES I make an error like including an extra quote mark at the end

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ) >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

I did assume that
-- [Date] is a date field
-- Shift is a number field
-- PartID is a number field
-- McnID is a number field

If any of those are incorrect, you will get an error.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you John - I pated the code in and there's something wrong w/ the
synatax...although I don't see a syntax error..it turns red..

:

I duplicated your code and I did not see the same problem. I got the expected
results. I am using Access 2003 SP2

You might try using the recordset's record count *Rs.RecordCount > 0) or
using the DCount function instead. In this case, DCount should be simpler to
implement and I suspect that it will be just as fast (at least from a user's
perspective of the elapsed time).

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ") >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob Barnes wrote:
David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a Query
& manually running the Query. In both instances, I see the "Blank Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

:

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)



An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single
Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or
IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem
??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

 

[Bob Barnes]

Thank you John.

I checked both Debug.Print and YES..they both include the values indicationg
a Record exists...but I'm still not sure what adds that New Record.

I will exclude the Function checking for a "Duplicate Record" and allow
Error 3022 to be as a Trap.

Thanks again...it's good to think thru this to see what could be causing the
issue.

Bob

Is it possible that there is a record out there that meets the
requirement and all the fields are zero-length strings? If you are
getting a count of 1 then I would say that whatever the record you are
looking for does exist. Have you tried seeing what the values of the
various fields are - using your original code?

Debug.Print Rs!Date, RS.Shift, RS.PartID, RS.McnID, "Data"
If that prints values that match then I would conclude there is a record
somewhere with the values. You might want to make that a bit more
comprehensive with a second Debug.Print right after or right before

Debug.Print Thedate, Partshift, cboPartID, cboMcnID, "Criteria"

Working with unbound forms is a pain and really robs you of many of the
strengths of Access.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John - thank you. Your revised syntax works. But, it's still reading
"DCount" of 1..even though the record has NOT been saved. Plus, this is on
an unbound Form (I know...lot of Developers don't like those, but my Client
LOVES them..).

Don't know if the original design (now 10 years old) of the Field named
"Date" (even though we're referring to it in code as [Date]) has anything to
do w/ this.

Using the code, and saving as a Query will result in the "Blank Record"...I
would expect the Query Run to display a "Gray Line" of ONLY the Field Names
and see NO "white" since, in theory, the Record doesn't exist.

Something is saving the record (again no bound Controls) before the Function
looking for a DupRecord gives its result.

Pretty sure that testing for DupRecord, and just letting Error 3022 will
control what we need.

Your thoughts? TIA - Bob

Sometimes the newsreader will introduce spurious characters.

Try typing the code in. If that still fails, copy and paste what is failing
and post that.

AND SOMETIMES I make an error like including an extra quote mark at the end

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ) >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

I did assume that
-- [Date] is a date field
-- Shift is a number field
-- PartID is a number field
-- McnID is a number field

If any of those are incorrect, you will get an error.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob Barnes wrote:
Thank you John - I pated the code in and there's something wrong w/ the
synatax...although I don't see a syntax error..it turns red..

:

I duplicated your code and I did not see the same problem. I got the expected
results. I am using Access 2003 SP2

You might try using the recordset's record count *Rs.RecordCount > 0) or
using the DCount function instead. In this case, DCount should be simpler to
implement and I suspect that it will be just as fast (at least from a user's
perspective of the elapsed time).

IF DCount("*","[Troduction Data]", _
"[Date] = #" & TheDate & "# AND" _
& " Shift = " & PartShift & " AND" _
& " PartID = " & cboPartID & " AND" _
& " McnID = " & cboMcnID ") >0 Then

Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Bob Barnes wrote:
David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a Query
& manually running the Query. In both instances, I see the "Blank Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

:

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)



An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND ((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single
Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or
IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a problem
??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob

 

[david]

hmm. I don't get that BOF/EOF. (As an Access query there is always
a blank record)

Is [Troduction Data] a table?

(david)

David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a
Query
& manually running the Query. In both instances, I see the "Blank
Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)

 

[Bob Barnes]

[Troduction Data] IS a table...ideas ???

hmm. I don't get that BOF/EOF. (As an Access query there is always
a blank record)

Is [Troduction Data] a table?

(david)

David - Thank you.

I've opened the recordset 2 ways...thru VBA code, and then making it a
Query
& manually running the Query. In both instances, I see the "Blank
Record"...

Here's concise VBA code which opens the "Blank Record", even though it
doesn't exist..here running a Function "Check_For_Duplicate_Records"

Dim Z As Database, RS As DAO.Recordset, Q$
'Query DB to see if DupRec for Date, Shift, Part & Mcn
'for data being entered = 4-Field PK.
Set Z = CurrentDb
Q = "SELECT DISTINCTROW [Date], Shift, PartID, McnID " _
& "From [Troduction Data]" _
& " WHERE (([Date] = #" & TheDate & "#) AND" _
& " (Shift = " & PartShift & ") AND" _
& " (PartID = " & cboPartID & ") AND" _
& " (McnID = " & cboMcnID & "));"
Set RS = Z.OpenRecordset(Q, dbOpenSnapshot)
With RS
If Not .BOF And Not .EOF Then
'now you know you have a minimum of one record
Check_For_Duplicate_Records = "YES"
Else
Check_For_Duplicate_Records = "NO"
End If
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With

It results in...
Check_For_Duplicate_Records = "YES"

Ideas? TIA - Bob

How are you doing that? When I open a query like
that in Access, I always get a blank line. It's the blank
line for entering data.

You get the blank line if you open the recordset that way.
So how are you opening the recordset?


Also,
BOF and EOF may not be valid unless you have done a
MoveFirst/MoveLast.

There is an implicit MoveFirst when you do a requery.

In some rare situations, there is not an implicit MoveFirst,
even though the situation is almost the same as all the other
things you have done, where there was an implicit MoveFirst.
So how are you opening the recordset?

(david)



An example SQL..
SELECT ProdData.Date, ProdData.Shift, ProdData.PartID, ProdData.McnID
FROM ProdData
WHERE (((ProdData.Date)=#5/31/2009#) AND ((ProdData.Shift)=1) AND
((Prod
Data.PartID)=475) AND ((ProdData.McnID)=163));

The above Record does NOT exist, but returns a "Blank Row" (a Single
Record)
w/ what looks like Nulls (but testing for Len of those Fields > 0, or
IsNull
doesn't work)..it returns a "Record".

I've inherited this DB, and perhaps the Field "Date" is causing a
problem
??

Even tried...
with rst
if not .bof and not .eof then
now you know you have a minimum of one record
else
no records so do something here...
end if
end with

..but that returns a "Record"...even though it shouldn't??

Ideas? TIA - Bob