Access and Groupwise 1

[Tarnish]

I need to tie in my Access 2003 application with some groupwise functionality, particularly the groupwise calendar but probably also the address book.

I've searched here and there, but don't really see much in the way of helpful info about the process for getting this done. Can anyone suggest a book, or a site or tutorial or anything that can help me get started in this area?

TIA,
T

 

[Tarnish]

Ken,

Thanks for the reply. Sorry I didn't get back to you sooner, but I've been trying to get to a point where I could more fully respond to your last email and that required a lot of thinking which usally means trouble on this end

In re your SetWarnings suggestion, it doesn't really fit here because the MsgBox I'm getting was coded in by me. I've set my form up NOT to allow users to modify records without acknowledging the fact. You have to hit an "edit" button to edit an existing record (but not a new record). Any record that's "dirty" requires you to confirm that you want to save it when you try to leave the record (by any means...hitting the "x" at the top right of the window of either the form or the application, using the nav buttons to change records..anything). I think that's more intuitive to most users, because that's the way most software works. It's not a big deal. It plays out like this. I hit the button to post to gw, it ask me if i want to save, I save, it tells me it's been posted (via msgbox), I hit "ok", it asks me if I want to save again, I save. What I need is a way to, programmatically, respond to those message boxes as they appear. I have a feeling that's not possible or it's well above my knowledge at this point. Again, no biggie at all...just not "ideal".

On the address book, I'm still thinking about that. Hopefully, by the end of the weekend or very early next week I'll have a better ability to explain just what it would need to do. If I tried to do it now, I'd end up writing 5000 words and nothing in it would be clear (I know, because I just tried!).

I'll post back in the next few days.

Thanks again,
Chuck

 

[Eupher]

Hi, Chuck,

In that case, I think I would handle the warning MsgBox with a public boolean variable, call it something like ShowWarnings. Set it to True when your DB or form or whatever opens, then test for the value in the event that pops the MsgBox, i.e.:

If ShowWarnings Then
    [red]...your MsgBox...[/red]
End If

Then in the GW routine, just set ShowWarnings to False before the line(s) that save the record. Remember to set it back to True before exiting the routine.

HTH,

Ken S.

 

[Tarnish]

Ken,

Interesting, but a bit over my head.

Are you saying:

1. Make a module level declaration in my form like

Public varShowWarnings AS Boolean
varShowWarnings = True

Then setup my msgbox such that it only fires if varShowWarnings = False

Then add the following to the "post to GW" command button on_click event (where that GW code is):

varShowWarnings = False
....Existing Code.....
varShowWarnings = True
End Sub

Does that sound right?

Chuck

 

[Tarnish]

I meant, near the top:

Then setup my msgbox such that it only fires if varShowWarnings = True

Chuck

 

[Eupher]

Chuck,

Yup, that's pretty much it. I would not put the declaration in the form's module, I would create a standard module and put your variable declaration there. But then you'll need to assign values to it from within your procedures.

HTH,

Ken S.

 

[Tarnish]

Ken,

Well, I got that set up finally. I already had several standard modules in my code, but all or most of them were just cut-and-paste jobs with a little modification, so I guess it was worth the effort just to get a full home-cooked one up and running. I also ran into an issue with the use of "Me", but somehow found a solution to that as well.

Anyway, I won't be able to test that until monday at work. But I assume that either A. it will work because when one sub is called from another, then variable values (varShowWarnings = False) set in the first sub carry through to the second when the first sub runs, or 2. it won't work because that's not true.

Gives me some reason to want to show up for work I guess.

I'll post back when I've put together a coherent thought about the Address book.

Thanks,
Chuck

 

[Eupher]

Hi, Chuck,

But I assume that either A. it will work because when one sub is called from another, then variable values (varShowWarnings = False) set in the first sub carry through to the second when the first sub runs

Of course it will... assuming the variable has the proper scope.

Let me know how it goes.

Ken S.

 

[Tarnish]

Ken,

Well, finally got those messages to quit popping up on the GW appointment push, at least I think. Won't know for sure until work tomorrow but as soon as I thought of the logical solution I was pretty sure it was right. The part that would have failed most likely I CAN test at home and it worked.

