By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Cannot save Form changes with VBA

P: n/a
I have a form that is used to ID and then load Excel files into Access.
I use labels on the form to record which file was last loaded. That was
accomplished with a simple lblFileLoaded = strFullFileName. The label
would show the file name and when the form was closed and then opened
it would still show the name of the last file loaded. That preserves an
important piece of information.

Suddenly the form has stopped doing that. When you close the form and
reopen it the lbl is blank or has the name from the second prior session.

At someone's suggestion I tried to Save the form on exit. with
"DoCmd.Save acForm, "frmLoad". It gives an error msg of
"the object "frmLoad" is not loaded. The form is loaded and the
following test routine, found on the web, confirms that it is loaded.

Sub test()
Dim IsOpen As Boolean
IsOpen = adhIsFormOpen("frmLoad")
Debug.Print IsOpen

End Sub
Public Function adhIsFormOpen(strname As String) As Boolean
On Error Resume Next
Dim FIsOpen As Boolean
FIsOpen = CurrentProject.AllForms(strname).IsLoaded
adhIsFormOpen = (Err.Number = 0) And FIsOpen
Err.Clear
End Function

Anyway I really need to have these labels saved on exit

Any ideas?

Thx
Kevin
May 31 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Labels are controls with static captions, such as: "Name" and "DOB".
Textboxes are often associated with labels and in them we can be enter
or modify data like "Larry Linson" and "1909-04-01". TextBoxes may
have default values.
If the form and textbox are bound to a field in a table then the
changes made to the textbox are likely to be saved in that field in
that table and to appear next time we open the Form and navigate to
the record.

But there is nothing inherent or implicit in a form which permits the
saving of data entered into a textbox. If we open the form in design
mode and change the caption of a label or the default value of a
textbox and save the form then the new caption or default value will
be saved with the form.

I think neither a bound form, nor changes in the form's design is what
you want to do.

This may effect what you want to do (for a control named "ExcelPath"):

Private Sub Form_Load()
On Error Resume Next
ExcelPath.Value = _
CurrentProject.AllForms(Me.Name).Properties("Defau ltExcelPath").Value
End Sub
Private Sub Form_Unload(Cancel As Integer)
With CurrentProject.AllForms(Me.Name).Properties
.Add "DefaultExcelPath", ExcelPath.Value
.Item("DefaultExcelPath").Value = ExcelPath.Value
End With
End Sub

Of course, there will be no default value saved until the second time
the form is opened.
On May 31, 8:45*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:
I have a form that is used to ID and then load Excel files into Access.
I use labels on the form to record which file was last loaded. *That was
accomplished with a simple lblFileLoaded = strFullFileName. *The label
would show the file name and when the form was closed and then opened
it would still show the name of the last file loaded. That preserves an
important piece of information.

Suddenly the form has stopped doing that. *When you close the form and
reopen it the lbl is blank or has the name from the second prior session.

At someone's suggestion I tried to Save the form on exit. with
"DoCmd.Save acForm, "frmLoad". *It gives an error msg of
"the object "frmLoad" is not loaded. *The form is loaded and the
following test routine, found on the web, *confirms that it is loaded.

Sub test()
* Dim IsOpen As Boolean
* IsOpen = adhIsFormOpen("frmLoad")
* Debug.Print IsOpen

End Sub
Public Function adhIsFormOpen(strname As String) As Boolean
* * On Error Resume Next
* * Dim FIsOpen As Boolean
* * FIsOpen = CurrentProject.AllForms(strname).IsLoaded
* * adhIsFormOpen = (Err.Number = 0) And FIsOpen
* * Err.Clear
End Function

Anyway I really need to have these labels saved on exit

Any ideas?

Thx
Kevin
May 31 '08 #2

P: n/a
Thanks very much.

The form is unbound so I am not looking to fill text boxes with the data
as that would not preserve the data. I could certainly tie it all to a
table but
label.captions seemed an easy way to hold the few pieces of data.

I just now got the form to save using the reference me.name vice a literal,
"frmLoad". Got that style of reference from your code below.

Thanks again.
"lyle fairfield" <ly************@gmail.comwrote in message
news:8d**********************************@x35g2000 hsb.googlegroups.com...
Labels are controls with static captions, such as: "Name" and "DOB".
Textboxes are often associated with labels and in them we can be enter
or modify data like "Larry Linson" and "1909-04-01". TextBoxes may
have default values.
If the form and textbox are bound to a field in a table then the
changes made to the textbox are likely to be saved in that field in
that table and to appear next time we open the Form and navigate to
the record.

But there is nothing inherent or implicit in a form which permits the
saving of data entered into a textbox. If we open the form in design
mode and change the caption of a label or the default value of a
textbox and save the form then the new caption or default value will
be saved with the form.

I think neither a bound form, nor changes in the form's design is what
you want to do.

This may effect what you want to do (for a control named "ExcelPath"):

Private Sub Form_Load()
On Error Resume Next
ExcelPath.Value = _
CurrentProject.AllForms(Me.Name).Properties("Defau ltExcelPath").Value
End Sub
Private Sub Form_Unload(Cancel As Integer)
With CurrentProject.AllForms(Me.Name).Properties
.Add "DefaultExcelPath", ExcelPath.Value
.Item("DefaultExcelPath").Value = ExcelPath.Value
End With
End Sub

Of course, there will be no default value saved until the second time
the form is opened.
On May 31, 8:45 am, "KC-Mass" <connearneyATcomcastDOTnetwrote:
I have a form that is used to ID and then load Excel files into Access.
I use labels on the form to record which file was last loaded. That was
accomplished with a simple lblFileLoaded = strFullFileName. The label
would show the file name and when the form was closed and then opened
it would still show the name of the last file loaded. That preserves an
important piece of information.

Suddenly the form has stopped doing that. When you close the form and
reopen it the lbl is blank or has the name from the second prior session.

At someone's suggestion I tried to Save the form on exit. with
"DoCmd.Save acForm, "frmLoad". It gives an error msg of
"the object "frmLoad" is not loaded. The form is loaded and the
following test routine, found on the web, confirms that it is loaded.

Sub test()
Dim IsOpen As Boolean
IsOpen = adhIsFormOpen("frmLoad")
Debug.Print IsOpen

End Sub
Public Function adhIsFormOpen(strname As String) As Boolean
On Error Resume Next
Dim FIsOpen As Boolean
FIsOpen = CurrentProject.AllForms(strname).IsLoaded
adhIsFormOpen = (Err.Number = 0) And FIsOpen
Err.Clear
End Function

Anyway I really need to have these labels saved on exit

Any ideas?

Thx
Kevin

May 31 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.