Looking up a Value for a Field

[cneill]

I am currently looking up a value using the following in the Contol Source of a Text field
=DLookUp("[GWhDelivered]","[QrySchemeActivityTotals]","[GWhDelivered]")

Is there a faster way to do this, I don't want use a subform as this will increase the form loading time
thanks
neill

 

[TheAceMan1]

How are ya cneill . . .

Use a function that performs the lookup on a Recordset. Example ... copy/paste the following function to the code module of the form:

[blue]Public Function Delivered()
   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("QrySchemeActivityTotals", dbOpenDynaset)
   Delivered = 0
   
   If Not rst.BOF Then
      rst.FindFirst "[PKname] = '" & Me!PKname
      
      If Not rst.NoMatch Then
         Delivered = rst!GWhDelivered
      Else
         MsgBox "Record Not Found!"
      Next
   Else
      MsgBox "Recordset is Empty!"
   End If
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

... and the controlsource will look like:

[blue]=Delivered()[/blue]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[gwinn7]

Good input Aceman, here is a new version...

Even Faster, less complex, and more re-usable....

Changes...
1. Added Parameter to function
2. Removed unnecessary Currentdb variable assignment
3. Changed recordset to dbforwardonly (faster!)
4. Removed "FindFirst" Approach (it was slower!)
5. Added NZ to protect from Nulls for return value.

Note: I am not sure why you needed to check if the recordset was empty, so, I just removed that part too.

Public Function Delivered(MyValue as string)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set rst = currentdb.OpenRecordset("SELECT PkName FROM QrySchemeActivityTotals WHERE [PKname] = '" & myValue & "'", dbOpenforwardonly)
Delivered = 0

If Not rst.BOF Then
Delivered = nz(rst("GWhDelivered"),0)
Else
MsgBox "Record Not Found!"
end if

Set rst = Nothing
Set db = Nothing


End Function

Simple simple..

Gary
gwinn7

 

[TheAceMan1]

gwinn7 . . .

Wow! ... I did say:

[blue]Use a function that performs the lookup on a Recordset. [purple]Example ...[/purple] copy/paste the following function to the code module of the form:[/blue]

There's alot that can be said about what you've presented (espcially 1,2,3). I'll get into this after [blue]cneill[/blue] is satisified with a resolution! ... believe me there are flaws.

I thought an example ... was an example!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JoeAtWork]

If this is:
1. A relatively static value (i.e. it's going to be the same the entire session)
2. Going to be required many times

I would suggest loading it once, then storing the result in a global variable that can be accessed from a global function. This avoids requerying the database over and over again.

 

[cneill]

Hi Gary,

There appears to be an error in your code, when I enter =Delivered() in the Controlsource I get a message to say the function has the wrong number of arguments
Any Thoughts

Thanks Neill

 

[gwinn7]

AceMan, relax!!! I know it was an example. I am curious to know what flaws you seem to find.

cneil,

The code I wrote was off the cuff without testing, but I have written numerous functions like this without experiencing issues. You may have to debug and customize to fit your needs. That said, you are missing the proper usage of the function...

=Delivered([myvalue])

not

=Delivered()

Gary
gwinn7

 

[cneill]

Hi Gary,
I have got a bit further with this
I am now getting Run-time error 3061 - Too few parameters. Expected 1, on the web it says I must put a
PARAMETERS clause in the SQL string in order
to feed the criteria into the WHERE clause.

This is the SQL for the Query QrySchemeActivityTotals
SELECT tblAccountSchemesActivity.ActivityID, Sum(tblAccountSchemesActivity.TCO2) AS TCO2Delivered, Sum(tblAccountSchemesActivity.Delivered) AS ElectricalDelivered, Sum(tblAccountSchemesActivity.ActualGWh) AS GWh
FROM tblAccountSchemesActivity
GROUP BY tblAccountSchemesActivity.ActivityID
HAVING (((tblAccountSchemesActivity.ActivityID)=[Forms]![FrmSchemes]![ActivityID]));

CAN YOU HELP PLEASE

 

[gwinn7]

cneil,

I built a working example for you with an updated function. Would you be comfortable putting an e-mail alias out so I can send it to you?

Please keep in mind, I am not fully aware of your project and what you are specifically attempting to accomplish. It is very likely that my working example may not fit your niche situation. Its only meant to show you how something can be done based on the information provided.

Gary
gwinn7

 

[cneill]

Hi Gary,

I do appreciate all your help and I know it is difficult with out knowing the full workings of my project.
happy to, but never done that before, how do I put out an e-mail alias?

Thanks Neill

 

[cneill]

Hi Gary,

Recieved fine, still no joy
I am now getting Run-time error 3061 - Too few parameters. Expected 1, I have changed your code to incorperate the Parameter value of the ActivityID, the GWh is a sum within the query so the query is filtered on the ActivityID to return the required record. Have I correctly added this parameter to the code below?

Public Function Delivered()
Dim ActivityID As String
ActivityID = Forms![FrmSchemes].ActivityID
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT sum([GWh]) as mysum FROM QrySchemeActivityTotals WHERE QrySchemeActivityTotals.[ActivityID] = " & Forms![FrmSchemes].ActivityID, dbOpenForwardOnly)

If Not rst.BOF Then
Delivered = Nz(rst("mysum"), 0)
Else
Delivered = 0
End If


End Function

 

[gwinn7]

Cneil,

I would stick to the example I gave and pass the function the parameter as defined.

Isn't the ActivityID field in your query a numerical value? If not, then you need to enclose with quotations like...

...[ActivityID = '" & ActivityID & "'"...

During Run-time, grab the SELECT query in the function and copy into a query definition and troubleshoot it there. It looks like a problem with how the QrySchemeActivityGoals is being used.

Btw, you should really consider getting out of the habit of dropping form control references into SELECT statements or queries. It makes testing and troubleshooting cumbersome and re-usability a b&tch.

Hope it helps,

Gary
gwinn7

 

[gwinn7]

ok, here is another look. Why not just call the dam# thing directly????


set rst = currentdb.openrecordset(
"SELECT tblAccountSchemesActivity.ActivityID, Sum(tblAccountSchemesActivity.TCO2) AS TCO2Delivered, Sum(tblAccountSchemesActivity.Delivered) AS ElectricalDelivered, Sum(tblAccountSchemesActivity.ActualGWh) AS GWh
FROM tblAccountSchemesActivity
WHERE tblAccountSchemesActivity.ActivityID = " & activityid & " GROUP BY tblAccountSchemesActivity.ActivityID", dbopensnapshot)

I also replaced your HAVING clause with a WHERE condition.

Gary
gwinn7

 

[cneill]

Hi Gary,

I removed the Criteria of [Forms]![FrmSchemes]![ActivityID] from the Query and changed the query slightly, now no error

so the code is now right, thanks for all your help

Neill

 

[gwinn7]

You are welcome!

OK, I am going to take a quick shot at what AceMan might have thought were issues with my code....

#1 Parameters -
I strongly advise against using form control references in your back end functions. Instead adding parameters gives you much more control and usabliity for testing and portability.

#2 You don't need to assign currentdb to a variable, unless your recordset is going to live outside of your function. In this case, its a fast lookup, which works perfectly.

#3 Yes, I think dbforwardonly would not have worked because of the nature of cneil's query, but, however, dbsnapshot sure would have worked, and still would have been better than dbopendynaset, which is also the default!

Btw, I think dbopenforwardonly may very well been your original problem when using my code, but as you see, I replaced it with dbopensnapshot, which is fast too. After that, it seemed to work fine.

Gary
gwinn7