Display data from last record entered on form.

[sheusz]

Hi All,
This is my first post so I haope that someone may be able to assist me.

I have a service records database containing a number of tables, two of which are CustomerInfo and ServiceHistory. I have a form that displays all the customer information but I also want to display (& not edit) the date of the last service performed for that customer on the customer information form.

Does anyone know how I can do this?

 

[dhookom]

The easiest method would be to use a text box with DMax() in the control source. Since you haven't described any table or field names or relations, I can only guess the syntax would be something like:
=DMax("[date of the last service performed]","ServiceHistory","CustomerID=" & CustomerID)

If you can't figure this out, please come back with names and relationships.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[sheusz]

Hi Duane,

Thanks for your reply.

I've tried various permutations of the syntax but can't seem to get it right, so here are the details.

The table, Customers, contains the field CustomerID.

The table, xServices, contains the fields CustomerID and Service Date as well as other descriptive fields relating to services.

The xServices table is linked to the Customers table with the CustomerID field. This is a one-to-many relationship (one CustomerID many Services).

Thanks in advance for your help.

 

[PHV]

ControlSource of your textbox:
=DMax("[Service Date]", "xServices", "CustomerID=" & [CustomerID])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sheusz]

Thanks for that. I'll give it a try

 

[sheusz]

PHV,

I have tried your suggestion but the field displays the following error; #Error

I have even tried DLookUp but get the same error. =DLookUp("[Service Date]","xServices","CustomerID=" & [CustomerID])

Is there a problem with how I have my xServices table set up? What about the space in Service Date? Though not desirable I believe that it is OK? (Legacy of an earlier programmer)

Here are some details about the xServices table

Data Type for CustomerID = TEXT
Row Source Type for CustomerID = Table/Query

Row Source for CustomerID = SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;

Data Type for Service Date = Date/Time


This "simple" thing is starting to drive me mad! I really do appreciate all your assistance and once again thank you in advance.

 

[PHV]

Data Type for CustomerID = TEXT

=DLookUp("[Service Date]","xServices","CustomerID=[!]'[/!]" & [CustomerID][!] & "'"[/!])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sheusz]

Hi PH,

That works but is not returning the date of the last service. I have sorted the syntax of the DMax command based on your reply and that has given me the desired result.

How do I insert code in my posts as you have done? I'd like to get my posts correct for you guys.

Thanks

 

[PHV]

Oops, sorry for the typo (DLookUp instead of DMax (as I suggested you in my 1st reply))
To get this:

my code here

type this:[ignore]

my code here

[/ignore]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sheusz]

Thanks PH,

You've been a great help. Hopefully I'll be able to help someone out myself in the future.