Display 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?

 

[TheAceMan1]

How are ya sheusz . . .

The [blue]structure[/blue] of what you've posted is very vague. Are you talking a form with subform, two independent forms, is there a relationship between the two? . . . What?

As a little heads-up for you, I see you've started the exact same thread in the forum700 forum. This is against forum policy and continued posting this way will cause your threads to be [red]red flagged[/red], [red]even removed.[/red] In my own words I call it [blue]Gang Banging The Forums![/blue] Just bear this in mind for future reference (don't want you to start off on the wrong foot).

Many of us have threads at the bottom of our posts that you should take the time to go over.

[blue]Your Thoughts! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[sheusz]

Hi AceMan1,
Thanks for the reply and the heads up on etiquette. I will take on you advice.

The form CustomerInfo does not have a sub-form. All CustomerInfo is in one table and service history is in a separate table. The two are linked by CustomerNumber (not an AutoNumber).

I only want to show the date of the last service taken from the service history table on the CustomerInfo form. I don't really want to add a sub form to the CustomerInfo form.

 

[randy700]

Two methods come to mind.

First, base your form on a query that pulls data from both tables. The field you want would be obtained using the DMax function.

Second, you could use a DLookup command as the RecordSource of the text box on your form.


Randy

 

[TheAceMan1]

sheusz . . .

In form design view, set the [blue]locked[/blue] property of the textbox to [blue]Yes[/blue] to prevent user editing.

Then copy/paste the following to the forms [blue]On Current[/blue] event. This is just a first shot, as [blue]field names[/blue] are not known and you'll have to go over them all and replace as necessary. [blue]CommonFieldName[/blue] should exist is both tables and perferably [blue]indexed[/blue]:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT TOP 1 [purple][b]LastServiceFieldName[/b][/purple] " & _
      "FROM ServiceHistory " & _
      "WHERE [[purple][b]CommonFieldName[/b][/purple]]=Forms!CustomerInfo![purple][b]CommonFieldName[/b][/purple]" & _
      "ORDER BY [[purple][b]LastServiceFieldName[/b][/purple]] DESC;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Me![purple][b]TextboxName[/b][/purple] = Null
   Else
      Me![purple][b]TextboxName[/b][/purple] = rst![purple][b]LastServiceFieldName[/b][/purple]
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

Let me know how ya make out . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[sheusz]

Hi AceMan

Thanks for that. I'll give that a go.