Search results matching tag 'VBA'

Use of REApplication object in VBA

47EBEBFF-1807-42F0-9B47-861F51022039 — Mon, 24 Nov 2008 18:37:59 GMT

Hi ... Has anyone tried to use the REApplication object and specifically writing code for the UIOpening and UIClosing events ? The vba documentation (admittedly limitted) suggests that VB code can be written to execute code when the user starts up a Raisers Edge session. I would like to take advantage of this, but do not have any examples for the event subroutine calls.

Thx Nigel

Search for Constituents by bank account

51CA828C-2740-4A43-8ABA-E04CEF1FE4D2 — Sun, 19 Oct 2008 16:58:38 GMT

Hi,

I'm looking for a way to find contstituents by their bank account.

As off version 7.81 the account numbers are encrypted in the DB.
Using the standard CustomWhereClause doesn't work because it queries the raw (encrypted) data.

Is there a way to encrypt the plain text (account no) and pass that in the customwhereclause ?
Or another filter object where I can give the account no in plain text as a parameter that returns a constituent collection ?

Eric

VBA Event on Batch new line

02e4d6ac-3dca-4c0c-b23e-72bd93449eaa — Fri, 29 Aug 2008 01:30:20 GMT

We're wanting to run a duplicate check on each line of a constituent batch, as the data is being entered, so that at the end of the batch we know that everyone of the records has been checked.

Currently, we have in the user procedure to click on each line, then do a "Duplicate Search For xxxxxx" in the menus, but users being users, this isn't always done to an acceptable standard. So we want to force them to do this, every line.

I have tested out all of the VBA events to see when they are triggered, and these ones I don't think ever fired in my testing:
-    Batch_BeforeBarCodeLookup
-    Batch_BeforeValidateRecord2
-    Batch_BatchException
-    Batch_AfterPostRecord
-    BatchVBARecord_HandleException
Certainly none fired as each line was being entered....

Does anyone know of an event that we can capture, or a way to automatically run the duplicate check on each line during entry instead of on the entire batch?

Queue; multiple queries to be refreshed

AEBCDA46-BA74-4CEC-A05F-C880E09397C3 — Tue, 29 Jul 2008 19:12:44 GMT

Hello,

I'd love your thoughts on how to approach the following issue:

I have a monthly process that entails refreshing 15-20 queries with a specific date range (i.e. June 20-July 21) and then doing 5-6 exports based off of various of the queries.

Currently, the process takes 4-6 hours due to the size and complexity of the queries and exports.

We have RE:QUEUE. However, it cannot auto-advance the dates to the next month. Thus I must either be there to refresh the dates using an ask operator, or I must manually perform the process.

Any thoughts at all would be appreciated, including customization suggestions using VBA or API or another appropriate methodology.

Many thanks!

Faith

UserForm not on Windows taskbar

60E2D44C-288D-4386-94F5-17635FE1D8A5 — Mon, 30 Jun 2008 14:59:03 GMT

I'm new to RE VBA and appreciate any assistance with the following problem :)

When I launch a VBA userform through a macro from the RE shell it does not appear on the Windows taskbar. So if I switch to another application (like Excel) I can't get back to the userform by clicking an icon on the taskbar or using ALT-Tab to rotate through windows. RE itself disappears from the Alt-Tab. There is still an RE icon on the taskbar but when I click it I go back to the RE and not to the userform. Since the userform is still open I hear the "thunk" that tells me a window is open and needs to be closed...since I can't access it I can't close it so effectively RE has crashed.

I get the same behavior whether the "ShowModal" property of the form is set to "true" or false.

Thanks in advance for any suggestions on correcting this!

Thanks,
Frank

Calendar Control

EA510BD0-8193-4A50-9EAC-58C4C883D722 — Thu, 12 Jun 2008 23:26:50 GMT

I am looking for a calendar (date) control like the one that appears in the date column of the attributes pages in RE.

It is not one of the default controls. I tried RE7_SC72.SuperCalendar, but that is not what I want.

Forms

EA510BD0-8193-4A50-9EAC-58C4C883D722 — Wed, 11 Jun 2008 23:45:49 GMT

