• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

How to prevent DLookup / DSum displaying previous record values in a new record ?

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])

End Sub

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.
Paul McCabe
Paul McCabe
3 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The error essentially stops your code from moving forward, so you must take steps to prevent that error. IMO, you should do this:

'/ clear the values first:
Textbox1 = ""
Textbox2 = ""
'/ check for a valid ProjectID before moving forward:
If Nz(Me.ProjectID, 0) <> 0 Then
  Textbox1 = DSum("Payment", "T_ProjectPayments", "ProjectID = " & [Forms]![F_ProjectInfo]![ProjectID])
  Textbox2 = DLookup("ProjectType", "T_ProjectType", "ProjectID = " & [Forms]![F_ProjectInfo]![ProjectID])
End If
Rey Obrero (Capricorn1)Commented:

Textbox1 = DSum("Payment", "T_ProjectPayments", "ProjectID = " & Nz([Forms]![F_ProjectInfo]![ProjectID],0))
 Textbox2 = DLookup("ProjectType", "T_ProjectType", "ProjectID = " & Nz([Forms]![F_ProjectInfo]![ProjectID],0))
An alternative method is to simply use the control source properties of the textboxes (and no code).

In the property sheet, set the control source as follows, including the = sign:

 = DSum("Payment", "T_ProjectPayments", "ProjectID = " & [Forms]![F_ProjectInfo]![ProjectID]

Open in new window

This will display the value associated with each record, without needing to navigate from record to record (which is required to trigger the Current Event).  This is particularly useful if you are using the Continuous Forms View.

The New record displays "#Error", which resolves as soon as you start entering data into the new record.  To avoid the #Error, you can use the NZ function shown in Scott's post:

 = DSum("Payment", "T_ProjectPayments", "ProjectID = " & NZ(ProjectID, 0)

Open in new window

(Also note that since the controls are on F_ProjectInfo, you do not need the full form reference - just the field name.)
Paul McCabeAuthor Commented:
Thank you all very much for your suggestions. I opted for Scott's VBA-based solution since I already had the VBA code, but I tried the solution described by Mbizup as well and it worked perfectly. Thank you so much !!
Rey Obrero (Capricorn1)Commented:
did you try the post at http:#a40529150 ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now