Solved Finds records using openrecordset (1 Viewer)

[Pop_Access]

Hi All,
I have a group of employee’s ID numbers in a textbox with “,” between each ID number. I want to open a recordset to find the matching employee name and Email from (tbl_student) table.

for example,
in the textbox, I have (358,258,4025), I want to find the employee name and email for each of them from table (tbl_student).

Thank you

 

[arnelgp]

to open a recordset:

dim db as DAO.Database
dim rs As DAO.Recordset

Set db=Currentdb
Set rs = db.OpenRecordSet("select [employee name], [email] from tbl_student where ID In (" & Me!textboxName & ");", dbOpenSnapshot, dbReadOnly)
With rs
    If Not (.Bof And .Eof) Then
        .MoveFirst
    End If
    Do Until .EOF
        ' your code to process each records, goes here'
        '
        '
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
set db = Nothing

 

[Ranman256]

make a form to show all records, then just filter the results via a text box. No need to write code to cycle thru a recordset.

sub txtBox_afterupdate()
if isNull(txtBox) then
   me.filterOn = false
else
   me.filter = "[field]='" & txtBox & "'"
   me.filterOn = true
endif
end sub

 

[Pat Hartman]

I have a group of employee’s ID numbers in a label with “,” between each ID number.

That's bizarre. I don't even want to know how a group of ID numbers ended up in a label's Caption. It is so bizarre that @Ranman256 AND @arnelgp both assumed you meant a textbox.

If you really mean the ID's are in a label, then you need to get them by referencing the label's Caption property.

Me.Filter = "field IN(" & Me.SomeLabel.Caption & ")"

However, if the ID's are not numeric, you need to unpack them first using split. Then you need to rebuild the "in()" string with the text fields enclosed in double quotes and separated by commas.

 

[GPGeorge]

"I don't even want to know how a group of ID numbers ended up in a label's Caption."

I can think of a way to make that happen, but it would require some clever coding .
But you are right. I assumed when I started reading this thread it was a misstatement for "text Box used as a label".

 

[Pop_Access]

That's bizarre. I don't even want to know how a group of ID numbers ended up in a label's Caption. It is so bizarre that @Ranman256 AND @arnelgp both assumed you meant a textbox.

If you really mean the ID's are in a label, then you need to get them by referencing the label's Caption property.

Me.Filter = "field IN(" & Me.SomeLabel.Caption & ")"

However, if the ID's are not numeric, you need to unpack them first using split. Then you need to rebuild the "in()" string with the text fields enclosed in double quotes and separated by commas.

thank you for your comment; you are right. the IDs in a textbox NOT in a label. I edited the post

 

[Pop_Access]

to open a recordset:

dim db as DAO.Database
dim rs As DAO.Recordset

Set db=Currentdb
Set rs = db.OpenRecordSet("select [employee name], [email] from tbl_student where ID In (" & Me!textboxName & ");", dbOpenSnapshot, dbReadOnly)
With rs
    If Not (.Bof And .Eof) Then
        .MoveFirst
    End If
    Do Until .EOF
        ' your code to process each records, goes here'
        '
        '
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
set db = Nothing

I have already used your idea before, but (Access) identify the employee Name and Email for the first ID and then iterates the same number (first ID) infinity with different course name.

This is my code:-

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT tbl_Student.stuID, tbl_Student.stuname, tbl_Student.Email, tbl_CourseList.corsname " & _
                          " FROM tbl_CourseList INNER JOIN (tbl_Session INNER JOIN (tbl_Student INNER JOIN tbl_AtndCors ON tbl_Student.stuID = tbl_AtndCors.stuID) " & _
                          " ON tbl_Session.SessionID = tbl_AtndCors.SessionID) ON tbl_CourseList.corsID = tbl_Session.corsID " & _
                          " WHERE tbl_Student.stuID in (" & Me!txtSelected & ");", dbOpenSnapshot, dbReadOnly)

    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
    End If
    Do Until rs.EOF
       ‘Get Outlook if it's running
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
                 'Outlook wasn't running, start it from code
 Set oOutlookApp = CreateObject("Outlook.Application")
 bStarted = True
End If
                 'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(olMailItem)

                 'Set the recipient for the new email

   oItem.To = rs![Email]
   oItem.Subject = rs![corsname] & " Certificate Will be Expired" 
     oItem.Body = "Dear " & rs!stuname & vbCr & _
     "Thanks for participating in our program”
   oItem.Display   
   rs.MoveNext
Loop
 rs.Close
Set rs = Nothing
Set db = Nothing
Set oItem = Nothing

 

[Pop_Access]

make a form to show all records, then just filter the results via a text box. No need to write code to cycle thru a recordset.

sub txtBox_afterupdate()
if isNull(txtBox) then
   me.filterOn = false
else
   me.filter = "[field]='" & txtBox & "'"
   me.filterOn = true
endif
end sub

Thank you.
But I need to send an Email for each of them.

 

[arnelgp]

if you can share your db here, so we can see which parts need to tweak on your string SQL.
we don't know what the "other" tables hold and if they can really be joined.

 

[Pat Hartman]

Let's go back to how the ids got into the control. If you want to send emails, they should come out of the loop that put the IDs in the control. You should not be creating a second loop.

 

[Pop_Access]

Let's go back to how the ids got into the control. If you want to send emails, they should come out of the loop that put the IDs in the control. You should not be creating a second loop.

Thanks for All;

@Pat Hartman
You will find the answer in the following post:
https://www.access-programmers.co.u...box-on-a-continuous-form.315623/#post-1741806
at that time, the DB allow me to send ONE Email to the selected employees, now I want to send a separate Email for each.

@arnelgp
Thank you again
instead of using the recordset method, I used your idea, by adding an invisible textbox and saving the selected employee data in it.
So the solution for this problem as below:-

Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim strCriteria As String

On Error Resume Next
   
     strCriteria = Me.txtSelected2 & ""
      If Len(strCriteria) > 0 Then
         If Right(strCriteria, 1) = "," Then _
            strCriteria = Left(strCriteria, Len(strCriteria) - 1)

         End If
       
         Dim str1() As String
         str1() = Split(strCriteria, ",")
       
         For i = LBound(str1, 1) To UBound(str1, 1)
            Dim str2() As String
            str2 = Split(str1(i), "#")
           
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
   
                'Outlook wasn't running, start it from code
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
End If

                'Create a new mailitem
            Set oItem = oOutlookApp.CreateItem(olMailItem)
           
            oItem.To = str2(3)
            oItem.Subject = str2(2) & " Certificate Will be Expired"
               
            oItem.Body = "Dear " & str2(1) & vbCr & _
                             vbCr & _
                            "Thanks for participating in our Program" & vbCr & _
                            "Please be advised that your certificate " & vbCr & _
                            "Will be expired on   " & str2(4) & vbCr & _
           
            oItem.Display
            oItem.MoveNext
           
         Next i

Set oItem = Nothing

'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub