open and create notepad from form

[steve2]

In MS Access 1997 (still working) I want to add a button in a form to open up a notepad file. I've been digging in the forum for a few days, but not being a programmer (wrote it 20 yrs ago) I cannot find the solution.

Here is my case:


I have a form with
fields for customers:

firstname
lastname


Now every time a customer pays a visit to my office
I need to write it down in a report(note).

---------
Therefore I would like to have a code that

1) opens an existing file in notepad (called:
D:\Documents and Settings\Administrator\My documents\management\managecustomers\jonathanrichmond\visitreport

whereas jonathanrichmond is the specific filename that is taken from the combination of 'firstnamelastname'

2) if the file does not exist, it should be created automatically
and with the .LOG command, so that everytime it is opened, it gets the date of today





I experimented a bit partially (but it has been 20 years ago since I last programmed in access so ofcourse it doesnot work)

----------------------
Private Sub Command276_Click()

Shell "NOTEPAD.EXE"

'strDocName = "visitreport"
'strdatum=trim(format(now(),ddmmyyyy_hhmm"))'
'str Filename="D:\Documents and Settings\Administrator\My documents\management\managecustomers\jonathanrichmond\ strDocName & " " & Format(Date(), "ddmmyyyy") & ".txt" , vbNormalFocus




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

I know it should not be that difficult, but for me it is ...

So if someone could help , many thanks


Steve

 

[Lightwave]

Here's some code that I used to create some HTML in a text file - You could easily adapt it for your need.

You will replace the target recordset with a query or table and you should replace the Easting and Northing items with the field names you wish displayed in your file.

Sounds like you are wanting to build a text string and then place it in the file so it is more horizontal than HTML typically is but the principal is the same.

I'm not sure about opening an existing file and writing to that, never tried to do it before.

I hope you are familiar with recordsets - they are like variables except you load a table or query into memory rather than a single value and then you can programmatically refer to the information and call down fieldnames as required. Below I am calling down the variable from a field name using rst! You don't need to define the fieldnames they are picked up from the table or query you set the recordset to. You reference an individual field by name and explanation mark and then the name of the field. Like variables you can call recordsets anything - below I call this recordset rst. Typically they are often called rssomething but could be called a single character if you wanted to.

Because a recordset is like a table you need to step through it hence the loop and the part about checking whether you are at the start or end of the table.

The table or query you wish to place into the recordset is defined between the brackets on this line. Although I state Query here it can be a table.

Set rst = CurrentDb.OpenRecordset("QueryTargetInformation")

Note its fairly easy to go into an infinite loop when you first start stepping through recordsets. It shouldn't damage things if you have to break a loop but take a backup before you kick of the process be patient letting it work through the loop and practice on a non essential machine if you are particularly nervous.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryTargetInformation")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\CodeGeneratedHTML.txt", True)
Do Until rst.EOF = True
TextFile.WriteLine ("")
TextFile.WriteLine (" ")
TextFile.WriteLine (" " & rst!EastingMn & "")
TextFile.WriteLine (" " & rst!NorthingMn & "")
TextFile.WriteLine (" ")
TextFile.WriteLine (" ")
TextFile.WriteLine (" " & rst!EastingMx & "")
TextFile.WriteLine (" " & rst!NorthingMx & "")
TextFile.WriteLine (" ")
TextFile.WriteLine ("")
rst.MoveNext
Loop
TextFile.Close

MsgBox "Created CodeGeneratedHTML File in C drive"

 

[moke123]

Any reason you cant store the notes in your database and then print them when needed?

You should be able to read a text file with the following

Private Function fGetText(strFilename As String) As String

    Dim strFileContent As String
    Dim iFile As Integer: iFile = FreeFile

    Open strFilename For Input As #iFile

    strFileContent = Input(LOF(iFile), iFile)

    Close #iFile

    fGetText = strFileContent

End Function

 

[steve2]

thanks Lightwave!

I have to admit that I'm not familiar with any code anymore, but trial and error solves a lot in access.

