Posts by Ktrasler

    Re: [SOLVED] ADODB Connection referring to Local path NOT Network

    Apologies anyone who has been looking at this.

    I have realized that I made a very stupid mistake...

    I managed to get it working if I reference the backend of my database, and then realized the problem was with the linked tables in the frontend.
    I removed all the linked tables and re-linked them and it seem to solve my problem...

    Apologies for wasting anyone's time...

    [SOLVED] ADODB Connection referring to Local path NOT Network

    Well, I have solved my problem. Once I knew the connection was fine it got me just playing with the SQL... Then it hit me - after a very long time...

    strSQL = "SELECT * FROM `T:\Folder\Folder\dbName`.tblName " & _
          "WHERE aDate>=#" & Format(Range("aDateFrom"), "mm/dd/yyyy") & "# " & _
          "And aDate<=#" & Format(Range("aDateTo"), "mm/dd/yyyy") & "#"

    The thing I still don't get is Why!

    Why would I have to specify the pathname in the SQL when I have already established a connection.


    Re: ADODB Connection referring to Local path NOT Network


    Another thing that is strange with this is that the connection to the database seems fine. Once the connection is opened I can see that the access database has the locking file open.

    I have tried modifying the path to the actual network path, i.e \\server\folder\folder"

    I have also tried modifying the way in which the rs is created, such as Set rs.ActiveConnection = cn

    Both of the above do nothing different, i still get the error relating to the C:\drive.

    Can anyone assist... I am completely stumped..

    Hi All

    I am having a problem with an ADODB connection.

    On my local drive it has been working fine, but the problem comes when I put it on the network terminal. The path names are correct. If I test the strDB to the immediate window it returns the correct path but when the code gets to the rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic it fails with the following error.

    The path name is automatically reverting to the c:Drive

    'C:\Documents and Setting\username\My Documents\folder\dbName.mdb' is not a valid path.
    'Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

    The first part of the code is

    I have tried adding chDir() but I can't get syntax correct.

    Just to confirm, this works fine on the local drive so I have been connecting to a local path fine...

    Any ideas... I hope this can be done...

    Re: VBA Code To Configure Windows Power Management Settings


    I have looked at lots of different websites just from searching in google, but can't find a VBA example to work with, and I don't know where to start with how to code when not dealing with the functionality of excel alone.

    Are you able to supply an example of how to achieve what I'm after?

    appreciate you help on this!


    Is it possible to modify the windows power settings in excel vba.

    I have this example, but it doesn't do anything.

    Sub testPOWERCFG()
        CreateObject("WScript.Shell").Run "POWERCFG /CREATE Custom1", 0, False
        CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-ac 15", 0, False
        CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-dc 10", 0, False
        CreateObject("WScript.Shell").Run "POWERCFG /SETACTIVE Custom1", 0, False
    End Sub

    I need some code to create and activate a scheme whereby everything is on and also some code to revert back to the default settings on the machine.

    Hope this all makes sense.


    Re: Trap Error - Subscript Out Of Range


    I have changed the code so that it doesn't/can't happen, as that seems the most logical solution.

    Although I did try both of your methods but could only get them working when they ran TRUE as the array had been formed, it still came up with error if they hadn't.

    Not to worry, it was obviously bad design in the first place.



    Hi all

    How can you trap an error for subscript out of range on an array.

    the error happens if this code is run before the arrays is formed.

    For i = 1 To 14
        strSheet = SheetArray(i)
        strForm = FormArray(i)
        If strSheet <> strForm Then
        End If

    I could use On Error Goto ...... but I wondered if you could test if the array had formed.

    hope this makes sense.




    I have searched and searched for this but can't find it.

    I want to sum hours in vba but as it gets to 24 hours it goes back to 0.

    I know how to do this on a worksheet, but can't do it in a module.

    my times are stored in text boxes on a userform.

    heres the code I have got


    Kev[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Just found a solution in a post by Andy Pope.


    CalcTotalTime = Application.WorksheetFunction.Text(tmTotal, "[hh]:mm")

    Hi All

    The following code highlights part of the time (hours or minutes) that will be modified by a spin button.

    The code works fine (although maybe a more efficient way).

    Each time you click in the time it will highlight the hours or the minutes, depending on where you click.

    When you click on the spin button the control loses the focus so becomes un-highlighted.

    I thought that just setting the focus back to the text box would work but what I am getting now is it highlights on every other click of the spin button.

    How can I rectify this??

    Appreciate your help


    Re: Set Variable To Forms Control &amp; Get Value Property

    Hi Dave

    I'm still getting the following error message:

    Object doesn't support this property or method

    The control I am using is from the control toolbox toolbar

    Here is the code to the spin button - This doesn't work.

    Here is the code I use in a userform spin button - This works fine.

    Can't understand why it won't work, although I haven't used the embedded controls before.




    This is going to be really simple but my mind has gone blank...

    How do I reference a spin button from a variable that I set.


    Dim objSpn As Object
    Dim y as Integer
    Set objSpn = ActiveSheet.Shapes("spnWCDate")  
    y = objSpn.Value

    This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it.

    ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable??



    Hi All

    Can't understand why the following doesn't work when changing the operator.

    Using the "<>"
    SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift FROM qryColleagues, tblTempNew WHERE (((qryColleagues.WCN)<>[tblTempNew]![WCN])) GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift ORDER BY qryColleagues.ClgName;

    the particaular part is (((qryColleagues.WCN)<>[tblTempNew]![WCN]))

    it works if the operator is "=" but not "<>"

    This works if there is only one record in the tblTempNew table. As soon as I put 2 records in there it does not work.

    Any ideas.


    Re: Create Relationship With Vba

    Hi George

    Thank you for you post, I have managed to get it working now.

    with regards to this..


    If I create the relationship manually it works fine but I need to be able to do this via vba.

    I could only create the relationship manually if I did not tick the box enforce referential integrity.

    I have checked through the database (the database is not one that I created, i just import the information) and it was due to some records not existing in the table that were in the primary table. I have now rectified this and it works fine, both manually and via code.

    Thanks for your help on this.




    I am trying to create a relation between two tables but I am having problems

    the relationship is

    One Teamleader to Many Colleagues

    I want to create a relationship between the Teamleader field in the ColleagueSetup table to the teamleader field in the team details table

    I get the error

    No unique index found for the referenced filed of the primary table.

    If i switch the table names around like so

    Set Rel = Db.CreateRelation("myRelationship", "Team Details", "ColleagueSetup")

    I get the error

    You cannot add or change a record because a related record is required in the table 'Team Details'

    If I create the relationship manually it works fine but I need to be able to do this via vba.

    Can anyone help please



    Re: change checkbox property on userform using keyboard


    You can create a class module to handle certain events to deal with a certain type of control but you can't for the purpose you have mentioned, I wanted to do a similar thing, but have had to enter events for all the controls on the form.

    This link shows how a class is used to handle various check box controls on a userform. It is fairly easy to modify this to deal with a different control. I use a similar thing for the change event of the textbox.

    If you read this thread:

    Andy Pope explains how to store the last textbox entered, but again it involves entering code in each control.

    Hope this helps.