Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cell equal cell (MS Outlook 98 Script)

    I have two cells in MS Access that I need to compare. Dept1 and Dept2 are two different cells that might contain the same or different names. If the names are the same it will call up a routine further down and continue the process. Otherwise, if they are not the same it call ups a routine that will generate a message/email. I am not sure how to compare the cells and continue.

    If Dept1 = Dept2
    Continue - the names are the same so it continues to a different section.
    Else
    The names do not match so it calls up a routine that will send out a message

    THis is what I had before where I only checked on one cell:

    'Set nms=application.getnamespace("mapi")
    'vrUser = nms.currentuser
    ''Msgbox "Name: " &vrUser
    Set Rst = MyDB.OpenRecordset("select * from SystemInfo where Username = '" & vrUser & RequestName & "'")

    If Rst.EOF = True And Rst.BOF = True Then
    Msgbox("Your are not authorized to send this request. Forward this to your Manager")
    Else
    update1
    UserProperties.Find("txtstatus").Value="Dept Manager Approved"
    update2
    Man1

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    By "cells", do you mean fields in the recordset opened as rst?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    Sorry, yes. Wrong choice of words, thinking in Excel right now.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    You could use something like this:

    If Rst.BOF = True Then
    ' No record found
    ...
    ElseIf Rst!Dept1 = Rst!Dept2 Then
    ' Fields are equal, so do something
    ...
    Else
    ' Do something else
    ...
    End If

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    Do you think this will do it?

    '-=-=-=-=-=-=-=-=-=-=-=-=-
    Sub CBMan1_Click()
    Dim Dbe
    Dim MyDB
    Dim Rst
    Dim Rsm
    'On error resume next
    Set Dbe = Application.CreateObject("DAO.DBEngine.36")
    If Err.Number <> 0 Then
    MsgBox Err.Description & "--- Some functions may not work correctly" _
    & Chr(13) & "Please make sure that DAO 3.6 is installed on this machine"
    Exit sub
    End If
    Set MyDB = Dbe.Workspaces(0).OpenDatabase("Newsecinventory.md b")
    'RequestName = UserProperties.Find("dept1").Value
    'Set Rst = MyDB.OpenRecordset("select * from task where Username = '" & RequestName & "'")
    If Rst.EOF = True Then
    'No Record Found
    msgbox("No Record Found")
    ElseIF Rst!Dept1 = Rst!Dept2 Then
    update1
    update1a
    UserProperties.Find("txtstatus").Value="Dept Manager Approved"
    update2
    Man1
    Else
    Msgbox("Your are not authorized to send this request. Forward this to your Manager")
    cmdBad 'Create message that Dept names are not the same
    End if
    End Sub
    '-=-=-=-=-=-=-=-

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    Looks mostly OK. You will have to uncomment the line

    'Set Rst = MyDB.OpenRecordset(...)

    And if you call MsgBox as a procedure, not as a function, you don't need parentheses around the message string:

    MsgBox "No Record Found"

    and similar for the last MsgBox statement.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    At line "ElseIF Rst!Dept1 = Rst!Dept2 Then", I keep getting "Expected Then" at end of line xx

    If Rst.EOF = True Then
    'No Record Found
    msgbox No Record Found
    ElseIF Rst!Dept1 = Rst!Dept2 Then
    update1
    update1a


    BTW, Thank you for your help. I have taken 1 class of VB a few years ago and now I am expected to write code. Will learning the hard way could be best. LOL

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    msgbox No Record Found

    should have quotes before and after the message text:

    MsgBox "No record found"

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    Can not get past the error statement - "Expect Then" at this line = Elseif Rst!Dept1 = Rst!Dept2 Then


    '-=-=-=-=-=-=-=-=-=-=-=-=-
    Sub CBMan1_Click()
    Dim Dbe
    Dim MyDB
    Dim Rst
    Dim Rsm
    'On error resume next
    Set Dbe = Application.CreateObject("DAO.DBEngine.36")
    If Err.Number <> 0 Then
    MsgBox Err.Description & "--- Some functions may not work correctly" _
    & Chr(13) & "Please make sure that DAO 3.6 is installed on this machine"
    Exit sub
    End If
    Set MyDB = Dbe.Workspaces(0).OpenDatabase("tabsproddatabaseed garNewsecsecurity2.mdb")
    RequestName = UserProperties.Find("dept1").Value
    Set Rst = MyDB.OpenRecordset("select * from task where Username = '" & RequestName & "'")
    If Rst.EOF = True Then
    'No Record Found
    msgbox "No Record Found"
    Elseif Rst!Dept1 = Rst!Dept2 Then
    UserProperties.Find("txtstatus").Value = "Dept Manager Approved"
    update2
    Man1
    Else
    Msgbox("Your are not authorized to send this request. Forward this to your Manager")
    cmdBad 'Create message that Dept names are not the same
    End if
    End Sub
    '-=-=-=-=-=-=-=-

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cell equal cell (MS Outlook 98 Script)

    I suspect that something has gone wrong in that line. This happens sometimes. The code as posted by you doesn't have this problem. I would delete the problem line, and either type it in anew, or copy the code from your post back into the Visual Basic Editor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •