General tracking question.

[Homegrownandy]

Hello again,



I want to track documentation for 'jobs'. Currently I have a status table where I can select different states a job can be in. Now I want to track which documents a job has.

* the documentation has no dependencies. (any document can be made at any stage)
* each job needs a 'test plan', 'rams' and an 'o&m'.

I'm pretty confident with a solution, but its always best to ask.

Would I add these fields to the jobs table? Then they would either be y/n (document made/document needed).
Is there any need storing this information in another table?

I ask because its either a true or false. the documents either exist or they don't.

Once the fields are set up, the form can have tick boxes to click when the documentation is produced.

Thanks in advance,
Andy

[ranman256]

you wouldnt need the document true/false. Youd have the path to the document / or the stored doc as OLE/attachment.
This filled field would be the TRUE. empty field = FALSE. This would give the 'tick' value: me.chkDocExist.value = not IsNull([docPath])

Tho you may need field [DocReqd] (as T/F) to determine if you DO need a document at all.

[Homegrownandy]

I don't suppose you have a link to anything relating to this? I have looked in the past but I didn't find anything.

anything will do, just to get me pointed in the right direction. I would greatly appreciate it.

Andy.

[ranman256]

I dont have a link. Ive programmed this for 20 years.
Which are you going to do,
store the document path (i like. the doc is on the server for others to get if needed)
or
emmbed the doc itself in the db?

[Homegrownandy]

The files will be saved on the server. I have a lot I would LIKE to do with the DB, but what I don't have a lot of is time. So obviously I'm prioritising what I work on.

I would love it if the DB could create a folder structure itself. But I don't know if its possible and right now I don't have the time to do that.

If its not too time consuming to set it up, so that I can enter file paths for documents, I'll go ahead with your suggestion. I just wouldn't know where to begin.

I like your suggestion though, and like I said I did look into this before. I'll look again though.

Andy.

[ItsMe]

When working with directories, it is likely you will need to reference the Microsoft Scripting Runtime library. One function that is helpful is the MkDir() function. You can use it to create folders. When using code to check if directories exist and make them if they don't, it will be beneficial to use variables and call sub procedures and or functions. Here is an example of a function that checks for a path and creates it if it does not exist.

I always start with a path to a server. So, in this case, it is already know that the path "C:\Test\Stuff\" exists and the connection is valid.

.

Private Sub setFullPath()
gstrPath = ""
gstrFileName = ""
Dim strDocType As String
Dim strDocPath As String
strDocType = "DockReceipt"
strDocPath = "C:\Test\Stuff\" & strDocType & "\"
gstrPath = strDocPath & glngCustomerKey & "\"
gstrFileName = strDocType & "_" & glngDockReceiptKey
 
'Make sure the folders exist and make them if they don't
    If Dir(strDocPath, vbDirectory) = "" Then
        MkDir (strDocPath)
    End If
    
        If Dir(gstrPath, vbDirectory) = "" Then
            MkDir (gstrPath)
        End If
End Sub

[Homegrownandy]

Hey itsme,

That's brilliant! It's a good start to what I want to do here. I've used similar macros in excel, but never in access. Ill spend some time this morning looking into MkDir. (just knowing what to look for helps).

andy.

[Homegrownandy]

I have this working as in your example. I cant seem to get "DockReceipt" to reverence a text box.

I was changing it to (Me!txtName.Text) the box is just called txtname. apart from that its brilliant.

Is there a way to adapt this to do multiple folders? so for example I can get 10 names from a query and then it would check for folders for all of them or make it.

cheers!

[ItsMe]

You will have to determine where you want your variables accessible from. The example provided is very generic and uses global variables. For instance gstrPath and gstrFileName are global variables and the procedure will allow for hyperlinks to use these from anywhere within the application.

Similarly, some key values are passed to the procedure. Both, glngCustomerKey and glngDockReceiptKey are used to insure unique directories and file names. These variables are set at the global level.

On the other side of the spectrum you have the following ...
strDocType = "DockReceipt"

This hardcoded "magic string" is really just a place holder for the code snippet provided. Like the global variables used, you need to decide what works best for your application. Ideally you would use variables that survive, only, for the duration of the procedure. In other words, use variables private to the Class or variables defined within the scope of the procedure.

The following (untested) example illustrates how you might employ the sub procedure in a Standard Module and call it from a click event from a form.


In a Standard Module ... (BTW, name your modules something other than the name of a procedure, ie use something like modFullPath)

Public gstrPath As String
Public gstrFileName As String

Public Sub SetFullPath(ByRef DocType As String, ByRef CustomerKey As Long, ByRef DockReceiptKey As Long)

gstrPath = ""
gstrFileName = ""

Dim strDocPath As String

strDocPath = "C:\Test\Stuff\" & strDocType & "\"
gstrPath = strDocPath & CustomerKey & "\"
gstrFileName = strDocType & "_" & DockReceiptKey
 
'Make sure the folders exist and make them if they don't
    If Dir(strDocPath, vbDirectory) = "" Then
        MkDir (strDocPath)
    End If
    
        If Dir(gstrPath, vbDirectory) = "" Then
            MkDir (gstrPath)
        End If
        
End Sub

And you can call the procedure from a form ...

SetFullPath "DockReceipt", 500, 952
MsgBox "gstrPath = " & gstrPath
MsgBox "gstrFileName = " & gstrFileName

[Homegrownandy]

Thanks for the help, Ill spend some time looking into this.