"Dean Collins" <(E-Mail Removed)> wrote in
news:099901c380f4$876ba460$(E-Mail Removed):
> Is there an expression that can be used to populate each
> field using a Dept and Year criteria in the control
> source of each text box?
=DLookUp("[Number]","MyTable","Dept=""ABT"" and [Year]=2000")
etc... By the way, Number and Year are really inadvisable names for fields
because, although being legal, they are also used as SQL key words and may
cause bugs that can be very hard to track.
> If this is not possible then
> can some one show me an alternative way to achieve the
> same results.
>
It is possible, but it may be more efficient to intercept the On Current
event and get all the values in one go:
Private Sub Form_Current()
' editors note: this is untested air code: treat with caution!!
' objects
Dim db as Database
Dim strSQL as String
Dim rs as Recordset
' create the query: note the projection and ordering
strSQL = "SELECT [Year], [Number] FROM MyTable " & _
"WHERE Dept = """ & Me!txtDept & """ " & _
" AND [Year] IN "2000,2001,2002,2003) " & _
"ORDER BY [Year];"
' open the query: forward snapshots are fastest
Set db=CurrentDB()
Set rs=db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
' read the records; omit loop if there weren't any
Do While Not rs.EOF
' put the data in the controls
' I am assuming the controls are txt2000, txt2001, etc.
' you may want to put in some formatting here too
Me.Controls("txt" & rs![Year]).Value = rs![Number]
' get the next record
rs.MoveNext
Loop
' all done: tidy up
rs.Close
End Sub
Hope that makes some kind of sense
Tim F
|
Similar Threads