I am creating a macro for entering a new individual consitutent. Unfortunately the only reference I have found so far to Forms in the RE VBA esentials guide is on page 98, where it indicates that "VBA provides full-featured forms design support", where I "can design a custom data entry form". It does not however, explain how to do this. After much reseach, trial, and error I have found that I must start the macro with the following code in the module named "User_Macros":

Public Sub AddNewMember()

Load frmNewMembers
frmNewMembers.Show

End Sub

Where "AddNewMember" is the name of the macro. Now my macro is being terminated as follows:

Private Sub cmdExit_Click()

    moCodeTablesServer.Closedown
    Set moCodeTablesServer = Nothing

    End

End Sub

This is taking place in the form, not in the original User_Macro module. The "Closedown" and "Nothing" lines are to shut down the Code Tables Server that I am using to load a combo box -- it took me quite awhile to figure out how to do this also, especially since there was an error in the sample code. But I am wondering if I am going to end up with any memory leaks from not unloading the form? Is this done automatically as part of the "End" statement? I cannot figure out how to return to the original code and unload the form.

Re: Creating an Action on Save

88105A16-FFA2-43EC-A7BE-D01D62236774 — Fri, 09 May 2008 02:05:20 GMT

You would need the VBA module [edit: as David mentioned] rather than an API license to do this. This code shows one way to track what values were changed TO, but you could extend it further to also track/log initial values. I don't know that actions are good place to store audit trail though, as a user could change the action records (and you'll have a LOT of action records). I would recomend a separate database table.

Public Sub Constituent_BeforeSave(oRecord As Object, bCancel As Boolean)

    Dim oM As IBBMetaField, oD As IBBDataObject
    Set oD = oRecord
    Set oM = oRecord

    Dim sChaChaChaChanges As String
    Dim i As Long
    For i = 1 To oM.Count
        If oD.FieldIsDirty(i) Then
            sChaChaChaChanges = sChaChaChaChanges & oM.DisplayText(i) & " changed to '" & oD.Fields(i) & "'" & vbCrLf
        End If
    Next i

    Dim oConstit As CRecord
    If Len(sChaChaChaChanges) Then
        Dim oA As CAction, oA2 As IBBAction2
        Set oA = New CAction
        oA.Init REApplication.SessionContext
        oA.Fields(ACTION_fld_RECORDS_ID) = oD.Fields(RECORDS_fld_ID)
        oA.Fields(ACTION_fld_CATEGORY) = "Task/Other"
        Set oA2 = oA
        With oA2.Notepads.Add
            .Fields(NOTEPAD_fld_NotepadDate) = FormatDateTime(Now, vbShortDate)
            .Fields(NOTEPAD_fld_NotepadType) = "Audit Trail" '<-- use a valid type
            .Fields(NOTEPAD_fld_Description) = "changes at " & Now
            .Fields(NOTEPAD_fld_Notes) = sChaChaChaChanges
        End With
        oA.Save
        oA.Closedown
        Set oA = Nothing
        Set oA2 = Nothing
    End If
    Set oD = Nothing

End Sub

Re: Creating an Action on Save

82205EDC-D36A-44E9-97CA-C3CEAE5AAF7D — Wed, 07 May 2008 16:09:53 GMT

Absolutely. This is a straight forward task. Using the VBA module you would capture the values before the record is opened and then on saving the record you would compare the values to those when the record was opened and record any change. This is common where you want to keep an audit trail of changes made.

Please don't hesistate to contact me if you would like any more information.

David

Log To SQL

76BA76AC-5DB3-4D0D-ABDB-71753008B104 — Mon, 10 Mar 2008 21:39:43 GMT

We've got a VBA macro and would like to log when whenever someone runs it and the context (what record were they in at the time). So basically, I want to capture the Date, Time, UserName, and ConstituentID-UserName of the record they were in at the time. Ideally, I'd like to send this information to a SQL table in a database on the same server as RE, but not the RE database. I have some code I've created for pulling information out of SQL and pulling it into RE and I was going to use that. However, I realized that it depends on having a DSN pointing to the other database and I don't want to do that for all of my users.

I'm very handy with SQL - much less so in VBA.

Does anyone have anything that might fit the bill?

Thanks!

Mitch