I had to do a massive working on my NotInList events for the three combo boxes associated with people's names. I still think I have a problem if someone in the smallish group of users has the same name as someone in the very largish main address book. I also have one other little issue I'm still working on, but it's not a big deal if it doesn't get remedied. It's in good shape for a demonstration I have to give this week at some point, though.

I'll be able to look more closely on the address book tomorrow morning. I've thought about it quite a bit now but I just can't figure out a good way to do what I want to do. I've got some functionality tied to my contacts' form in the way of tabs that, if I get rid of the form I'll lose. I don't want to lose it.

Everytime I think about it I keep coming to the conclusion that I almost have to duplicate the data, one copy in my own contacts table and another in the GW address book. I'm not sure that's worth it but I should be able to figure it out well enough to post a more thorough airing of the needs/concerns tomorrow morning.

Thanks again,
Chuck

 

[Tarnish]

Well, I was wrong about getting rid of those popup messages when pushing an appointment to gw. I've tried pretty much every combination I can think of putting in values for varShowWarnings (near the beginning and end of each) of the two events, AND trying putting in values inside the standard sub I wrote too (that replaces the code in the beforeUpdate event in my main form..or at least replaces the code that causes the messages to popup warning that data was edited and requiring confirmation of changes).

I know the standard sub works. But it's clear that the values I add to the beforeUpdate event are overwriting the values I put in the _click event of the button that pushes the appointments to GW.

I'm sure there's an obvious answer but I can't see the forest for the trees...

chuck

 

[Eupher]

Hi, Chuck,

It sounds like you are having a problem with where to set the values of the boolean. Since you haven't given many specifics, I'm sorta shooting in the dark here, but I *think* I have an idea where you're headed.

