Checking my vba references

[ECEK]

Easy one this !!
I have encased my code within "DEPLOYMENT TEXT"

My recordset is a query feuqryCRTSK

I'm just not sure that everything within the "DEPLOYMENT" is referencing this query as it doesn't work.

fileTargettext

Public Sub trusteecreate()

TSKGetFile = "S:\Access\DATA\TT\Front Ends\TRUSTEE TASKS.accdr"
TSKUdFile = "S:\Access\DATA\UPDATER\TRUSTEE TASKS.accdr"

Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("feuqryCRTSK", dbOpenSnapshot)

With rs
If Not (.BOF And .EOF) Then .MoveFirst
While Not .EOF
DoCmd.SetWarnings False



On Error Resume Next


TSKDestFile1 = "S:\Access\DATABASES\" & !Employee & "\TRUSTEE TASKS.accdr"
DoCmd.SetWarnings False
FileCopy TSKGetFile, TSKUdFile
FileCopy TSKUdFile, TSKDestFile1
Kill TSKUdFile
DoCmd.SetWarnings True

'------------------------------------------------------------
'
'
'                   START OF DELOYMENT CODE
'
'
'------------------------------------------------------------
Dim WSHShell
Dim MyShortcut
Dim DesktopPath


Dim FileTarget As String
FileTarget = fileTargettext

Dim Desktop As String
Desktop = networktext

Dim filename As String
filename = filename

Dim shortcut As String
shortcut = shortcutNametxt


Set WSHShell = CreateObject("WScript.Shell")
If Not WSHShell Is Nothing Then
DesktopPath = WSHShell.SpecialFolders("Desktop")


Set MyShortcut = WSHShell.CreateShortCut(Desktop & "\" & shortcut)
MyShortcut.TargetPath = FileTarget & "\" & filename



MyShortcut.WorkingDirectory = Desktop
MyShortcut.WindowStyle = 1
MyShortcut.Arguments = ""
MyShortcut.Save
Set MyShortcut = Nothing
End If

'------------------------------------------------------------
'
'
'                   END OF DELOYMENT CODE
'
'
'------------------------------------------------------------

.MoveNext
Wend
.Close
End With
Set rs = Nothing
End Sub

If you could point me in the right direction then that would be fantastic.

 

[CJ_London]

what does 'doesn't work' mean?

 

[Minty]

You aren't specifying a type for some of your variables, so they will all be variants.

I don't think I would use Filename as a Variable - I'm sure it's a reserved word.

Finally fileTargettext isn't being set to anything so if you meant to refer to a recordset field you would need to use one of the following

!fileTargettext
.Fields("fileTargettext")
![fileTargettext]

to refer to the fields value.

 

[ECEK]

The solution to my question was (as I suspected) not referencing the query "feuqryCRTSK"

This was simply solved by a ! infront of the references. therefore:

Dim FileTarget As String
FileTarget = !fileTargettext

Dim Desktop As String
Desktop = !networktext

Dim filenametxt As String
filenametxt = !filenametxt

Dim shortcut As String
shortcut = !shortcutNametxt

Works a treat. Thanks for your input peopl.

 

[Minty]

As a piece of advice, it more common to declare all the variables at the beginning of your code, as it makes it much less cluttered and hence much easier to read. It also indicates that you have thought through what you needed rather than adding things in piecemeal as you go.
Also when you use a With object it also make it much more obvious you are still in the With clause if you indent you code so something like

Public Sub trusteecreate()

    Dim WSHShell
    Dim MyShortcut
    Dim DesktopPath
    Dim shortcut As String
    Dim Desktop As String
    Dim filename As String
    Dim FileTarget As String
    Dim rs As dao.Recordset
    
    TSKGetFile = "S:\Access\DATA\TT\Front Ends\TRUSTEE TASKS.accdr"
    TSKUdFile = "S:\Access\DATA\UPDATER\TRUSTEE TASKS.accdr"
    
    Set rs = CurrentDb.OpenRecordset("feuqryCRTSK", dbOpenSnapshot)

    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            DoCmd.SetWarnings False

            On Error Resume Next

            TSKDestFile1 = "S:\Access\DATABASES\" & !Employee & "\TRUSTEE TASKS.accdr"
            DoCmd.SetWarnings False
            FileCopy TSKGetFile, TSKUdFile
            FileCopy TSKUdFile, TSKDestFile1
            Kill TSKUdFile
            DoCmd.SetWarnings True

            '------------------------------------------------------------
            '
            '
            '                   START OF DELOYMENT CODE
            '
            '
            '------------------------------------------------------------
       
            FileTarget = !fileTargettext
            Desktop = !networktext
            filename = !filename
            shortcut = !shortcutNametxt

            Set WSHShell = CreateObject("WScript.Shell")
            If Not WSHShell Is Nothing Then
                DesktopPath = WSHShell.SpecialFolders("Desktop")
                Set MyShortcut = WSHShell.CreateShortCut(Desktop & "\" & shortcut)
                MyShortcut.TargetPath = FileTarget & "\" & filename
                MyShortcut.WorkingDirectory = Desktop
                MyShortcut.WindowStyle = 1
                MyShortcut.Arguments = ""
                MyShortcut.Save
                Set MyShortcut = Nothing
            End If

            '------------------------------------------------------------
            '
            '
            '                   END OF DELOYMENT CODE
            '
            '
            '------------------------------------------------------------

            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
End Sub

This is much easier to read, and much easier to spot errors in both logic and program flow.

You'll notice you have turned warnings off twice. I'm pretty certain nothing you are doing requires warnings off, and can be dangerous as it hides genuine errors being displayed. I would avoid that at all times. All action events can be called from Access without the need for that.

Also add Option Explicit to the top of all your code modules, you still have undeclared variables in your procedure.