VBA Code for pulling data from table using form 1

[lvengineer]

Hello All,
I am trying to extract data from a table bound to a form, peform some simple math on the data, and, display the results in a text box.
I am currently using the DLookup function for selecting table cells but would like to specify name in the DLookup or learn another method for "gleaning" data from tables.
My table1, "Activities" contains fields, Date:, Name:, Credit:.
Table 2, "Stats", contains field, Name:, Total Credit:.
I would like to sum the credit fields of both tables for a given name selected in my combobox.
Currently code is:
dblCr = DLookup("Sum([Credit:])", "Activities", "") + DLookup("Sum([Total Credit:])", "Stats", "")
Obviously, this yields credits for all users. How do I specify a particular name?

How do I code the name criteria? Is there a better way to get data from a table?

Thanks in advance,

Bruce

 

[Eupher]

Hi, Bruce,

First, I would strongly consider renaming some your fields. "Date" is the name of a common VBA function, and "Name" is a property of just about every object available in Access. You're courting trouble when you use such names in your tables/forms/reports, etc.

Now to your question... You need to include the "where" argument in your DLookup:

dblCr = DLookup("Sum([Credit:])", "Activities", [red]"[Name] = '" & Me![Name] & "'"[/red]) + DLookup("Sum([Total Credit:])", "Stats", [red]"[Name] = '" & Me![Name] & "'"[/red])

But... to what table is your form bound? Why not create a query that pulls the data from both Credit and Total Credit, then use that as the record source for your form? Then you wouldn't need DLookup at all, just a simple calculated field on your form.

HTH,

Ken S.

 

[lvengineer]

Thanks Eupher....works great and good suggestions. Actually, as a novice, I make a lot of poor choices in field names, etc. I have renamed the fields and unbound the form as well.
Normally, I would use a query but in this case, because I am retrieving data from multiple tables, I decided on the DLookup function. I just couldn't figure out the criteria (where) method/s.
Thanks once again for your quick and great response.

Bruce

"Hardware engineers make terrible programmers". BW.

 

[PHV]

Have a look at the DSum and Nz functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886