Microsoft Access to Text File

[newestAF]

New challenge for me here. When a record is edited or added to one of my db tables, I need the save button to update a .bat file. Using the export txt option leaves spaces in places the .bat file doesn't recognize causing it not to function. The form only shows input fields that would change however, the other fields are set to default and are automatically input when a new record is added. I need the vba code to extract data based off what the table would reflect and append an existing .bat file in exact format. If someone could get me pointed in the right direction, that would be great. Thanks.

 

[NXMold]

Create a recordset, set it to a clone of the forms recordsource, step through the current record or as many records as needed creating a string, create a file system object and use it to append the string to an existing file copying/renaming the file if required. I'll try to post some code later today.

 

[newestAF]

Ok, that makes sense but I'm still new to the scripting world. Can you help me out with a template code?

 

[NXMold]

I re-read your post, I assume you want to append an existing batch file with some text from the current record on the same form as the save button (no sub forms). That should be easy:

Private Sub btnSave_Click()
DoCmd.RunCommand acCmdSaveRecord 'Save the current record first

Open "C:\test.bat" For Append As 1
    Write #1, "first line of stuff, Field1: " & txtField1.Value
    Write #1, "second line of stuff, Field2: " & txtField2.Value
Close #1

End Sub

Of course if you click save again it will append more lines to the batch file, and this will repeat indefinately.

 

[newestAF]

Not working. I get a run-time error 424, object required. Not sure what I'm doing wrong here.

 

[NXMold]

You have to modify the code to suit your database, I have absolutely no idea what forms, controls, or fields you have nor what you want to write in the file.

Furthermore, you'll have to hit the debug button when you get an error and see where the problem is. It should highlight a line in yellow, then we can start to narrow things down.

 

[newestAF]

Come to think of it, I think I need to elaborate more on what I'm trying to do. Once the form adds a new record to the table, I need script to take the last record added to the table and write it to the .bat file. The table has information not seen by the user that needs to be included in the .bat file.

 

[newestAF]

Ok, I figured out that problem by changing the write to print and using a string txt reference. This works great for adding but now I need to figure out how to do the reverse. If a user goes in to delete a record from the table via form, I need the script to search the text file for that record and delete it from the text file. This is what I have for the adding. Can anyone help me figure out how to reverse it for deleting now?

Private Sub Command18_Click()
On Error GoTo Close_(Form)__Err

DoCmd.RunCommand acCmdSaveRecord 'Save the current record first

Dim cdb As DAO.Database
Dim rst As DAO.Recordset
Dim strtxt As String
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset([Table Name])
rst.MoveLast
strtxt = [String Referenc]

Open "[File Path]" For Append As 1
Print #1, strtxt

Close #1

Set cdb = Nothing
Set rst = Nothing


DoCmd.Close acForm, [Form]

Close_(Form)__Exit:
Exit Sub

Close_(Form)__Err:
MsgBox Error$
Resume Close_(Form)__Exit

End Sub

 

[NXMold]

Could you re-create the entire file each time its written? Maybe by using a extra boolean field in your table "IncludeInFile".

That would probably be easier than searching it out and deleting lines, but I don't know the whole picture. Is there anything in the file that would relate the line or lines you want to remove to the table? Maybe you will need to add a line:

Print #1 "REM RecordID " & [PrimaryKeyField]

 

[newestAF]

In the strtxt, one of the fields references could be the identifier. I'll probably need to set that field as the Primary Key. The record currently opened in the form shows only 3 fields out of 19. The second field is an excel file name that gets exported later via a batch file that could be a reference point.

This database inputs fields via form to a table, writes that row as script to a batch file that is used to run a query from Oracle later. In the dbs, a code allows access to call that previously scripted batch file to run the query and then automatically send all exported xls associated e-mails. Rewriting the file each time seems to be too much of a hassle. The file name can't change bc it is referenced in code somewhere else in the dbs.

Any suggestions?