How to prevent DLookup / DSum displaying previous record values in a new record ?
Posted on 2015-01-03
Hello, I have a form (F_ProjectInfo) on which some values are displayed in unbound text boxes using VBA Dlookup and DSum statements, as follows:
Private Sub Form_Current()
Textbox1 = DSum("Payment", "T_ProjectPayments", "ProjectID = " & [Forms]![F_ProjectInfo]![ProjectID])
Textbox2 = DLookup("ProjectType", "T_ProjectType", "ProjectID = " & [Forms]![F_ProjectInfo]![ProjectID])
Two problems occur when the form moves onto a new record:
1) an error message appears due to the new record not yet having a ProjectID (an auto number), hence causing problems for the DSum and DLookup statements.
2) the unbound text boxes display the DSum / DLookup values from the previous record.
I solved 1) by adding “On Error Resume Next” at the top of the code, but I just cannot seem to find a solution for problem 2). Does anyone have any ideas ?
Thank you in advance for any help.