Hi,

   I want to do the following:

I have a form that has a subform. The form contains employee data, and the subform contains a list of pieces of equipment that the employee has in their possession.

The structure is as follows:

tblEmployee
PK UserID

tblAssets
PK AssetID
txtAssetCategory

tblDetails
PK DetailsID
FK UserID
FK AssetID

I would like to query the database, and if the employee has particular items in their possession, I want to capture all those items in a text string variable. I want it in one variable so that I can put it in an email (working fine) as part of the message.

I would like to query this data so that I can do the following wild cards: Like "Laptop *", and Like "PDA *", etc.

I think I may need a module to gather the data.

What the user will be able to do is if they own these items, a list can be sent by email to a helpdesk showing all the items, but only like this: "User owns 1 Cell Phone, 1 Laptop, 2 PDAs"

Thanks in advance.


mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Okay, once again, I got this before I got a reply here.

What I needed to do was from my asset management database, notify the helpdesk and some other people when an employee was leaving the agency, and provide a list of equipment they had signed out and its serial numbers. Here is how I did it (and yes, I did create a query called "Query1"):

'==========
Private Sub btnNotify_Click()

    Dim stSubject As String
    Dim stName As String
    Dim stSender As String
    Dim stMessage As String
    Dim stHelpDesk As String
    Dim stFinished As String
    Dim rs As ADODB.Recordset
    Dim stSQL As String
    Dim stAsset As String
    Dim stSN As String
    Dim stList As String

    stSQL = "Select * from Query1 Where UserID = " & Me.UserID
    stSubject = "Exiting Employee"
    stSender = "[email protected]"
    stName = Me.FirstName & " " & Me.LastName & " (" & Me.LOGIN_NAME & ")"
    stMessage = "Please retrieve the following items from "
    stHelpDesk = "[email protected];[email protected];mmcd [email protected]"
    stFinished = "The HelpDesk has been notified."

    Set rs = New ADODB.Recordset
    rs.Open stSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    Do Until rs.EOF
        stAsset = rs!AssetCategory
        stSN = rs!SerialNumber
        stList = stList & vbCrLf & stAsset & " (SN:" & stSN & ")"
        rs.MoveNext
    Loop

    Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
    Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

    Const cdoAnonymous = 0 'Do not authenticate
    Const cdoBasic = 1 'basic (clear-text) authentication
    Const cdoNTLM = 2 'NTLM

    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = stSubject
    objMessage.Sender = stSender
    objMessage.To = stHelpDesk
    objMessage.TextBody = stMessage & stName & ":" & vbCrLf & stList

    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    'Name or IP of Remote SMTP Server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.OurAgency.gov"

    'Type of authentication, NONE, Basic (Base64 encoded), NTLM
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

    'Your UserID on the SMTP server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "****"

    'Your password on the SMTP server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "****"

    'Server port (typically 25)
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

    'Use SSL for the connection (False or True)
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

    'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

    objMessage.Configuration.Fields.Update

    '==End remote SMTP server configuration section==

    objMessage.Send

    MsgBox stFinished


End Sub
'==========

This does it all. Thanks for your perusal, and I hope this helps someone else.

mmcdonal