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.

Thanks

Re: ADODB Connection referring to Local path NOT Network

All

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

'' Database variables
  Dim cn As ADODB.Connection, rs As ADODB.Recordset
  Dim strDB As String, strPassword As String
  Dim strTable As String, strField As String
  
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset
    
  strDB = ThisWorkbook.Path & "\folder\dbName.mdb"  strTable = "tblHoliday"
  strPassword = "mypassword"
   
  cn.Provider = "Provider=Microsoft.Jet.OLEDB.4.0;"


  cn.Open cn.Provider & "Data Source=" & strDB & "; Jet OLEDB:Database Password=" & strPassword & ";"
  
  ' Opens recordset with criteria as above
  Dim strSQL As String
    
    strSQL = "SELECT * FROM " & strTable & " " & _
      "WHERE idNo=" & myID & " " & _
      "AND aDate=#" & Format(myDate, "mm/dd/yyyy") & "# " 'AND " & _
      "Status='" & myStatus & "'"


    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

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

Can anyone assist further on this please??

Re: VBA Code To Configure Windows Power Management Settings

Hi

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!

Hi

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.

Kev

Re: Trap Error - Subscript Out Of Range

Hi

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.

Cheers

Kev

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
        'CODE
    End If
  Next

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

hope this makes sense.

Cheers

Kev

All

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

Function CalcTotalTime(tmTotal As Date)
    
  Dim strTime As String
  Dim tmTime As Double
  Dim x As Integer
  
  tmTotal = 0
  For x = 1 To 7
    strTime = Me.Controls("txtTotal" & x)
    If IsDate(strTime) Then
      tmTime = strTime
      tmTotal = tmTotal + tmTime
    End If
  Next
  
  CalcTotalTime = Format(tmTotal, "hh:mm")


End Function

Cheers

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

http://www.ozgrid.com/forum/showthread.php?t=59050

Thanks

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

Re: Highlight Portion Of Text Of TextBox

Hi Andy

Apologies for that. I will have a play around with your idea and post back if I get it working. I will also attach sample workbook.

Cheers

Kev[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Andy

Thanks, your idea works great, gives it a good effect i think.

Here is the working sample.

Cheers

Kev

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??

Private Sub spnTime_Change()


  Dim dtTime As Date
  Dim y As Integer
  
  If strTimeChange = "" Then
    MsgBox "Please click on a time to modify it"
    Exit Sub
  End If
  
  dtTime = Format(ctlText, "hh:mm")
  
    y = Me.spnTime.Value
    If y > i Then
        ctlText = Format(dtTime + TimeValue(strTimeChange), "hh:mm")
    Else
        ctlText = Format(dtTime + 1 - TimeValue(strTimeChange), "hh:mm")
    End If
    i = Me.spnTime.Value
    
    ctlText.SetFocus
  
End Sub


Private Sub txtStart_Enter()
  Set ctlText = Me.txtStart
  strControl = ctlText.Name
  HighlightTime (iCur)
End Sub


Sub HighlightTime(iCur As Integer)


  Dim iPos As Integer
  
  iPos = InStr(1, ctlText.Value, ":")
  
  If iCur >= iPos Then
    strTimeChange = "00:15:00"
    ctlText.SelStart = 3
    ctlText.SelLength = 2
  Else
    strTimeChange = "01:00:00"
    ctlText.SelStart = 0
    ctlText.SelLength = 2
  End If
  
End Sub

Appreciate your help

Kev

Re: Set Variable To Forms Control & Get Value Property

Hi Dave

Apologies for the confusion. What I meant is that I copied the code from a forms control spin button as I used that in another file (thinking it would be that same).

Thats working now, thanks for you help.

Cheers

Re: Set Variable To Forms Control & 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.

Private Sub spnWCDate_Change()


  Dim dtDate As Date
  Dim shSpn As Shape
  Dim lSpinVal As Long
  Dim lSpinVal2 As Long


  Set shSpn = ActiveSheet.Shapes("spnWCDate")


  dtDate = Range("rngwcdate")


  lSpinVal = shSpn.ControlFormat.Value


  If lSpinVal > lSpinVal2 Then
      Range("rngwcdate") = dtDate + 7
  Else
      Range("rngwcdate") = dtDate - 7
  End If
  
  lSpinVal2 = shSpn.ControlFormat.Value


End Sub

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

Private Sub btnSpin_Change()


  dtDate = Format(Me.txtDate, "dd/mm/yyyy")
  y = Me.btnSpin.Value
  If y > i Then
      Me.txtDate = Format(dtDate + 1, "dd/mm/yyyy")
  Else
      Me.txtDate = Format(dtDate - 1, "dd/mm/yyyy")
  End If
  i = Me.btnSpin.Value
  
End Sub

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

Cheers

Kev

Hi

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.

e.g

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??

Cheers

Kev.

Re: Query With <> Operator

Hi Mavyak

It was still returning all the records.

Thank you, that is spot on.

Cheers

Kev.

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.

Kev

Re: Create Relationship With Vba

Hi George

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

with regards to this..

Quote

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.

Cheers

Kev

Hi

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

Sub CreateRelationship1()


  Dim Db As DAO.Database
  Dim Rel As DAO.Relation
  Dim Fld As Field
  
  Set Db = CurrentDb()
  Set Rel = Db.CreateRelation("myRelationship", "ColleagueSetup", "Team Details")
   
  Set Fld = Rel.CreateField("Teamleader")
  
  Fld.ForeignName = "Teamleader"
  Rel.Fields.Append Fld
  Db.Relations.Append Rel
  Db.Relations.Refresh
  
  Set Db = Nothing


End Sub

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

Cheers

Kev

Re: change checkbox property on userform using keyboard

Hi

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.

http://proofficedev.com/blog/2007/02/

If you read this thread:

http://www.ozgrid.com/forum/showthread.php?t=17801

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

Hope this helps.

Cheers

Kev.