Saving Last Entries in Form 1

[jercsr]

I have an unbound form set up, where a user enters info in three text boxes. They then open and print multiple reports, each of which picks up the information in the three fields they filled out. Is there a way to save these entries in the form, so that if the database or form is closed, they can reopen the form and not have to retype this information in the next session?

 

[ThomasLafferty]

For the following example, I have a user form called UserData. It has Three text boxes:
FirstName
LastName
Phone

In the form module global declarations area, I declared three variables to contain the text box values:
FName
LName
Ph

When the form loads, it reads in values from a text file. If the file doesn't exist, it is created and blank values are loaded in the textboxes.

When the user closes the form, a procedure writes the values to a txt file. If the user doesn't want to keep the values, blank lines are written to the text file. If the User closes from the Forms control icon, the confirmation message appears a second time.

Here's the code:
Option Compare Database
'Establish variables to track user choice of saving data
'*****Code by Thomas Lafferty*****
Dim FName
Dim Lname
Dim Ph

Private Sub Form_Close()
Call Close_UserData
End Sub

Private Sub Form_Load()
Open "C:\My Documents\UserData.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, FName, Lname, Ph ' Read data into variables.
FirstName.SetFocus
FirstName.Text = FName
LastName.SetFocus
LastName.Text = Lname
Phone.SetFocus
Phone.Text = Ph
Loop
Close #1 ' Close file.

End Sub

Private Sub Save_Close_Btn_Click()
Close_UserData
DoCmd.Close
End Sub

Public Function Close_UserData()
FirstName.SetFocus
FName = FirstName.Text
LastName.SetFocus
Lname = LastName.Text
Phone.SetFocus
Ph = Phone.Text
'*******variable to capture choice to save data******
Dim UserChoice
UserChoice = MsgBox("Do you want to save this data?", vbYesNo, "User Information")
If UserChoice = vbYes Then
Open "C:\My Documents\UserData.txt" For Output As #1
Print #1, FName
Print #1, Lname
Print #1, Ph
Close #1 'Close Output file
Else 'User chose no, so overwrite with blank lines
Open "C:\My Documents\UserData.txt" For Output As #1
Print #1,
Print #1,
Print #1,
Close #1 'Close Output file
End If
End Function

 

[ThomasLafferty]

After looking at my code a bit more closely, I resolved the double call issue. A public function is not necessary. All that needs to happen is to put the code in the close form sub and under the close button sub, use docmd.close. Here's the improved code:

Option Compare Database
'Establish variables to track user choice of saving data
'*****Code by Thomas Lafferty*****
Dim FName
Dim Lname
Dim Ph

Private Sub Form_Close()
FirstName.SetFocus
FName = FirstName.Text
LastName.SetFocus
Lname = LastName.Text
Phone.SetFocus
Ph = Phone.Text
'*******variable to capture choice to save data******
Dim UserChoice
UserChoice = MsgBox("Do you want to save this data?", vbYesNo, "User Information")
If UserChoice = vbYes Then
Open "C:\My Documents\UserData.txt" For Output As #1
Print #1, FName
Print #1, Lname
Print #1, Ph
Close #1 'Close Output file
Else 'User chose no, so overwrite with blank lines
Open "C:\My Documents\UserData.txt" For Output As #1
Print #1,
Print #1,
Print #1,
Close #1 'Close Output file
End If
End Sub

Private Sub Form_Load()
Open "C:\My Documents\UserData.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, FName, Lname, Ph ' Read data into variables.
FirstName.SetFocus
FirstName.Text = FName
LastName.SetFocus
LastName.Text = Lname
Phone.SetFocus
Phone.Text = Ph
Loop
Close #1 ' Close file.

End Sub

Private Sub Save_Close_Btn_Click()
DoCmd.Close
End Sub

 

[jercsr]

Thomas,

Thank you very much. I'll give this a try. Would you recommend this method over writing a record to a table?

Jeremy

 

[ThomasLafferty]

Jeremy:
My assumption was that, since you needed to to keep the data and the form was unbound, you weren't gathering the data in a table. Keeping the information in a table would make accessing it for reporting purposes, etc, much easier! You would have to lock things down appropriately to prevent users from possibly seeing or modifying the table data. The code posted above will definitely work, but keeping things native to Access is probably an easier way to goin the long run. You could set up an event button which would append the control values to an existing table using a query. You could also set up a load event for the form giving users the opportunity on load to bring in values from the previous user by means of a query using the largest ID number (sort descending on UserDataID select top 1), or load the form as unbound.

In any case, the above code will keep the data outside your database since without looking at your code, who knows where it's being written to?

Hope this helps!

Tom