How to change Default Value on Form

This question relates to the default value for a field on a subform. I have
an unbound box that allows the user to type in a new default rate and in the
After Update event it changes the default value for the rate field. This
works fine until the user closes the form and it reverts back to the old
default value for the Rate field. I tried saving the form using the OnClose
event but that didn't work either. What do I need to do to make the new
default value for the rate field stick when the form is closed?

Sorry but after looking at it further, the problem seems to be saving the
subform. Is it possible to save the subform when closing the main form? I
can't get it to work by just doing:

DoCmd.Save acForm, "[Forms]![frmProjects]![frmHours].[Form]"

"larochy" wrote:

This question relates to the default value for a field on a subform. I have
an unbound box that allows the user to type in a new default rate and in the
After Update event it changes the default value for the rate field. This
works fine until the user closes the form and it reverts back to the old
default value for the Rate field. I tried saving the form using the OnClose
event but that didn't work either. What do I need to do to make the new
default value for the rate field stick when the form is closed?

Is it possible to save the subform
when closing the main form
[to save the new defaultvalue]?

No, I'm afraid not. If you must make the new default value
permanent, then you would have to close the main form and
open the subform in design view. This would not give a good
user experience.

However, there is an alternative strategy. You could use
the AfterUpdate event to not only update the DefaultValue
property, but also write the new default value to a table.
You could then use the Form_Open event of the subform to
read that value from the table into the DefaultValue
property. This doesn't make the DefaultValue permanent, but
it does create the same user experience.

Here's some sample code for the subform, which stores the
new default rate in the first record of the table
"tblDefaultRate" in the field "NewDefaultRate" (You need to
create this table.):

' Assumes reference to Microsoft DAO 3.6.

Private Const strcTableName As String = "tblDefaultRate"
Private Const strcFieldName As String = "NewDefaultRate"
Private Const strcTextBoxName As String = _
"txtNewDefaultRate"

Private Sub Form_Open(Cancel As Integer)

Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Dim strNewVal As Variant

On Error GoTo Error_Form_Open
Set objDB = CurrentDb()
Set objRS = objDB.OpenRecordset(strcTableName)

' Get new default value from first record:
If Not objRS.BOF And objRS.EOF Then
strNewVal = objRS.Fields(strcFieldName).Value
' Use quotation marks assuming string:
Me.txtRate.DefaultValue = """" & strNewVal & """"
End If

Exit_Form_Open:

If Not objRS Is Nothing Then
objRS.Close
Set objRS = Nothing
End If
Set objDB = Nothing
Exit Sub

Error_Form_Open:

' Assumes no error message required.
Debug.Print "Error occurred in Form_Open."
Resume Exit_Form_Open

End Sub

Private Sub txtNewDefaultRate_AfterUpdate()

Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Dim strSQL As String

On Error GoTo Error_txtNewRate_AfterUpdate

' Temporarily set new default rate
' (uses quotation marks, assuming string field):
Me.txtRate.DefaultValue = """" _
& Me.Controls(strcTextBoxName) & """"

' Permanently store new default rate:
Set objDB = CurrentDb()
Set objRS = objDB.OpenRecordset(strcTableName)
With objRS
If .BOF And .EOF Then
.AddNew
.Fields(strcFieldName) = _
Me.Controls(strcTextBoxName).Value
.Update
Else
.Edit
.Fields(strcFieldName).Value = _
Me.Controls(strcTextBoxName).Value
.Update
End If
End With

Exit_txtNewRate_AfterUpdate:

If Not objRS Is Nothing Then
objRS.Close
Set objRS = Nothing
End If
Set objDB = Nothing

Exit Sub

Error_txtNewRate_AfterUpdate:

' Assumes no error message required.
Debug.Print "Error occurred in txtNewRate_AfterUpdate."
Resume Exit_txtNewRate_AfterUpdate

End Sub


Incidentally, if you have a multi-user application (with a
front- and back-end), you may need to allow for the fact
that it would be possible for two users to simultaneously
update the table storing the default rate.


Geoff




"larochy" wrote in
message
...
Sorry but after looking at it further, the problem seems
to be saving the
subform. Is it possible to save the subform when closing
the main form? I
can't get it to work by just doing:

DoCmd.Save acForm,
"[Forms]![frmProjects]![frmHours].[Form]"

"larochy" wrote:

