DLookup in Access not running until textBox clicked on in Form

Question

I'm setting 12 TextBox ControlSources in my Form from VBA using the following :

...
Me.Oct.ControlSource = "=DSum('GBPValue', 'MF YTD Actual Income & Adret', 'Month=10 AND Org_Type=[Key]')"
Me.Nov.ControlSource = "=DSum('GBPValue', 'MF YTD Actual Income & Adret', 'Month=11 AND Org_Type=[Key]')"
...

[Key] is the name of a textbox in the form

When the form loads up i get some odd behavior -

all of the summary form text boxes are blank as are all the dlookup text boxes
if i then click on one of the text boxes that has a dlookup control source assigned the summary text boxes for the other columns start to populate with 0's and #Num etc. and the dlookup runs and displays the expected numbers
once i've clicked on all the dlookup fields the summary numbers calc properly.

In the final version of this the query will be re-written after user clicks from the VBA so ... is this a sensible way to get the form to re-query the DB and, if so, how can i make the DLookups run/display automatically so that everything displays immediately on form load?

Fionnuala Fionnuala · Accepted Answer · 2009-09-25T08:45:05

You are probably looking for Recalc (Me.Recalc). However, I suggest you use a recordset, rather than DlookUp, and the Current event for the form:

Dim rs As DAO.Recordset 'Needs MS DAO 3.x library
Dim db As Database
Dim strSQL As String

Set db = CurrentDb()

'Guessing that key is a form value
'Note that Month is a reserved word

strSQL = "SELECT [Month], Sum(GBPValue) As SumVal " _
       & "FROM [MF YTD Actual Income & Adret] " _
       & "WHERE Org_Type= " & Me.[Key]  
       & " GROUP BY [Month]" 

Set rs=db.OpenRecordset(strSQL)

'You can probably use a Do While Loop, consider 
'naming the controls, eg, Month10

rs.FindFirst "[Month]=10" 
Me.Oct = rs!SumVal

'and so on

DLookup in Access not running until textBox clicked on in Form

1 Answers