Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB code only works from 1 directory (xp)

    Here is a strange problem well 2 problems stemming from the same thing. Everything works fine until I change the directory name then when I run the progran again the following happens:

    1/ a text box on a form Control Source (=Format(Now(),"dddd"", ""mmm d yyyy"", ""hh:nn:ss ampm")) usually show the date and time after change of directory all I get is (#name?)

    2/ the following code stops working; works well until I change the dir name then nothing
    -----------------------------------------------------------------------------------------------------
    Private Sub Command2_Click()

    Dim deluser As String
    Dim loginuser As String
    Dim stDocName As String
    Dim stLinkCriteria As String


    deluser = "Del Last User"
    DoCmd.OpenQuery deluser, acViewNormal, acEdit

    'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Me.cboEmployee.SetFocus
    Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.txtPassword.SetFocus
    Exit Sub
    End If
    'Check value of password in tblEmployees to see if this matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("loginpassword", "Users", "[UserID]=" & Me.cboEmployee.Value) Then

    loginname = Me.cboEmployee.Value

    'Close logon form and open splash screen

    loginuser = "Login User Query"
    DoCmd.OpenQuery loginuser, acNormal, acEdit

    DoCmd.Close acForm, "Password screen", acSaveNo

    If DLookup("[Permission Enter PO]", "[Current User]") = 1 Then

    DoCmd.OpenForm "Main Menu"
    Else
    DoCmd.OpenForm "Warning"
    End If


    Else
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
    Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
    MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
    Application.Quit
    End If

    End Sub
    ------------------------------------------------------------------------------

    Any ideas to what is going on

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: VB code only works from 1 directory (xp)

    <hr>Until I change the directory name<hr>

    What directory name are you changing?
    Do you have a split database?
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB code only works from 1 directory (xp)

    It sounds like a variation on the 'missing reference' problem. If you look at Tools | References... in the Visual Basic Editor when the problem occurs, is there a reference near the top of the list that indicates MISSING?

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code only works from 1 directory (xp)

    I have looked that and they seem to be all intact. I highlighted each and none said missing. I anso tried coping the program to another location and as long as the original directory was their the program works fine. The next thin I will try is del 1 thin at a time out of the orl location and try the copy until I hit the error.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code only works from 1 directory (xp)

    Yes the database is split but their is code to look for the backend and if not found to ask you for the location... that works great tanks to Hans and the rest of the people here. The back end is in a sub directory of the front end.... if I change anything in the directory up to the fron end... I have to search for the backend which goes ok then I have the problems...

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: VB code only works from 1 directory (xp)

    The question I am asking is "Why does the database care about you changing the name of a directory?"

    One option is that there is some code somewhere that has a directory name hard coded in it. From the VBA editor do a search.

    The other option is it is something to do with the path to the backend. The front end can no longer find the backend.
    So is it the case that the directory you are renaming is the one that holds the database, or some other directory?
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code only works from 1 directory (xp)

    One option is that there is some code somewhere that has a directory name hard coded in it. From the VBA editor do a search.

    Do you mean search just the code that the problem is happening in or all of the code? I have posted the code where the problem is happening. And would this account for the text box problem as well?


    The other option is it is something to do with the path to the backend. The front end can no longer find the backend.
    So is it the case that the directory you are renaming is the one that holds the database, or some other directory?


    I have code installed so that if the backend is lost it is automatically found or you are give the chance to re-link to it. I can change the dir name and this code works fine as after I run it I can see the links to the backed tables just fine.... it is just this text box and the one secion of code that stops working.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: VB code only works from 1 directory (xp)

    Like you I am at a loss to know what the problem is.

    So in the vba editor it only takes a second to go Edit..Find then choose "Current Project" just to see if you have any hard coded reference to the folder name anywhere.

    You still have not answered the question I have asked twice. Is the folder you are renaming the one that has the database in it?

    Are you renaming it while the database is open, or while it is closed?
    What triggers the code the relink the backend? Does it just happen at startup or anytime the links are lost?

    Does compiling the code produce any errors?

    It could well be some mysterious corruption. What happens if you import everything into a new blank database?
    or try decompiling then compiling again.
    To decompile open the database with a shortcut with /decompile added to the end of the shortcut.
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code only works from 1 directory (xp)

    Like you I am at a loss to know what the problem is.

    So in the vba editor it only takes a second to go Edit..Find then choose "Current Project" just to see if you have any hard coded reference to the folder name anywhere.


    This is not a hard coded reference but it might be the problem. in the VBA I have used the following code
    --------------------------------------------------------------
    strPathBackground = CurrentProject.Path
    If Not Right(strPathBackground, 1) = "" Then
    strPathBackground = strPathBackground & ""
    End If
    strFileBackground = strPathBackground & "Backgroundssnow.jpg"
    ----------------------------------------------------------------------------------------------
    This should make the directory tree dynamic up to the Backgroundssnow.jpg since the first parts is CurrentProject.Path or should it be Application.CurrentProject.Path

    You still have not answered the question I have asked twice. Is the folder you are renaming the one that has the database in it?

    Yes. I was trying the code to re-link to the backend when I found this problem.

    Are you renaming it while the database is open, or while it is closed?

    Closed

    What triggers the code the relink the backend?

    On open event of a splash screen (splash screen auto starts)

    Does it just happen at startup or anytime the links are lost?

    It is only attached to the On open event of the splash screen so it only happens at the start.

    Does compiling the code produce any errors?

    nope it looks like it compiled just fine.

    It could well be some mysterious corruption. What happens if you import everything into a new blank database?
    or try decompiling then compiling again.
    To decompile open the database with a shortcut with /decompile added to the end of the shortcut.


    I think I might know that the problem is. I tried to import all the items into a new database and when I tried to run it it gave me an error that I was missing a reference. I looked and re attached the reference and the new database seemed to work just fine... I tried the old database and it now worked as well... I think it might have something to do with the moving of that reference.

    Would it be ok to place the *.mde (the reference file) into a directory which is on every computer (Like say c:windowssystem32).

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code only works from 1 directory (xp)

    Hans,

    Why did I ever doubt you..... I have been playing with this problem and as soon as I removed and re-entered the reference it worked just fine. It was just funny how it did not show missing......

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: VB code only works from 1 directory (xp)

    I am glad you have sorted out the problem.

    There is one thing in your code I want to comment on. (maybe this should be in another thread, but I have noticed it here.)

    You use: strPathBackground = CurrentProject.Path

    CurrentProject.path gives you the path to the fornt end not the path to the backend.

    So if you use the common setup of having the backend on a server, and each user having a local copy of the front end, then it is usually the path to the backend that you need to know, because you keep snow.jpg on the server not on each users local disk.

    Even if you don't currently use this setup, it would be better to write your code so that you could move the front end without creating problems.
    Regards
    John



  12. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB code only works from 1 directory (xp)

    Thanks for all your help....

    I use it this way so each user can change pictures (locally) to suit their liking... As long as they use the same names in the background dir they are fine

Posting Permissions

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