Record Selecting

[Munekita95]

Hello, I have a form in datasheet view, these records are filtered to display
only the records (personnel) that pertain to a particular department. These
filtered records have a Check box, indicating if the personnel is available
or not.

I would like to be able to click a transfer button and get the name of all
the available personnel to be transferred into one textbox field on the form,
to send them an email.

is this possible? and if so, anyone have any idea how to do it?

 

[Anand]

Hello,
You could achieve this by getting all the records with the checkbox status
set to -1 (or 0) in a recordset and getting the names of each one of the
personnel by looping through the recordset. The code would go behind your
transfer button's On Click event.
It would look something like this:
Dim dbs as Database
Dim rst as Recordset
Dim stSQL as String

stSQL = "SELECT tblTable.Name FROM tblTable WHERE tblTable.chkAvailable = -1"
'You can add more criteria to this SQL statement like departments etc. if
you like.
Set dbs = currentdb
Set rst = dbs.openrecordset(stsql)

with rst
if .recordcount>0 then
..movefirst
do until .eof
me.name = me.name & ", " & !name
..movenext
loop
end with

I am assuming that
You have knowledge of adding/writing code for objects in a form
You are trying to get all names of available personnel in a single text box
that is on the same form as your transfer button. This is however, not such a
good idea if you are trying to send emails to each of these personnel. You
would be better of fetching their email IDs using the same method described
above and using those in a SendObject

HTH
Anand

 

[Munekita95]

Thank you Anand, this was very helpful!

Hello,
You could achieve this by getting all the records with the checkbox status
set to -1 (or 0) in a recordset and getting the names of each one of the
personnel by looping through the recordset. The code would go behind your
transfer button's On Click event.
It would look something like this:
Dim dbs as Database
Dim rst as Recordset
Dim stSQL as String

stSQL = "SELECT tblTable.Name FROM tblTable WHERE tblTable.chkAvailable = -1"
'You can add more criteria to this SQL statement like departments etc. if
you like.
Set dbs = currentdb
Set rst = dbs.openrecordset(stsql)

with rst
if .recordcount>0 then
.movefirst
do until .eof
me.name = me.name & ", " & !name
.movenext
loop
end with

I am assuming that
You have knowledge of adding/writing code for objects in a form
You are trying to get all names of available personnel in a single text box
that is on the same form as your transfer button. This is however, not such a
good idea if you are trying to send emails to each of these personnel. You
would be better of fetching their email IDs using the same method described
above and using those in a SendObject

HTH
Anand