Replies

Like I said in my earlier post, Visual InterDev 6 For Dummies (when you’re just starting out) and then Visual InterDev 6 Unleashed (once you get beyond the basics).

However, note this: Visual InterDev as a development tool is gone in Visual Studio.NET. However, what you learn Visual InterDev, you can still apply all you know to ASP.NET. OTOH, you may just decide to skip learning Visual InterDev entirely and jump straight into ASP.NET — there are pros and cons to doing either, but it’s your decision.

I’ve had crazy problems like this in subforms with listboxes and comboboxes not being requeried even though I issue the requery method.

One thing that worked for me was to programmatically set the RecordSource property for the form and/or RowSource property for the control AGAIN just BEFORE having the line of code doing the requery, i.e.,

Me.List99.RowSource = “SELECT whatever, etc., etc.”
Me.List99.Requery

Let me know if this works for you.

Stephan

What OS are you using? Windows 2000? This sounds more like a Windows 2000 permissions problem than an Access security issue.

But first, you need to rule out that it’s not the Access security which is causing the problem. (I assume you didn’t create Access login security for this user. If so, none of the following applies.) In Access, go to User and Group Permissions under the Tools | Security menu. You should see only one user: Admin. Make sure that user has full rights for ALL the objects — the Database itself, Tables, Queries, Forms, Reports, etc.

If you find that the Admin user indeed has full rights, then like I said, your problem is most likely due to Windows 2000 (or whatever OS you’re using) permissions. In the directory where this DB is stored, make sure this user has full rights to the directory itself and this particular database file. You’ll have to log into Windows 2000 (or whatever OS you’re using) as an administrator in order to do this. I don’t remember all the particulars of setting permissions in Windows 2000, so you should consult your Windows documentation before doing this. Just remember, give this user full rights for that directory and the file itself.

Stephan

Thanks, it’s the AutoExpand property I want — this is analogous to the MatchEntry property in VB/VBA combo boxes. I love it when Microsoft doesn’t make things consistent.

Apparently, in my Access combo boxes which are exhibiting the aberrant AutoExpand behavoir, I have combo box Change event procedures, and when I rem’ed out the code, the AutoExpand behavoir of the combo boxes works fine — although NOTHING in my code altered the AutoExpand property! Looks like I found another Microsoft bug.

Thanks again for your reply,

Stephan

Bill,

Thanks for your reply. However, it turns out what I was trying to do is possible. Incredibly, the trick is simply to create the relationship FROM the 2-key table TO the 3-key table. If you try to do it the other way, Access thinks it’s an indeterminate relationship!

Thanks again,

Stephan

Claus,

Holy cow, of course! Now why didn’t I think of that?

Stephan

Claus,

I did some more tinkering and found the answer using the approach in your solution #2, and I did wind up using the OpenArgs property after all. The answer turned out to be relatively simple, but I couldn’t do it all through VBA code. I also had to create a simple macro.

In the frmClients Load event, I put the following code:

Private Sub Form_Load()
If Me.OpenArgs = “GotoNewSubform” Then
DoCmd.RunMacro “MacroNewSubformRecord”
End If
End Sub

The macro MacroNewSubformRecord only has 2 actions:
GoToControl: [fsubMatters]
GoToRecord: New

Incredibly simple — however it’s just annoying I couldn’t do this 100% through code and had to resort to using a macro.

In any event, thanks again for your help.

Stephan

Claus,

Thanks for your response.

In my case, I could only consider your possible solution #2 since Matters Clients is a one-to-many relationship, so having a fsubMatters subform in the frmClients form makes more sense than using a separate frmMatters form. If I did have a separate frmMatters form, I wouldn’t have a problem.

Essentially, I would have a line a code:
DoCmd.OpenForm “frmMatters”, acNormal, , , acAdd, acDialog

in place of the line:
DoCmd.OpenForm “frmClients”, etc., etc.

But since I’m using a subform, I do have a problem. My difficulty remains going to a new record in the subform. However, I am a little puzzled why you say I have to do this in the frmClients’ Open or Load event. In other words, I don’t see why I can’t also do this if frmClients is already open. For example, if frmClients is open and I want to go to a new record, this simple line of code does the trick:

DoCmd.GoToRecord acDataForm, “frmClients”, acNewRec

Again, what is infuriating is that I can’t figure out the syntax to go to a new record in a subform within the main form. This is all I need to do, and you’d think that this should also be pretty easy to do in Access.

Thanks again,

Stephan

If your users are updating and inserting data using forms, the answer’s easy. Simply use the AfterUpdate and AfterInsert event procedures in the forms.

However, if they’re updating and inserting data directly using queries or in the tables themselves, then I don’t know.

Stephan

Francois,

Thanks!

