controlsource in field of continuous sub-form

  • Thread starter Thread starter John Pritchard
  • Start date Start date
J

John Pritchard

Hi,

I have a continuous sub form and (following help from
GreySky - thanks mate) can now populate a description
field successfully by having the following in the
controlsource of the description field:-

=DLookUp("TitleName","TitleCodes","AcctUnit=Forms!AddPDS!
PDSLinSub!AcctUnit")

and this in the exit event of the AcctUnit field (i.e. the
field the above is a descrition of) :-

Private Sub AcctUnit_Exit(Cancel As Integer)

Me.TitleName.ControlSource =
Me.TitleName.ControlSource

End Sub

This works but is a bit clunky. Without the above line
(which seems pointless on the face of it) the descriptions
soon get in a mess.

So - Can I change the above to make it more efficient -
like hardcoding the Dlookup into the AcctUnit_exit routine?

If so please say how as I'm quite new to this stuff

Kind Regards

John P.
 
You can use a recordset object. It eould look something
like this:
put this in your afterupdate event for the field which is
used to select the record desired.

dim db as database
dim rst as recordset
dim sqlstr as string

set db=currentdb

sqlstr ="Select yourTable.field1, yourtable.field2
(...etc...) from yourtable where yourtable.fieldname =
somecriteria";

set rst=(sqlstr, dbopensnapshot)

me!field1=rst!field1

etc...

'the following releases memory assigned to the db object.

db.close
set db=nothing

I hope this helps!

Kevin
 
Yep it is a combo box and I already use rowsourec so this
seems like a good solution. I'll give it a try - Hope it
works when the users just type in the value !

Thanks for the idea !
 
Thanks I'll give this a try and the other solution - also
from a Kevin. I'm 3 months into VB with both Excel (think
I've pretty much got the idea of that now) and Access. I
know SQL already BUT what are the other important
concepts/areas given I can make things work OK'ish already?

Thanks again John P.
 
Back
Top