title: vba function to return record with most recent date. given another criteria
body: <p>I need to set the default value for a textbox in an ms access 2010 form.  The default value needs to be the most recent date in <code>CommunicationTable</code> where the <code>ClientNumber</code> is the same as the <code>ClientNumber</code> associated with the current record in the form.  The code below references the correct <code>ClientNumber</code>, but I am not sure how to get the most recent date.  I am concerned that <code>DMax</code> might not be the appropriate function for getting the most recent date.  How should I change the following to get the most recent date?  </p>
  
  <pre><code>=DMax("DateOfCommunication","[CommunicationTable]","[ClientNumber]=  "  
  &amp; [Forms]![Main]![NavigationSubform].[Form]![ClientNumber] &amp; "")  
  </code></pre>
  
  <p>I realize I should also post the larger function in which the above function is nested:  </p>
  
  <pre><code>=DLookUp("[Level]","[CommunicationTable]","DateOfCommunication= "  
  &amp; DMax("DateOfCommunication","[CommunicationTable]","[ClientNumber]=  "  
  &amp; [Forms]![Main]![NavigationSubform].[Form]![ClientNumber] &amp; ""))  
  </code></pre>
  
  <p>Also, the form itself is bound to <code>CommunicationTable</code>.  This VBA function is in the <code>DefaultValue</code> dialog box, which I get into via the property sheet for the text box.  So I am not sure that creating a sql query will work in this case.  </p>
  
  <hr>
  
  <h2>EDIT:</h2>
  
  <p>I have uploaded a stripped down copy of the database which reproduces the problem <a href="http://jmp.sh/v/6X79HFZXoBMMlfU2c6x3" rel="nofollow"> at this file sharing site</a>.  </p>
  
  <p>To locate the code:  </p>
  
  <pre><code>1.) Open the CommunicationEntryForm and  
  2.) open the AfterUpdate() event procedure for the ClientNumber field.  
  </code></pre>
  
  <p>Next, to reproduce the problem:  </p>
  
  <pre><code>1.) close `CommunicationEntryForm`    
  2.) In the Main form(which should already be open), click the View tab to open  
      the most recent CommunicationForm for any Client you want.  Note the Level  
      number for that Communication.  
  3.) Click on the Communication tab.  This will leave the form and show a list  
      of CommunicationForms for that Client.  
  4.) Click the Create New Form button.  This will open up CommunicationEntryForm.  
      The value for Level should be the same as the value you noted in step 1 above.  
      The problem is that this is blank.  
  </code></pre>
  
  <p>Can someone show me how to fix this problem?  </p>