Using the recordsource property to change the second form’s record source was precisely the type of functionality I was looking for. I really appreciate your help.

Stephan

Francois,

Thanks for your reply.

Just to clarify, you’re right, there is no problem with the existing code. I just need to figure out how to do the same thing a different way. The reason for this is if you open the second form with a filter (as it’s being done in my current code), the user can remove the filter himself using Access’s Remove Filter/Sort command once the second form opens. I need to avoid this possibility.

That’s why I would like to make the underlying query for the second form a parameter query with a WHERE condition, and in my code pass the value to the parameter query when the second form opens.

I hope this clarifies what I’m trying to do.

Thanks again,

Stephan

I want to thank you again for your help on this.

The problem here was really the limitations of what could happen in Outlook’s Send event, and even in Outlook 2000, I don’t know if this could work. (Of course, not having Outlook 2000 here, I can’t really test, but it’s a moot point, because I had to make this work in OL 97.)

Your idea to delete and then re-add the bad email addresses was a good one. It’s just unfortunate that OL’s Send event refuses to send and gives me an error message when I do that. Curiously enough, if I add error handling, the mail message will be sent, but I still get the error message (which is an unacceptable solution).

I also thought of a clunkier solution — which is to have the Item_Send function test for bad email addresses, then return FALSE if there are any, and then have the user click another Command Button to delete and re-add the bad emails, then have the user click the Send button again. Stupid, ugly and clunky, but i’tll work.

Thanks again.

Stephan

Thank you so much for getting back to me. I revised your code slightly (below) to work in Outlook 97/VBScript. Any invalid email addresses are in fact being to corrected to valid formats (and no I don’t mind losing the display name), which is good, but now the send operation is failing (after the email address gets corrected). I get an error message saying “The operation failed”.

I’m beginning to think what I’m trying to do (correct bad email address formats before sending) may be impossible to do programmatically in Outlook? Were you able to send the email successfully after your code ran?

Also, can you recommend some decent Outlook 97 documentation?

Thanks again,

Stephan

‘************************************** BEGIN CODE **************************************
Option Explicit

‘ ————————————————————————
‘ Purpose: Traps the Item_Send event so that invalid email addresses in
‘ the format (name ) from InterAction get corrected.
‘
‘ Inputs: None
‘
‘ Written: 10/26/01 by Stephan Ip
‘ ————————————————————————
Function Item_Send()
Dim n
Dim strEmailAddr
Dim lngBeginBracket, lngEndBracket
Dim intRecipients

‘ initialize
intRecipients = CInt(Item.Recipients.Count)

‘ new array
Dim nameAddress()
ReDim nameAddress(2, intRecipients)

‘ loop through all the recipients
For n = intRecipients To 1 Step -1
strEmailAddr = Item.Recipients(n).Address
lngBeginBracket = InStr(1, strEmailAddr, “”)
‘ test to see if email address has beginning and end brackets,
‘ which means it’s an invalid email address
If lngBeginBracket 0 And lngEndBracket 0 Then
‘ parse email address to strip it of the brackets
strEmailAddr = Mid(strEmailAddr, lngBeginBracket + 1, _
lngEndBracket – (lngBeginBracket + 1))
End If
‘ change email address to corrected parsed address
‘*** FOLLOWING LINE REM’ED OUT BECAUSE ADDRESS PROPERTY APPEARS TO BE READ-ONLY
‘*** EVEN THOUGH OUTLOOK VB HELP SAYS OTHERWISE
‘Item.Recipients(n).Address = strEmailAddr
‘ store new valid address and display name for adding later
nameAddress(1, n) = strEmailAddr
nameAddress(2, n) = Item.Recipients(n).Name
‘ delete bad address
Item.Recipients(n).Delete
Next

‘ add valid addresses
For n = 1 To UBound(nameAddress, 2)
‘If nameAddress(1, n) vbNullString Then
If nameAddress(1, n) “” Then
Item.Recipients.Add nameAddress(1, n)
End If
Next
End Function

Gary,

Thanks for your reply. You’re right on target — the problem does have to do with forms protection.

I did some digging and the culprit in my firm’s setup is Word’s integration with iManage. What’s happening here is that Word’s print command gets captured by a FilePrint procedure in the iManage code. For whatever reason, the code protects forms at the beginning of the procedure, then unprotects at the end (after printing has been completed). So this was what was causing the strange print behavoir.

Thank you for your tip to have to user specify the page number to print — that should make them happy for now until I can release the fixed code.

Take care,

Stephan

Lin,

Thanks for your response. I just started Word with the /a switch so that none of the global templates would load. The bug isn’t happening anymore.

Which means that the problem isn’t because of MS Word itself, but rather due to some integration glitch with either iManage or SoftWise.

Stephan