default value for textbox in bound form

Question

I have a data entry form in an MS Access 2010 database which is bound to a table called CommunicationTable, which in turn contains a primary key CommunicationNumber, a foreign key ClientNumber, and a field called Level. When the user opens the form, the ClientNumber is automatically populated into a textbox called ClientNumber. I want the Level textbox to show a default value equal to the value that was recorded for Level in the most recent record (highest CommunicationNumber value) for the specified ClientNumber in CommunicationTable. How do I set this up?

Here is the code I am currently using in the Default Value expression builder for the Level textbox in the form's property sheet:

=DLookUp([Level],[CommunicationTable],[CommunicationNumber]=DMax([CommunicationNumber],[CommunicationTable],[ClientNumber]=[Me].[ClientNumber]))

This code is giving an empty value when I load the form with a valid ClientNumber. I thought this might be due to the DMax() function producing the current (blank) row about to be added to the CommunicationTable, but I get the same empty result when I use the following instead:

=DLookUp([Level],[CommunicationTable],[CommunicationNumber]=DMax([CommunicationNumber],[CommunicationTable],[ClientNumber]=[Me].[ClientNumber])-1 )

How can I change this expression so that it gives the most recent value of Level for the specified ClientNumber in CommunicationTable?

EDIT:

I tried the following, but it is giving a #type! error:

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

Note that the ClientNumber textbox in the form is itself populated using the expression:

[Forms]![Main]![NavigationSubform].[Form]![ClientNumber]

Kiril Rusev Kiril Rusev · Accepted Answer · 2013-12-17T15:31:33

As I mentioned in my comment, you have to adjust your query a bit:

=DLookUp("[Level]","[CommunicationTable]","CommunicationNumber= " & DMax("CommunicationNumber","[CommunicationTable]"))

Update:

=DLookup("[Level]", "[CommunicationTable]", "CommunicationNumber= " & DMax("CommunicationNumber", "[CommunicationTable]", "[ClientNumber]=  " & Me.ClientNumber.Value & ""))

default value for textbox in bound form

EDIT:

1 Answers