Populating a control on a form

[Rafi]

I have a switchboard on which I have a textbox CurrQ which I need to update
on open with a value as defined by

Select Max(tbl_IDD_Revenue.Qtr) As Qtr from Tbl_Idd_Revenue.

I have tried the On Open event of the form with the following code

stSQL = "Select Max(tbl_IDD_Revenue.Qtr) As Qtr from Tbl_Idd_Revenue"
Set db = CurrentDb()
Set rst = db.OpenRecordset(stSQL)
With rst
If Not (.EOF And .BOF) Then
Qtr = rst.Fields("Qtr")
End If
.Close
End With

me.CurrQ = Qtr

 

[Klatuu]

You could do this in the Control Source of your text box with a DLookup:
=DMax("[Qtr]", "tbl_IDD_Revenue")

 

[Brendan Reynolds]

It's difficult to be sure, as you didn't say what happens or fails to happen
when you try to use this code. But a possible problem may be the event
you're trying to use. The Open event may be too soon in the lifetime of the
form. Try the Load event instead.