This question relates to the default value for a field on
a subform. I have
an unbound box that allows the user to type in a new
default rate and in the
After Update event it changes the default value for the
rate field. This
works fine until the user closes the form and it reverts
back to the old
default value for the Rate field. I tried saving the
form using the OnClose
event but that didn't work either. What do I need to do
to make the new
default value for the rate field stick when the form is
closed?

Apologies.
Code corrections:

For:
Dim strNewVal As Variant
Substitute:
Dim strNewVal As String
Change the above variable type as appropriate to the field
in question.

For:
If Not objRS.BOF And objRS.EOF Then
Substitute:
If Not (objRS.BOF And objRS.EOF) Then
or substitute:
If objRS.RecordCount 0 Then

Geoff




"GeoffG" wrote in message
...
Is it possible to save the subform
when closing the main form
[to save the new defaultvalue]?

No, I'm afraid not. If you must make the new default
value permanent, then you would have to close the main
form and open the subform in design view. This would not
give a good user experience.

However, there is an alternative strategy. You could use
the AfterUpdate event to not only update the DefaultValue
property, but also write the new default value to a table.
You could then use the Form_Open event of the subform to
read that value from the table into the DefaultValue
property. This doesn't make the DefaultValue permanent,
but it does create the same user experience.

Here's some sample code for the subform, which stores the
new default rate in the first record of the table
"tblDefaultRate" in the field "NewDefaultRate" (You need
to create this table.):

' Assumes reference to Microsoft DAO 3.6.

Private Const strcTableName As String = "tblDefaultRate"
Private Const strcFieldName As String = "NewDefaultRate"
Private Const strcTextBoxName As String = _
"txtNewDefaultRate"

Private Sub Form_Open(Cancel As Integer)

Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Dim strNewVal As Variant

On Error GoTo Error_Form_Open
Set objDB = CurrentDb()
Set objRS = objDB.OpenRecordset(strcTableName)

' Get new default value from first record:
If Not objRS.BOF And objRS.EOF Then
strNewVal = objRS.Fields(strcFieldName).Value
' Use quotation marks assuming string:
Me.txtRate.DefaultValue = """" & strNewVal & """"
End If

Exit_Form_Open:

If Not objRS Is Nothing Then
objRS.Close
Set objRS = Nothing
End If
Set objDB = Nothing
Exit Sub

Error_Form_Open:

' Assumes no error message required.
Debug.Print "Error occurred in Form_Open."
Resume Exit_Form_Open

End Sub

Private Sub txtNewDefaultRate_AfterUpdate()

Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Dim strSQL As String

On Error GoTo Error_txtNewRate_AfterUpdate

' Temporarily set new default rate
' (uses quotation marks, assuming string field):
Me.txtRate.DefaultValue = """" _
& Me.Controls(strcTextBoxName) & """"

' Permanently store new default rate:
Set objDB = CurrentDb()
Set objRS = objDB.OpenRecordset(strcTableName)
With objRS
If .BOF And .EOF Then
.AddNew
.Fields(strcFieldName) = _
Me.Controls(strcTextBoxName).Value
.Update
Else
.Edit
.Fields(strcFieldName).Value = _
Me.Controls(strcTextBoxName).Value
.Update
End If
End With

Exit_txtNewRate_AfterUpdate:

If Not objRS Is Nothing Then
objRS.Close
Set objRS = Nothing
End If
Set objDB = Nothing

Exit Sub

Error_txtNewRate_AfterUpdate:

' Assumes no error message required.
Debug.Print "Error occurred in txtNewRate_AfterUpdate."
Resume Exit_txtNewRate_AfterUpdate

End Sub


Incidentally, if you have a multi-user application (with a
front- and back-end), you may need to allow for the fact
that it would be possible for two users to simultaneously
update the table storing the default rate.


Geoff




"larochy" wrote in
message
...
Sorry but after looking at it further, the problem seems
to be saving the
subform. Is it possible to save the subform when closing
the main form? I
can't get it to work by just doing:

DoCmd.Save acForm,
"[Forms]![frmProjects]![frmHours].[Form]"

"larochy" wrote:

This question relates to the default value for a field
on a subform. I have
an unbound box that allows the user to type in a new
default rate and in the
After Update event it changes the default value for the
rate field. This
works fine until the user closes the form and it reverts
back to the old
default value for the Rate field. I tried saving the
form using the OnClose
event but that didn't work either. What do I need to do
to make the new
default value for the rate field stick when the form is
closed?