CODEWITHSUNDEEP
ms-accessvbams-access-2010
CodeMed
CodeMed

Reputation: 9191

vba function to return record with most recent date. given another criteria

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 CommunicationTable where the ClientNumber is the same as the ClientNumber associated with the current record in the form. The code below references the correct ClientNumber, but I am not sure how to get the most recent date. I am concerned that DMax might not be the appropriate function for getting the most recent date. How should I change the following to get the most recent date? 

=DMax("DateOfCommunication","[CommunicationTable]","[ClientNumber]=  "  
& [Forms]![Main]![NavigationSubform].[Form]![ClientNumber] & "")

I realize I should also post the larger function in which the above function is nested: 

=DLookUp("[Level]","[CommunicationTable]","DateOfCommunication= "  
& DMax("DateOfCommunication","[CommunicationTable]","[ClientNumber]=  "  
& [Forms]![Main]![NavigationSubform].[Form]![ClientNumber] & ""))

Also, the form itself is bound to CommunicationTable. This VBA function is in the DefaultValue 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.

EDIT:

I have uploaded a stripped down copy of the database which reproduces the problem at this file sharing site.

To locate the code: 

1.) Open the CommunicationEntryForm and  
2.) open the AfterUpdate() event procedure for the ClientNumber field.

Next, to reproduce the problem: 

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.

Can someone show me how to fix this problem?

Upvotes: 0

Views: 4059

Answers (2)

craig.white
craig.white

Reputation: 427

@CodeMed - I did download the database but found you have issues other than what you are describing- such as when you 'add' a new communication you simply overwrite an existing record. I managed to get the result you were looking for, but it just changes the 3 records around. Does your non-sample program actually have the ability to create and add new records? As it is, I just changed your existing code to this:

Private Sub cmdNewCommForm_Click()
Dim cNum As Long
Dim strSQL As String
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
cNum = Forms!Main!NavigationSubform.Form!ClientNumber
strSQL = "SELECT Top 1 Co.Level AS MaxOfLevel " & _
         "FROM CommunicationTable co Where Co.ClientNumber = " & cNum
Set rs = db.OpenRecordset(strSQL)
Forms!Main!NavigationSubform.Form!NavigationSubform.SourceObject = "CommunicationEntryForm"
Forms!Main!NavigationSubform.Form!NavigationSubform!ClientNumber = cNum
Forms!Main!NavigationSubform.Form!NavigationSubform!DateOfCommunication = Date
If rs.RecordCount > 0 Then
    Forms!Main!NavigationSubform.Form!NavigationSubform!Level = rs!MaxOfLevel
Else
    Forms!Main!NavigationSubform.Form!NavigationSubform!Level = 0
End If

Set rs = Nothing
Set db = Nothing

End Sub

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8402

What I would do is first grab the date by doing something like:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset("SELECT Top 1 DateOfCommunication FROM CommunicationTable WHERE ClientNumber= " & [Forms]![Main]![NavigationSubform].[Form]![ClientNumber] & " ORDER BY DateOfCommunication DESC")

This will get the most recent date. Then, in your above VBA, you can just stick rec(0) in where your calculation was:

Me.MyDateField = DLookUp("[Level]","[CommunicationTable]","DateOfCommunication= #" & rec(0) & "#")

Substitute "MyDateField" with whatever that name of your date field actually is.

I'm pretty sure you need the pound signs (or "hashtags" as the kids call them today...) in order for Access to do the calculation on a date value.

Upvotes: 1

Related Questions