View Single Post
  #9 (permalink)  
Old January 17th, 2008, 02:00 PM
mmcdonal mmcdonal is offline
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Okay, so the way I see it, is you have a Table A on the One side of the relationship, and a Table B on the Many side, so there are many instances of Service Dates in Table B for an item in Table A, and you want to record the most recent service date in Table A every time someone schedules service in Table B. Or the latest date, for example if the user put a servicedate of 01/31/2008 in one related record and 02/01/2008 in another, 02/01/2008 would be entered in Table A for that item.

If you are then marking each recently updated record as the user inserts a new ServiceDate in Table B, and again, with multi-users this could be problematic but we can fix that, I would do this on the On Close event of the continuous form. Assuming the check boxes have been properly checked. I am also assuming local tables:

Dim rsA As ADODB.Recordset
Dim rsB As ADODB.Recordset
Dim sSQLA As String, sSQLB As String
Dim iPK As Integer

'Open a recordset on Table A first:
Set rsA = New ADODB.Recordset
rsA.Open sSQLA, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Take first PK
Do Until rsA.EOF
   iPK = rsA("PrimaryKeyFieldName")
   sSQLB = "SELECT TOP 1 FKFieldName, ServiceDate, Update FROM TableB WHERE [FKFieldName] = " & iPK & " AND [Update] <> 0 ORDER BY ServiceDate DESC"
   Set rsB = New ADODB.Recordset
   rsB.Open sSQLB, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
      If rsB.RecordCount > 0 Then
           rsA("LastServiceDate") = rsB("ServiceDate")
      End If



This will loop through all the records and take for each unique item in TableA, the latest service date from TableB, regardless of who entered it.

Then do this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUPDATE_TableB_SetUpdateto_0_ALL"
DoCmd.SetWarnings True

This will reset all the Update fields to 0 so that processing runs better on subsequent runs. You need to create that Update query and then call it like this.

Then open the next form and close this one. If the main form for Table A opens, it will have the most current LastServiceDate information.

Did that work for you?


Look it up at:
Reply With Quote