Add New Record for Combo Box

  • Thread starter Thread starter Sprinks
  • Start date Start date
S

Sprinks

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
 
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
 
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
 
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
 
If Me.Dirty = True Then
Me.Undo
End If
DoCmd.Close acForm, Me.Name


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Back
Top