I'll test it out this weekend

 

[moke123]

you could do it in one fell swoop with this (hope it works with 20 year old access!)

Private Sub sWriteNote(strPath As String)

    Dim strOutput As String

    'get the existing text into a string
    strOutput = fGetText(strPath)

    'add the new note to the string
    strOutput = strOutput & Date & ": " & "Your new text goes here" & Chr(10)

    'open the text file and write over the existing text
    Call CreateFile(strPath, strOutput)

End Sub

Private Function fGetText(strFileName As String) As String

    Dim strFileContent As String
    Dim iFile As Integer: iFile = FreeFile

    Open strFileName For Input As #iFile

    strFileContent = Input(LOF(iFile), iFile)

    Close #iFile

    fGetText = strFileContent


End Function


Sub CreateFile(strFileName As String, strIn As String)
'needs reference to microsoft scripting runtime

    Dim fso As New FileSystemObject

    Dim ts As TextStream

    Set ts = fso.CreateTextFile(strFileName, True)

    ts.WriteLine strIn

    ts.Close

End Sub

the above will work for an existing file. you should add a test for the file existence and create it if needed.

 

[steve2]

thanks to everybody for the advice.
But I think I have the thing that I wanted (however still not working properly)

--------------------------------------------------
Private Sub Command278_Click()
On Error GoTo Err_Knop278_Click

Dim shlApp As String
Dim stDocName As String
stDocName = "activityreport"

shlApp = "notepad.exe c:\Users\eigenaar\Documents\jonathanrichmond\stDocName.txt"

Call Shell(shlApp, vbMaximizedFocus)


Exit_Knop278_Click:
Exit Sub

Err_Knop278_Click:
MsgBox Err.Description
Resume Exit_Knop278_Click

End Sub
----------------------------------------------------------------------------
It opens- or if not existing- it creates the textfile.
The correct link to the records in the accesform I think I'll be able to program
that.
But the problem is that It creates a textfile called 'stDocName' instead of
'activityreport' .
What am I doing wrong here?

thanks for the advice

 

[moke123]

In red...
shlApp = "notepad.exe c:\Users\eigenaar\Documents\jonathanrichmond\" & stDocName & ".txt"

I'm not sure about Access97 but Ac2010 you can link directly to the text file as a table.

 

[isladogs]

I'm not sure about Access97 but Ac2010 you can link directly to the text file as a table.

True but it needs to be in a format that Access can read as a table i.e. a CSV or TSV formatted file

 

[steve2]

Life can be simple ...

thanks Moke! This works.

For the .LOG command, I think I have to type it in the new created .txt file only the first time at the first line. Afterwards the date is automatically put in. That's a thing I'll have to live with.

I'll try to do the link to the related table and post the result afterwards.


By the way, probably the 2010 and 2013 versions of access have a lot more features, but the 97 version was the reason I started to use access. The only problem is that if I converse it to 2010 I have to use 2003 first, and afterwards cannot program in it. So for that reason, I have to use my old XP laptop to program. It does not look very professionaly, but for me it works.

 

[arnelgp]

for the log:

Shell "NOTEPAD.EXE " & [PathFile], vbNormalFocus
SendKeys "^{END}{ENTER}"
SendKeys "LOG: " & Format(Now, "dddd, dd \o\f mmmm, yyyy hh:nn:ss AM/PM") & "{ENTER}"

 

[steve2]

This should be the final code:


Private Sub Command278_Click()
On Error GoTo Err_Knop278_Click

Dim shlApp As String
Dim stDocName As String
Dim stnaamfile As String
stDocName = "bezoekrapport"
stnaamfile = Me.VOORNAAM & "" & Me.ACHTERNAAM


shlApp = "notepad.exe c:\Users\eigenaar\Documents" & stnaamfile & "" & stDocName & ".txt"

Call Shell(shlApp, vbMaximizedFocus)


Exit_Knop278_Click:
Exit Sub

Err_Knop278_Click:
MsgBox Err.Description
Resume Exit_Knop278_Click

