ansaurus

Question

DLookup in Access not running until textBox clicked on in Form

Answer 1

+1 A:

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

Remou 2009-09-25 08:45:05

thank-you for this ... i got it working with Recalc after much confusing playing around trying to get the recordSet method to work -will be posting the problem i ran into as a separate question (http://stackoverflow.com/questions/1479640/vba-access-recordset-per-form-record-problem), thanks again!

Ben Roberts 2009-09-25 21:19:58

I've gone back to adding in a load of DSums given the complexity of any other solution, the only problem i have with this approach is that only the text boxes that are currently on-screen are displayed once the Recalc runs, the others have been calc-d but are only displayed once i click into that text-box. This form is ~2x a 1024px LCD wide. I tried playing around with multiple REcalcs and moving the focus in between which nearly works but seems really kludgy - is there a better way?

Ben Roberts 2009-09-30 03:10:55

above should be another question i guess - http://stackoverflow.com/questions/1496072/after-me-recalc-in-access-off-screen-values-are-not-displayed

Ben Roberts 2009-09-30 04:07:06

With a continuous form, any unbound control that depends on the current record for data will display the result for the current record on all rows of the form - all unbound controls will display the same data, but it may change according to the current record.

Remou 2009-09-30 09:34:17

ansaurus

tags:

views:

answers:

DLookup in Access not running until textBox clicked on in Form

related questions