Pulling a field from a table not linked to a form

[dbowens]

I have a contributor tracking table that is linked to a form of the same name. I created a make table from a query that calculates the total to date for each contributor (based on their contributor ID in the tracking table). I want to place this sum to date, in read only mode, on each contribution record for each contributor in the tracking table and on each master record in another table with the contact information for each contributor.

The contact table is in the one and the contributor tracking table is the many. If this isn't clear, I can upload the database. I essentially want to link a field from one table to a form with a different table source. The sum to date should only show for the record with a matching contributor ID.

Thanks!

 

[plog]

I created a make table from a query that calculates the total to date

I stopped there. MAKE TABLE queries are a sign of a poor structure. They're use is a hack around that poor structure and in your case that's true. You shouldn't store calculated values, you should....calculate them. Turn your query from a MAKE TABLE to a SELECT query and then reference that query when you need a total.

To make that value appear on a form you use an unbound control and have its data source be a DLookup(http://www.techonthenet.com/access/functions/domain/dlookup.php) into that total query to retrive the data you want.

 

[dbowens]

Thanks. I am not sure how to use the dlookup function as I have never used it before. Could you provide more info (I checked out the instructions, but I am still a little unsure).

 

[plog]

The Dlookup can take 3 inputs--the field you want to look up, the table/query that field is in, and then criteria which is optional. So suppose you had this table of data:

Users
UserID, FirstName, LastName, JoinDate
3, Steve, Jones, 12/2/2012
4, Sally, Smith, 1/3/2014
7, David, King, 4/17/2008

If you used a Dlookup like this:

=Dlookup("[LastName], "Users", "[UserID]=7")

In english, the dlookup was told to retrieve the value in the LastName field of the Users table where the UserID was 7. That resolves to "King".

 

[namliam]

=Dlookup("[LastName]", "Users", "[UserID]=7")

Fixed a missing quote, just want to add that DLookup hrmph ok if you must.
However DLookupS is a BAD thing.

You need 1 sure possible, you need multiple, you either again have a structuring problem or you should use a query.

 

[dbowens]

This seems to complex. I cannot get the Dlookup to work in the form. I need it to display the total to date for multiple contributors not just one. These are the formulas that I used.

=DLookUp("[SumOfAmountofContribution]","ContributontoDate Query","[ContributorID] = Forms![KairosContributorTracker]![ContributorID]")

I attempted to use the expression above to get the unbound field to pull the sum to date from the query based on a contributor ID match

=Dlookup("[SumOfAmountofContribution]", "ContributiontoDateQuery", "[Contributor ID]=4")

While the unbound field excepts this expression, when I go into form view, nothing appears in the unbound field even though there is a match on contributor ID. Maybe I should just pull the sum to dates as needed instead of attempting to make them appear on the contributors individual profiles.

 

[plog]

Can you post a screenshot of ContributionDateQuery?

 

[dbowens]

Here is a screen shot of the query in design view and datasheet

 

[plog]

Your Dlookup is referencing fields and a query that doesn't exist. You don't have spaces in your Dlookup call, but you do in your query.

 

[namliam]

Thankfully we all use and follow a proper naming convention.

http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=naming+convention