It sounds like, as a general rule, you want the users to be prompted when making any changes to the data. So then I would set varShowWarnings to True very early - like when the form loads or maybe even when your Switchboard loads (if you're using one) - and then LEAVE IT ALONE until you absolutely need to change it. I would not set the value in any BeforeUpdate event, I would only TEST for the value immediately before popping the MsgBox. Further, I would only set the value to False immediately before the first acCmdSaveRecord in your button's click event (that pushes the GW appt) and set it back to True immediately after the last one. Does that make sense?

I apologize if I've misunderstood the issue.

HTH,

Ken S.

 

[Tarnish]

Ken,

Yup, that's all I needed. I never really knew where to declare the sort of "baseline" value, so I always had it in my mind that I'd declare one value in the beforeUpdate event where the msgbox was setup (now in call to my standard module), and then one value in the button_click for GW. That's what I was kinda hinting at a few posts back talking about one procedure being precipitated by another and which value would be used if both had values for a variable (though I said it very differently at the time).

I had already tried declaring it at the top of the form's module, but that locked up the form when I tried to open it.

Anyway, that's fixed so thanks! I can figure out later if I should keep the initial value declared in the "on_load" or if it would be better in the "on_current" event of the form.

Thanks Ken. I'm working on an address book post but thought I would shoot this out real quick.

Chuck

 

[Tarnish]

Actually, now that I think about it, it's probably better in the on_load of the form that loads at startup, like you suggested.

chuck

 

[Tarnish]

Hey Ken!

Don't know if you still have this thread on your radar, but I thought I would try to squeeze one more related answer out of you and also let you know I've all but given up on using the addressbook in GW for this particular project.

I've tied quite a bit of functionality to my current contacts "module" in my application, and even if I could get the GW address book to do what I want it's just getting too late to reinvent that wheel. The only way I could figure out to make it work would be to essentially just set it up to "push" new contacts to a shared address book, so that what it amounts to is just having the data duplicated just for a little convenience. It just seems like too much work at this point in my project's development life.

I do have one last question about the other part, the appointment functionality. I think I mentioned in a prior post that though the workgroup (for lack of a better term) that will be using this application is small, the organizational GW addressbook is massive. Fortunately, since all I'm concerned with is pushing issues to the calendars of those people within the workgroup, I'm only dealing with the smaller number. To get everything to work to my satisfaction, I basically had to add a column to the info that is storred in a combo box via it's recordset. I originally had the person's ID and their name (lastName, firstName MI) That makes it easier to scan existing entries in the dropdown list, and easier to parse the entries when the notInList fires (at least easier for me to do it). Due to groupwise, I added a third column that lists the name: firstName lastName. That seems to be how groupwise wants to find it and it's no big deal.

Here's the problem: even though the workgroup is small, the addressbook is huge. Therefore, it seems almost inevitable that eventually someone will join the workgroup that has the same (groupwise) name as an existing person in the main addressbook. Right now, I just don't have any idea how I could even "fix" a situation like that on a "per person" basis. There must be some type groupwise address book entry "primary key" that I could manually enter such that before using the groupwise name my "push appointment" button would check the "groupwisePK" and, if not null, use it as the basis for pushing instead of the name. Maybe there's a better way, but at least that would give me something that I could add now that would be a reasonable response to that potential issue......it's not like it should happen often.....it probably won't happen at all, but it could.

Anyway, I want to thank you again for all the help. You've really helped me get my project to a high degree of functionality and I really appreciate it.

Thanks again,
Chuck

PS: As always, I FULLY understand if time constraints (or anything else) preclude you from responding.

 

[Eupher]

Chuck,

Why not simply use the GW UserID (i.e. the GW login)? That will always be unique. Then there is no need to parse any combination of firstname/lastname in your code, just send the UserID in the .Recipients.Add method. If the UserID resolves, the GW address book already knows and will append the lastname and firstname. FWIW, our main address book has probably 8,000+ entries.

Ken S.

 

[Tarnish]

Why not simply use the GW UserID (i.e. the GW login)?

Sometimes I just feel stupid not even thinking to try something like that. It's so obvious.

Well, it's fixed now and all I had to do with change the select query expression that populates the combo box with the name of the person that gets the appointment. Didn't even have to change the GW code since it was already pulling from a hidden column in that combo box that I just replaced with an expression that parses out the userID from the email address in my contacts form.

Any easy way to use that User ID to populate my contacts form? That would be pretty nice hehe.

BTW, I don't know exactly how many addresses our main book has, but the best estimate I can get is around 11-12k. Shouldn't be a problem now, though!

Thanks,
Chuck

 

[Eupher]

Chuck,

Heh, no problem. Believe me, I've had more than my share of "Doh!" moments.

Any easy way to use that User ID to populate my contacts form?

How do you mean? By using it as a search criteria for contact info in your DB? Or to pull info from the GW address book?

Ken S.

 

[Tarnish]

How do you mean? By using it as a search criteria for contact info in your DB? Or to pull info from the GW address book?

The second one. Kind of the same principle as you can accomplish with the combo box wizard IF you tell it to find the record based on the combo box selection, except in this case the "record" would be in the gw address book. I could have a button that parsed out the userID from the email address (the code for which I already wrote for the other thing), and then just look up the address book entry in gw for that userID (maybe I wouldn't even need to parse...just look it up by email address...same difference). Then, once found, I could set all the other controls on my contacts form equal to their equivalents in the gw address book and have all that data entry done with just entering one email address and maybe hittng one button or something. When I save the record, as normal, it would save it to my database contacts table.

Just an idea. Seems like it wouldn't be that hard since it's only data retrieval. Maybe I'm wrong.

Chuck

 

[Eupher]

Hey, Chuck, here's a bit of code to get you started with the address book thing:

Public Sub GetAddressBookItems()

Dim myApp As Application2
Dim myAccount As Account3
Dim myAddressBook As AddressBook2
Dim myField As Field2
Dim myFieldName As String
Dim myFieldData As String
Dim i As Integer
Dim j As Integer

Set myApp = New Application2
Set myAccount = myApp.Login("[blue]UserID[/blue]", , "[blue]Password[/blue]")
Set myAddressBook = myAccount.AddressBooks("[blue]My Address Book Name[/blue]")

For i = 1 To myAddressBook.AddressBookEntries.Count
    For j = 1 To myAddressBook.AddressBookEntries(i).Fields.Count
        Set myField = myAddressBook.AddressBookEntries(i).Fields(j)
        myFieldName = myField.Name
        myFieldData = myField.Value
        Debug.Print myFieldName & Space(5) & myFieldData
        Set myField = Nothing
    Next j
    Debug.Print "---------- Next Record ----------"
Next i

Set myAddressBook = Nothing
Set myAccount = Nothing
Set myApp = Nothing

End Sub

For the GW address book you specify, this loops through all the fields in each entry and prints them in the immediate window. Unless the address book is very small it will quickly exceed the max number of lines the immediate window will hold. But you get the idea.

HTH,

Ken S.

 

[Eupher]

Chuck,

A minor variation:

Public Sub GetAddressBookItems()

Dim myApp As Application2
Dim myAccount As Account3
Dim myAddressBook As AddressBook2
[highlight]Dim myEntries As AddressBookEntries2[/highlight]
Dim myField As Field2
Dim myFieldName As String
Dim myFieldData As String
Dim i As Integer
Dim j As Integer
[highlight]Dim x As Integer
Dim y As Integer[/highlight]

Set myApp = New Application2
Set myAccount = myApp.Login("[blue]UserID[/blue]")
Set myAddressBook = myAccount.AddressBooks("[blue]My Address Book Name[/blue]")
[highlight]Set myEntries = myAddressBook.AddressBookEntries
x = myAddressBook.AddressBookEntries.Count[/highlight]

[highlight]For i = 1 To x
    y = myAddressBook.AddressBookEntries(i).Fields.Count
    For j = 1 To y[/highlight]
        Set myField = myEntries(i).Fields.Item(j)
        myFieldName = myField.Name
        myFieldData = myField.Value
        Debug.Print myFieldName & Space(5) & myFieldData
        Set myField = Nothing
    Next j
    Debug.Print "---------- New Record ----------"
Next i

[highlight]Set myEntries = Nothing[/highlight]
Set myAddressBook = Nothing
Set myAccount = Nothing
Set myApp = Nothing

End Sub

Dunno why, but seems to run *much* faster when assigning the objects to variables instead of directly referencing them each time through the loop.

HTH,

Ken S.

 

[Eupher]

Chuck,

Here's another procedure for you to play with. This one takes a user's login and the address book to be searched as arguments. It loops through the address book, searching the User ID field for a corresponding entry in the contacts table; if not found, it creates a new record in the table.

Public Sub ImportGWContacts([red]myLogin[/red] As String, [red]myBookName[/red] As String)

Dim myApp As GroupwareTypeLibrary.Application2
Dim myAccount As GroupwareTypeLibrary.Account3
Dim myAddressBook As GroupwareTypeLibrary.AddressBook2
Dim myEntries As GroupwareTypeLibrary.AddressBookEntries2
Dim myFields As GroupwareTypeLibrary.Fields
Dim i As Integer
Dim x As Integer
Dim CurDB As DAO.Database
Dim Rst As DAO.Recordset

[green]'instantiate the GW object and login; set object variables[/green]
Set myApp = New GroupwareTypeLibrary.Application2
Set myAccount = myApp.Login(myLogin)
Set myAddressBook = myAccount.AddressBooks(myBookName)
Set myEntries = myAddressBook.AddressBookEntries
x = myAddressBook.AddressBookEntries.Count

[green]'open the contacts table[/green]
Set CurDB = CurrentDb()
Set Rst = CurDB.OpenRecordset("tblMyContacts")
If Not Rst.EOF Then
    Rst.MoveLast
    Rst.MoveFirst
End If
[green]'set the index for the Seek method[/green]
Rst.Index = "GWID"

[green]'loop through the selected address book[/green]
For i = 1 To x
    Set myFields = myEntries(i).Fields
    [green]'in this example, limit the scope to Novell Groupwise addresses[/green]
    If myFields.Item("E-Mail Type", egwString) = "NGW" Then
        With Rst
            [green]'look for a matching GWID in contacts table; if not found, create a new record[/green]
            .Seek "=", myFields.Item("User ID", egwString).Value
            If .NoMatch Then
                .AddNew
                !GWID = myFields.Item("User ID", egwString).Value
                !LastName = myFields.Item("Last Name", egwString).Value
                !FirstName = myFields.Item("First Name", egwString).Value
                !PhoneNumber = myFields.Item("Office Phone Number", egwString).Value
                !Department = myFields.Item("Department", egwString).Value
                !Email = myFields.Item("E-Mail Address", egwString).Value
                .Update
            End If
            .MoveFirst
        End With
    End If
Next i

[green]'Close the recordset and release variables[/green]
Rst.Close
Set CurDB = Nothing
Set myFields = Nothing
Set myEntries = Nothing
Set myAddressBook = Nothing
Set myAccount = Nothing
Set myApp = Nothing

End Sub

HTH,

Ken S.