How To Turn Off Query Datasheet Screen?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Everytime I run the following function, my screen will display the query
result which I do not need. The function result is to be use by another
query.

Function LastID() As Long
'qryProduct is a saved query
Dim lngSourceID As Long
Set rst = CurrentDb.OpenRecordset("Select * From qryProduct where ProductID
= " & lngSourceID & ";")
If Not rst.EOF Then LastID = rst!ProductID
End Function

Can someone tell me how to suppress the screen display of this query result?
Thanks in advance!

Scott
 
Hi Scott,

The code you posted is, I suspect, a cut-down version of your real function;
as posted, it won't run, since rst is not declared.

Using a saved query in a recordset does not cause the query datasheet to
open; so it's likely that the query datasheet is being opened by some other
code which you have omitted to post here.

I'm confused as to what you are trying to do here. For a start, you are not
passing any variable to your function, and you are declaring lngSourceID
within the function; it will therefore have an undefined value when it is
used in the SQL statement for the recordset. Then, your recordset will
return all records where ProductID is equal to lngSourceID; all these
records will have the same ProductID, and so your function will actually
just return lngSourceID as LastID (if it doesn't error out!).

What exactly are you trying to do? What is the exact code that you are
using? And what do you want LastID to be when the recordset is empty?

HTH,

Rob
 
Dear Rob,
I have found the bug and resolved the issue.
You are so helpful! Your advice "using a saved query in a recordset does not
cause the query datasheet to open" has prompted me to look at other auxiliary
queries for the source of the bug. I am sorry for posting a curtailed code
that caused confusion to you.

This LastID function retrieve the newest ID from a temp table that stores 2
identical products with different ID for use as bookmark on a form’s
RecordsetClone so user is present with the new product on the product form
for his further action.

I have revised my sample code below. Thanks Rob!

Function LastID() As Long
'qryDupProducts is a saved query
'lngSourceID is a module variable
Dim rst as recordset
Set rst = CurrentDb.OpenRecordset("Select * From qryDupProducts where
ProductID = " & lngSourceID & ";")
If Not rst.EOF Then LastID = rst!ProductID
End Function
 
Hi Scott,

Glad you got the query datasheet problem fixed.

The fact still remains that this function is essentially taking a roundabout
route to set LastID equal to lngSourceID. And, if lngSourceID is available
in the same module as this function is being called from, then you could
eliminate the function call altogether and simply replace the function call
with the statement:
LastID = lngSourceID

Again, HTH,

Rob
 
Dear Rob,
Oh yes, I am so careless in my sample code...
This SQL: ("Select * From qryDupProducts where ProductID = " & lngSourceID &
";") Should read:
("Select * From qryDupProducts where ProductID <> " & lngSourceID & ";")
Because qryDupProducts returns one original/source product and one
duplicate/copied product, so the SQL will return the duplicate one (not the
source)for use by lastID() to retrieve its ID.
Thanks for your help! :-)
 
Hi again Scott,

Now it makes more sense! One last point: you should be able to do this
without using a function involving a recordset at all - just use a dLookup
in your calling routine:

LastID = dLookup("ProductID","qryDupProducts","ProductID <> " &
lngSourceID)

HTH,

Rob
 
Dear Rob,

Point noted. I will modify my application to use dlookup() then, save many
lines.
Thanks!
 
Back
Top