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
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 said:
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?
