I've Never Seen This Happen Before (1 Viewer)

TheSearcher

Registered User.
Local time
Today, 04:29
Joined
Jul 21, 2011
Messages
185
I have a function that queries a table. The table has a record in it. But the recordset returns EOF.
The first msgbox tells me that the query reads correctly.
The second msgbox tells me that there are no records matching the criteria of the query. But there definitely is. If I copy and paste the query in design mode it correctly returns the record.
Any idea what's going on here?

Code:
Public Function CheckLock(ByVal UID As Integer)

Dim sql1 As String
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb

sql1 = "select UID, Full_Name from tbl_Lock where UID = " & UID

MsgBox sql1

Set rs = db.OpenRecordset(sql1)

If rs.EOF Then
    MsgBox "EOF"
End If

If rs.EOF Then
    CheckLock = "Free"
Else
    CheckLock = "Locked"
End If

rs.Close

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:29
Joined
Sep 21, 2011
Messages
8,832
Change the first msgbox to a debug.print and post that output.
Also a pic of the table with that record.
 

TheSearcher

Registered User.
Local time
Today, 04:29
Joined
Jul 21, 2011
Messages
185
I changed it to:
debug.print
and then to:
debug.print sql1

and there is no output. What should I expect to see?
 

TheSearcher

Registered User.
Local time
Today, 04:29
Joined
Jul 21, 2011
Messages
185
CheckLock 241
SELECT tbl_Lock.UID, tbl_Lock.Full_Name From tbl_Lock WHERE (((tbl_Lock.UID)= 241));

UID Full_Name
241 John Smith
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:29
Joined
Sep 21, 2011
Messages
8,832
That is not a pic of the table?

Can you upload a dB with relevant data/objects to replicate the issue?
 

moke123

AWF VIP
Local time
Today, 04:29
Joined
Jan 11, 2013
Messages
2,531
SELECT tbl_Lock.UID, tbl_Lock.Full_Name From tbl_Lock WHERE (((tbl_Lock.UID)= 241));
if this is what your debug.print returns somethings not right as there should not be any ( )'s in the print.

I would probably use something else rather than .EOF
Why not use rs.recordcount or just a dcount?
 

Isaac

Lifelong Learner
Local time
Today, 01:29
Joined
Mar 14, 2017
Messages
5,578
At this point it may be a good idea to post 100% of your code, and include any function code that the calling code references.

If debug.print sql1

returns nothing in the Immediate window, something is wrong. You were checking the Immediate Window for the output...Right?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:29
Joined
Feb 28, 2001
Messages
20,690
@TheSearcher - it might do nothing at all, but .... just after your rs.OpenRecordset, insert

rs.MoveFirst

and see if that helps. Shouldn't have to, but heck, it is an easy enough experiment.
 

TheSearcher

Registered User.
Local time
Today, 04:29
Joined
Jul 21, 2011
Messages
185
Gasman - Yes it is a pic of the table
Moke123 - I just copied and pasted the SQL that the Access query designer generated. I also tried it without the parens. Still did not work.
Isaac - I'll work on posting the actual database. I know that debug.print posts to the immediate window but since my function is called from a form I usually use msgbox to debug. I did test the function in the immediate window, however, and posted the results in a previous post.
Doc_Man - rs.movefirst doesn't work because Access thinks the table is empty.
Thank you all for your help! I'll continue to troubleshoot and keep you all posted.
 

Isaac

Lifelong Learner
Local time
Today, 01:29
Joined
Mar 14, 2017
Messages
5,578
I know that debug.print posts to the immediate window but since my function is called from a form I usually use msgbox to debug
The fact that the code runs based on a form does not preclude you from printing output to the immediate window

I did test the function in the immediate window, however, and posted the results in a previous post.
You also mentioned, in post #3:
there is no output

From which I conclude either one of the following is true:
1) you're not properly viewing the immediate window content, or
2) sql1 variable is empty at the time it runs
 

bastanu

AWF VIP
Local time
Today, 01:29
Joined
Apr 13, 2010
Messages
864
How about this modified version:
Code:
Public Function CheckLock(UID As Long) as String

Dim sql1 As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

sql1 = "Select UID, Full_Name From tbl_Lock where UID = " & UID

MsgBox sql1

Set rs = db.OpenRecordset(sql1)

If rs.recordcount=0 Then
    MsgBox "EOF"
End If

If rs.recordcount=0 Then
    CheckLock = "Free"
Else
    CheckLock = "Locked"
End If

rs.Close
Set rs=Nothing
Set db=Nothing
End Function

And of course can you confirm UID is defined as a long or integer in the table?

Cheers,
 

TheSearcher

Registered User.
Local time
Today, 04:29
Joined
Jul 21, 2011
Messages
185
See attached.
Steps to replicate recordset issue:
DoubleClick on frm_Login: User ID: tuser Password: testing
Click on Directors/CCO button
Doubleclick on Test_Client - EOF message box appears. This is correct since the record was not locked.
Open tbl_Lock - You can see that the record appears in the table and therefore is now locked.

Close frm_Main by clicking on the upper right X
Click on frm_Login: User ID: tuser Password: testing
Click on Directors/CCO button
Doubleclick on Test_Client - EOF message box appears. This is INCORRECT since tbl_Lock has a record in it.
Open tbl_Lock to see that a record exists.

I very much appreciate your time on this. I've never run across something like this before.
 

Attachments

  • Recordset_Issue.accdb
    680 KB · Views: 29

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:29
Joined
May 7, 2009
Messages
13,797
your code is doing Exactly as it should.
see the code on the Click event of "Directors/CCO":

Code:
Private Sub cmd_Directors_Click()

RemoveRecordLock
DoCmd.OpenForm "frm_Executive", acNormal
DoCmd.Close acForm, "frm_Main"


End Sub

The code on RemoveRecordLock:
Code:
Public Sub RemoveRecordLock()

Dim sql1 As String

sql1 = "DELETE tbl_Lock.*, tbl_Lock.UID From tbl_Lock WHERE tbl_Lock.UID = " & Globals.glb_UID
DoCmd.SetWarnings False
DoCmd.RunSQL sql1
DoCmd.SetWarnings True


End Sub
and on the Double-Click of the List report, you are setting Globals.glb_UID:

Globals.glb_UID = lst_ExistingReports.Column(4)

therefore, when you exit the main form, Globals.glb_UID is set to the "tuser" (id).
you login again using "tuser" and RemoveRecordLock will delete the record of "tuser".
 

bastanu

AWF VIP
Local time
Today, 01:29
Joined
Apr 13, 2010
Messages
864
Have a look at the attached file, I added some comments that might help. You should change UID from Integer to Long as it is based on ID which is an autonumber (long integer).
 

Attachments

  • Recordset_Issue_Vlad.zip
    138.3 KB · Views: 29

Users who are viewing this thread

Top Bottom