MS Access 2003 - Information from a form output to a text file

Question

So I have this form that has tons of text boxes, combo boxes, etc. There is VB in the form's module that check to see if there are duplicates, and then run a few update queries to save the information in to tables. All that works great.

My question is what VB method can I use in this forms module to take information from all these objects (txt, cmb, etc) and output/save it into a text file?

I would just use as an example a form with the following three text boxes:

Text1 - String
Text2 - Date
Text3 - True/False

I know the vb for adding this to the database tables, but I am looking to be able to save the information in a text file, and then use the SendObject method to allow for these text files to be emailed from the field to the hq, and then placed in a larger db.

The outlook sendobject I think I have a pretty good handle on that portion, just need to figure out how to create a text file with the data in it. Thanks for the help!

Answer 1

you could iterate through the collecton of controls on the form, get the control's name & use select case to get the type of the control & get the appropriate value from it.

these pairs you can write to a string / text file which can then be made into the body of the mail.

Alternatively use the Access DoCmd.SendObject

DoCmd.SendObject acSendQuery, QueryName, acFormatXLS, emailAddress, "", "", "Form Data", "", 0

Where the QueryName is a query that extracts all of the info you need. this will send it as an attached XLS file to the email.

Answer 2

You want to Open a text file and use either the write # or print # statements. Go into Access VBA and search in help on write. You will find the Write # statement which will give you the necessary sample code. You might want the print # statement depending on your requirements.

Answer 3

Are your controls, textboxes, comboboxes, etc, bounded to a recordset? In this case, wouldn't you prefer to send related table records to whoever or whatever is concerned instead of collecting control's values throught text file? I mean this could be done, but I really find it weird!

EDIT: following @galleySlave comments, one idea would then be to write code similar to this one:

Dim m_dataInForm as string, _
    m_control as control

m_dataInForm = "Page Header"
for each m_control in screen.activeForm.section(1)
    m_dataInForm = m_dataInForm & m_control.caption & ": " & m_control.value & vbCr & vbLf
Next m_control
m_dataInForm = "Details"
for each m_control in screen.activeForm.section(o)
    m_dataInForm = m_dataInForm & m_control.caption & ": " & m_control.value & vbCr & vbLf
Next m_control
m_dataInForm = "Page footer"
for each m_control in screen.activeForm.section(2)
    m_dataInForm = m_dataInForm & m_control.caption & ": " & m_control.value & vbCr & vbLf
Next m_control

The m_dataInForm will then contain all data title (.caption) and value (.value). You might need some extra code to avoid errors on controls that do not have caption (like lines) or values (like labels) and/or to get the value in the expected format ('Yes' instead of -1). This will costs you a few extra instructions such as

SELECT CASE m_control.controlType

You'll then be able to send the m_dataInForm value either in a text message or save it as a file somewhere.