If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
How to change Default Value on Form
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? |
#3
|
|||
|
|||
How to change Default Value on Form
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? |
#4
|
|||
|
|||
How to change Default Value on Form
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? |
Thread Tools | |
Display Modes | |
|
|