IF statement (VBA Script)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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
‘-=-=-=-=-=-=-=-=-=-

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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$edgarMasterCIGARS06copyblank.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

Reply | Quote

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 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…

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

Reply | Quote