Setting Default Value Based on Previous Record

[mikeTTI]

I have a subform that is dispalyed as a datasheet.

I am trying to make two combo boxes on the datasheet use the value of the previous record as their DefaultValue for new records.

To do this I have created a functions and LostFocus event for each combo box: -

This is working for one combo box, which uses: -

Public Function GroDef() As String

GroDef = Forms![Levy Receipts]![sbfLevyReceiptsDetail].Form![Grower]

End Function

Then

Private Sub Grower_LostFocus()
Me!Grower.DefaultValue = GroDef()
End Sub

The other does not work (#Name? appears as the default value), and uses: -

Public Function SppDef() As String

SppDef = Forms![Levy Receipts]![sbfLevyReceiptsDetail].Form![Species]

End Function

Then

Private Sub Species_LostFocus()
Me!Species.DefaultValue = SppDef()
End Sub

I think the SppDef() function is working because I can display its value in a text box. I just can't get the combo box to accept a default value from it.

The field underlying this combo box is text, but so is the one that does work.

The only difference i can think of is the problem Combo box does not have a primary key associated with it.

Any suggestions?

 

[mikeTTI]

Update: I have gotten around this by using DLookUp instead (much simpler).