Add New Record for Combo Box
in [Forms]
|
From: Sprinks on 21 Apr 2010 16:51 For a Chemical Inventory database, there are the tables: Containers A bottle, jar, or other container of "stuff", and its qty and unit Substances A chemical, its CAS Registry #s, and Hazard Class ContainerSubstances The % of each Substance in a Container There is a Container main form with a ContainerSubstance subform. Since there are multiple fields in Substances, if the Substance does not exist, rather than using the NotInList event, I've provided a "New" button which opens the Substance form in Add and Dialog modes. A form BeforeUpdate procedure ensures that all required fields are filled: Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo Err_Handler Dim ctl As Control For Each ctl In Me.Controls If ctl.Tag = "R" Then If Nz(ctl) = 0 Then Beep MsgBox ctl.ControlSource & " is a required field. Please enter a value." ctl.SetFocus Cancel = True Exit For End If End If Next ctl Err_Exit: Exit Sub Err_Handler: Select Case Err.Number Case Else MsgBox Err.Description Resume Err_Exit End Select End Sub I've provided a "Save" button, with which I wish to requery the previous form's combo box to include the new record, select the new value from the combo box, and close the Substance form IF all of the fields are validated by the BeforeUpdate procedure. If the user has entered nothing, I want to simply close the form, redundantly doing what could also be done by a "Cancel" button. The following code, in combination with the BeforeUpdate displays a message correctly for blank required fields, but then closes the form regardless, not letting the user update the missing field(s). Private Sub cmdSave_Click() On Error GoTo Err_cmdSave_Click If Not Me.NewRecord Then With Forms![Containers].[sbfContainerSubstances].Form![cboSubstanceID] .Requery .Value = Me![txtSubstanceID] End With End If DoCmd.Close Exit_cmdSave_Click: Exit Sub Err_cmdSave_Click: MsgBox Err.Description Resume Exit_cmdSave_Click End Sub How can I avoid this? Can I test if BeforeUpdate has set Cancel to True within the scope of the Save event? Or is there an easier way? Thank you, Sprinks
From: Jeanette Cunningham on 21 Apr 2010 20:35 The usual way is to code the save button like this: Private Sub cmdSave_Click() If Me.Dirty = True Then Me.Dirty = False End If DoCmd.Close End Sub The above code forces the form's before update event to run if the form is dirty. You can put the code to requery the calling form on the unload event. If Not IsNull(Me![txtSubstanceID]) Then With Forms![Containers].[sbfContainerSubstances].Form![cboSubstanceID] .Requery .Value = Me![txtSubstanceID] End With End If Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Sprinks" <Sprinks(a)discussions.microsoft.com> wrote in message news:CAABE40F-5B4C-4EC6-B24B-79635944130E(a)microsoft.com... > For a Chemical Inventory database, there are the tables: > > Containers A bottle, jar, or other container of > "stuff", and its qty and unit > Substances A chemical, its CAS Registry #s, and Hazard > Class > ContainerSubstances The % of each Substance in a Container > > There is a Container main form with a ContainerSubstance subform. Since > there are multiple fields in Substances, if the Substance does not exist, > rather than using the NotInList event, I've provided a "New" button which > opens the Substance form in Add and Dialog modes. > > A form BeforeUpdate procedure ensures that all required fields are filled: > > Private Sub Form_BeforeUpdate(Cancel As Integer) > On Error GoTo Err_Handler > Dim ctl As Control > > For Each ctl In Me.Controls > If ctl.Tag = "R" Then > If Nz(ctl) = 0 Then > Beep > MsgBox ctl.ControlSource & " is a required field. Please > enter a value." > ctl.SetFocus > Cancel = True > Exit For > End If > End If > Next ctl > > Err_Exit: > Exit Sub > > Err_Handler: > Select Case Err.Number > Case Else > MsgBox Err.Description > Resume Err_Exit > End Select > End Sub > > I've provided a "Save" button, with which I wish to requery the previous > form's combo box to include the new record, select the new value from the > combo box, and close the Substance form IF all of the fields are validated > by > the BeforeUpdate procedure. > > If the user has entered nothing, I want to simply close the form, > redundantly doing what could also be done by a "Cancel" button. > > The following code, in combination with the BeforeUpdate displays a > message > correctly for blank required fields, but then closes the form regardless, > not > letting the user update the missing field(s). > > Private Sub cmdSave_Click() > On Error GoTo Err_cmdSave_Click > > If Not Me.NewRecord Then > With > Forms![Containers].[sbfContainerSubstances].Form![cboSubstanceID] > .Requery > .Value = Me![txtSubstanceID] > End With > End If > DoCmd.Close > > > Exit_cmdSave_Click: > Exit Sub > > Err_cmdSave_Click: > MsgBox Err.Description > Resume Exit_cmdSave_Click > > End Sub > > How can I avoid this? Can I test if BeforeUpdate has set Cancel to True > within the scope of the Save event? Or is there an easier way? > > Thank you, > Sprinks
From: Sprinks on 21 Apr 2010 22:46 Thank you, Jeannette. It works and makes sense. The Me.Dirty = False triggers a "No Current Record" error, the MsgBox of which shows up after the BeforeUpdate runs. The little research I did on this error said that it was triggered by an empty database, but the database has records and when it was triggered, other fields were entered in the current record. It also indicated that Access could not trap for this error, but I had already done so and it performs correctly, so perhaps I am using a newer version than that to which the post applied. In any case, I'm very happy to have a strategy that works, since it lets the user cleanly drill down to underlying tables; in this case, each Product has a qty of one or more Containers, so this strategy can enable a new Product record to be entered smoothly, even if there is no existing Substance record. Thanks again. Sprinks
From: Sprinks on 21 Apr 2010 23:01 Jeanette, I spoke too soon. I'm having a similar problem with the companion Cancel button, for which I'd like to close the form without adding any records whether the user has 1) entered all required fields; 2) entered 1 or more fields but not all required fields; and 3) the user has not entered anything. In the second case, I'd want to bypass any "required" messages from the BeforeUpdate procedure. Can you help? Thank you. Sprinks
From: Jeanette Cunningham on 21 Apr 2010 23:26
If Me.Dirty = True Then Me.Undo End If DoCmd.Close acForm, Me.Name Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Sprinks" <Sprinks(a)discussions.microsoft.com> wrote in message news:E3798E59-448E-4C39-97C4-0144F681304E(a)microsoft.com... > Jeanette, > > I spoke too soon. I'm having a similar problem with the companion Cancel > button, for which I'd like to close the form without adding any records > whether the user has 1) entered all required fields; 2) entered 1 or more > fields but not all required fields; and 3) the user has not entered > anything. > > In the second case, I'd want to bypass any "required" messages from the > BeforeUpdate procedure. > > Can you help? > > Thank you. > Sprinks |