Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Unanswered: VBA BeforeUpdate to move .doc file...

    Hi all,
    I work at an investigative agency (not as interesting as it sounds). When a case is reassigned to another investigator I'd like an accompanying .doc file to move to another location, a template to be copied/renamed if the case has yet to be assigned etc. I've tried the following on the BeforeUpdate event of the form in question. Through testing/commenting sections out it seems the file I/O stuff is all working fine but I'm not getting msgbox dialogs where/under conditions as expected. Whenever a msgbox should fire I get nothing - no dialog, no error - just stuck on the record, unable to make the change ("You can't save this record at this time..." when closing form). This is a legacy Access 2k app... Thanks for any help...

    Chris


    Option Compare Database
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error Resume Next

    Dim NewInvest As String
    Dim OrigInvest As String
    Dim NewLoc As String
    Dim OrigLoc As String

    NewInvest = Left(Me!Investigator.Value, 8)
    OrigInvest = Left(Me!Investigator.OldValue, 8)
    NewLoc = "\\Server\DATA\USERS\" & NewInvest & "\CWS\" & Me!Case & "cws.doc"
    OrigLoc = "\\Server\DATA\USERS\" & OrigInvest & "\CWS\" & Me!Case & "cws.doc"

    If OrigInvest = "" Then
    FileCopy "\\Server\data\intranet\Forms\investigator\worksum .dot", NewLoc
    ElseIf Dir(OrigLoc) = "" Then
    If MsgBox("A CWS has either not been started or been misnamed. OK to create a new one for the new investigator. Cancel to look for and correct misnamed file (youll need to reassign the case again when done).", vbOKCancel, "CWS Missing") = vbOK Then
    FileCopy "\\Server\data\intranet\Forms\investigator\worksum .dot", NewLoc
    Exit Sub
    Else
    Me!Investigator.Value = Me!Investigator.OldValue
    Application.FollowHyperlink "\\Server\DATA\USERS\" & OrigInvest & "\CWS\"
    Exit Sub
    ElseIf OrigInvest <> NewInvest And Dir(NewLoc) <> "" Then
    MsgBox "A new CWS has already been created by the new investigator. Both will now open. Please Copy and Paste appropriately FROM THE OLD file TO THE NEW which is already saved in the proper location", vbOKOnly, "CWS Already Created"
    Application.FollowHyperlink NewLoc
    Application.FollowHyperlink OrigLoc
    ElseIf NewInvest <> OrigInvest And Dir(NewLoc) = "" Then
    Name OrigLoc As NewLoc
    If Err.Number = 70 Then
    MsgBox "The CWS is open cannot be moved at this time. Please have it closed then reassign the case again.", vbOKOnly, "CWS Currently in Use"
    Me!Investigator.Value = Me!Investigator.OldValue
    End If

    End If

    End Sub
    Last edited by cwiz; 11-17-10 at 20:23.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    My guess is that your problem is with your lines like

    If OrigInvest = "" Then

    You're assuming, I suspect, that if

    Me!Investigator.OldValue

    has no value, then

    OrigInvest = Left(Me!Investigator.OldValue, 8)

    will return a Zero-Length String, which is what you're checking for with

    If OrigInvest = "" Then

    But in fact, if it's empty, it'll return a Null.

    If you replace

    OrigInvest = Left(Me!Investigator.OldValue, 8)

    with

    OrigInvest = Nz(Left(Me!Investigator.OldValue, 8),"")

    it will return a Zero-Length String, if empty, and your test will work properly.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2006
    Posts
    4
    Hi linq,
    Thanks for the response. Unfortunately that doesn't seem to do the trick. I even tried using Nz that way on all the variables. I should say this. If I cut out all other conditions and simply do this:

    If OrigInvest <> NewInvest And Dir(NewLoc) = "" Then
    Name OrigLoc As NewLoc
    If Err.Number = 70 Then
    MsgBox "The file is open.", vbOKOnly, "File Open"
    Me!Investigator.Value = Me!Investigator.OldValue
    End If

    End If

    it will move the file fine and dandy if not open. If its open it won't do both (msgbox and reset to oldvalue) or either when the other is commented out.

Posting Permissions

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