End Sub

--------------

Note: when I edit my post it keeps on changing my code : so between 'Documents', 'Stnaamfile' and 'stDocName' there should be '\' I don't know why, but I tried it twice...
----------------

arnelgp,

I tried to put the LOG code in .
But whatever I change, I get or no permission /or the button does nothing/or it opens a blank notepad (and not in my path)

What am I doing wrong (a lot probably, but I'm used to it...)
This is what I did:




shlApp = "notepad.exe c:\Users\eigenaar\Documents" & stnaamfile & "" & stDocName & ".txt"
SendKeys "^{END}{ENTER}"
SendKeys "LOG: " & Format(Now, "dddd, dd \o\f mmmm, yyyy hh:nn:ss AM/PM") & "{ENTER}"

Call Shell(shlApp, vbMaximizedFocus)

Same note for the code = whith '\' between the strings
----------------------
Now I still remain whith one problem:

It seems access is case-sensitive. And whereas I saved all my 'folders' (in the code called 'stnaamfile')in lowercase, in my access table I used upper-case. I have about 4000 records of which 500 active.
(20 yrs ago I did apparently not think enough...:banghead:
Anyone knows if there is a solution for this?

----------

 

[arnelgp]

you Call Shell() first, after that, the two Sendkeys.

about missing backslash \\ when posting, make sure you double it. it's the page error

 

[moke123]

It seems access is case-sensitive. And whereas I saved all my 'folders' (in the code called 'stnaamfile')in lowercase, in my access table I used upper-case. I have about 4000 records of which 500 active.
(20 yrs ago I did apparently not think enough...
Anyone knows if there is a solution for this?

try
LCase()

 

[Solo712]

----------------------
Now I still remain whith one problem:

It seems access is case-sensitive. And whereas I saved all my 'folders' (in the code called 'stnaamfile')in lowercase, in my access table I used upper-case. I have about 4000 records of which 500 active.
(20 yrs ago I did apparently not think enough...:banghead:
Anyone knows if there is a solution for this?

----------

There is absolutely, positively no solution for this, steve, I am afraid.

Best,
Jiri

 

[isladogs]

There is absolutely, positively no solution for this, steve, I am afraid.

Best,
Jiri

I'm not exactly clear what issue steve has with the case of folder names but I'll offer this possible workround in case it helps

Recently I had to distinguish between field names such as 'DOB' and 'dob'
As far as Access is concerned they are identical which is I believe why Jiri made the above comment.

However, the following code CAN distinguish them

StrComp(UCase(strFieldName), strFieldName, vbBinaryCompare)

If the 2 items being compared are equal, the result = 0 (counter intuitively)
So if strFieldName = "DOB", the result is 0
But if strFieldName = "dob", the result is -1

Can this be adapted to help your situation?

And I know I'm late to this party, but I would recommend that you do NOT use SendKeys.
Code that works in one version of Access using SendKeys may not work in another.
There are other more reliable solutions e.g the code given by Lightwave in post #2

 

[steve2]

LCase did the Job. I first took it a try with the solution of ridders and decided to change Ucase in LCase.
Only afterwards I saw Moke123 allready mentioned it. Time to upgrade my glasses :banghead:
Thank you all!!!! Great Forum!
(ridders: all the folders are outside the dbase so just files in maps)
----------------------------------


Private Sub Command278_Click()
On Error GoTo Err_Knop278_Click

Dim shlApp As String
Dim stDocName As String
Dim stnaamfile As String
stDocName = "bezoekrapport"
stnaamfile = Me.VOORNAAM & "" & Me.ACHTERNAAM
Dim LResult As String
LResult = LCase("stnaamfile")
shlApp = "notepad.exe c:\Users\eigenaar\Documents" & stnaamfile & "" & stDocName & ".txt"
Call Shell(shlApp, vbMaximizedFocus)

Exit_Knop278_Click:
Exit Sub
Err_Knop278_Click:
MsgBox Err.Description
Resume Exit_Knop278_Click
End Sub