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

    IF statement (VBA Script)

    I am having a problem with some script. When a form is first started, I want to have a message displayed/sent and then update the database. After the Form is sent to the next person I want them to update their section of the form and then send it to the next person. My problem is no body ofter the first person should see the message form pop up on their screen.

    Anybody have some ideas where I am going wrong? UPdate A in the attached is where I want to check if they are the 1st or anybody else.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: IF statement (VBA Script)

    The first thing I noticed is that you are creating an Obe variable and then using a different name Dbe. This is unlikely to solve your direct problem though.

    Which message form popup are you talking about? Is it one of the MsgBox lines or the actual outlook form that this code resides in? In either case, I would be getting the code to check whether there is a value in a particular cell which the first person completes and only run the relevant code if this value is blank.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

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

    Re: IF statement (VBA Script)

    The OBE/DBE was a typeo, sorry.

    In UpdateA, If the Value of SATS is Yes, then cmdCOm7 will build the reply message that is display on the screen. As it should do in the first pass as the requestor. In Update 5, the SATS is changed from "Yes", to "Mailed" (I verified this in the database.) Now the Form is sent to the next person for processing. When the Next person/group opens the Form, do their task and gets ready to forward the form to the next group. Its at this stage that Com7 displays again, even if the SATS is not Yes but Mailed in the database.

    If UserProperties.Find("sats").Value = Yes Then
    cmdCom7_click()
    Update5
    Else
    End if
    End Sub
    '-=-=-=-=-=-=-=-=-=-

    '************************************************* ***************
    '* Procedure:
    '* Arguments:
    '* Description: Sends mail to HR Dept. about NEW Employee
    '************************************************* ***************
    Sub cmdCom7_Click()
    Item_Reply = False
    Set MyFolder = Application.GetNameSpace("MAPI").GetDefaultFolder( 6)
    Set MyItem = MyFolder.Items.Add("IPM.Note.Security Awareness Communication")
    MyItem.To = Item.UserProperties.Find("reqby").Value
    MyItem.Subject = "Security Awareness Communication: " & "" & Item.UserProperties.Find("jobnum").Value
    myItem.Body = "Keep this form until Security Awareness is completed. Then send the Date and TIme of person to Data Security."
    MyItem.Display
    End Sub
    '-=-=-=-=-=-=-=-=-=-

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: IF statement (VBA Script)

    I think you are getting a little confused with data types. I would expect the value of UserProperties.Find("sats").Value to be of type string which is not what you are comparing it to. You have the If statement testing a value of Yes rather than "Yes". In VB parlance "Yes" = "Yes" but Yes = ""

    Try some debug.print statements at various times to diagnose these types of problems.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

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

    Re: IF statement (VBA Script)

    Just tried that and it goes to the Else all the time. It does no run Com7, Update5.

    Going to drop this and change to code to use a Forward button instead of trying it this way.

    Thanks

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: IF statement (VBA Script)

    Hi Daniel,

    Which did you try:
    . changing 'If UserProperties.Find("sats").Value = Yes Then' to 'If UserProperties.Find("sats").Value = "Yes" Then'?; or
    . the debug.print statements?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: IF statement (VBA Script)

    Used "Yes" How do I know if I am using the right dbtable?
    '-=-=-=-=-=-=-=-=-=-
    Sub UpdateA
    Dim Obe
    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

    'How do I know if I am looking at dbUserID and not a different table?
    Set MyDB = Dbe.Workspaces(0).OpenDatabase("tabsnasdatabase$ed garMasterCIGARS06copyblank.mdb")
    Set RST = MyDBOpenRecordset("dbUserID")
    If UserProperties.Find("sat").Value = "Yes" Then
    cmdCom7_click()
    msgbox"com7"
    Update5
    Else
    msgbox"Else"
    End if
    End if
    End Sub

  8. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: IF statement (VBA Script)

    The right dbTable would depend on what you are trying to do. Your posted code is attempting (unsuccessfully) to create a recordset but then doesn't use the recordset for anything.

    Hans' <post#=612,432>post 612,432</post#> has an example of creating a recordset and doing a count on the records. Pay careful attention to the syntax/punctuation as you have a mistake in your posted code. You will also find samples of code on this forum that show how to read and write data in recordsets. Do a search on terms such as "openrecordset" and "opendatabase"

    Your code should follow the basic pattern shown below...
    <pre>Set dbsSTD = OpenDatabase(MyDatabasePath) ' Open a database object
    Set RST = dbsSTD.OpenRecordset("tblTable", dbOpenDynaset) ' Open table or query within the database
    RST.MoveLast ' Move to the record of interest
    debug.print RST!fieldname ' Read/write value in field fieldname of table tblTable
    RST.Close ' Close the recordset
    Set RST = Nothing ' Release the object
    dbsSTD.Close ' Close the database
    Set dbsSTD = Nothing ' Release the object</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

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