B
BruceM
I have a database for keeping track of equipment. It includes the tables
tblEquip and tblProcess, and the junction table tblEquipProcess. Each piece
of equipment may be associated with many processes and vice versa, thus the
junction table.
The main form is based on tblEquip. The subform is based on
tblEquipProcess. The subform contains a combo box based on tblProcess. It
could happen that the user wants a new process (one that is not yet in
tblProcess). One way the process could be added is with the combo box Not
In List event:
Private Sub cboProcessID_NotInList(NewData As String, Response As Integer)
If MsgBox("New Process?, vbYesNo) = vbYes Then
Response = acDataErrAdded
strNewProc = NewData
Me.Undo
DoCmd.OpenForm "frmProcess", , , , , acDialog
Else
Response = acDataErrContinue
Me.Undo
Me.txtHidden.SetFocus
End If
End Sub
strNewProc is defined as a string in a standard module. The value is passed
to frmProcess:
Private Sub Form_Load()
On Error GoTo ProcErr
Me.Recordset.AddNew
If strNewProc <> "" Then
Me.txtProcessID = strNewProc
Me.cboProcessType.SetFocus
End If
End Sub
I don't know if that is the best way to pass the value. I open frmProcess
because another field is needed to complete the record; otherwise I expect I
could use some sort of Recordset AddNew code to add NewData as a new record
in tblProcess.
The reason for setting the focus to txtHidden (it could have been another
control) is that the user is presented with the cboProcess drop-down list
otherwise.
If the user clicks Yes at the New Process message box, frmProcess opens with
the Not In List value (the text the user tried to add to cboProcess) in
txtProcessID and the cursor in cboProcessType. If the user selects a
ProcessType from cboProcessType the record is updated, and the new process
ends up in cboProcessID back on the subform. If the user does not select a
value, Before Update validation code prompts the user to select a
ProcessType. If the user selects a ProcessType, all is well, just as if the
user had selected the ProcessType without being prompted..
This is part of the validation code in frmProcess:
If MsgBox("Process Type is needed", vbOKCancel) = vbCancel Then
Me.Undo
End If
If the user selects Cancel the new record is not added. That is as it
should be. The annoyance occurs in that the Not In List event back at
cboProcess on the subform generates the standard message "The text you
entered isn't an item on the list".
The first Me.Undo (after strNewProc = NewData) was an attempt to clear the
record that includes the NotInList value, but it didn't help.
The reason I need to go through all of this is that in my experience if a
user can click a button, sooner or later they will do so. Somebody could
say "Yes" to adding a new process, then realize that isn't what they meant
to do. It would be good if users always proceed the right way, but they
don't. I need to allow for that. The user needs to be able to close
frmProcess without adding a record.
tblEquip and tblProcess, and the junction table tblEquipProcess. Each piece
of equipment may be associated with many processes and vice versa, thus the
junction table.
The main form is based on tblEquip. The subform is based on
tblEquipProcess. The subform contains a combo box based on tblProcess. It
could happen that the user wants a new process (one that is not yet in
tblProcess). One way the process could be added is with the combo box Not
In List event:
Private Sub cboProcessID_NotInList(NewData As String, Response As Integer)
If MsgBox("New Process?, vbYesNo) = vbYes Then
Response = acDataErrAdded
strNewProc = NewData
Me.Undo
DoCmd.OpenForm "frmProcess", , , , , acDialog
Else
Response = acDataErrContinue
Me.Undo
Me.txtHidden.SetFocus
End If
End Sub
strNewProc is defined as a string in a standard module. The value is passed
to frmProcess:
Private Sub Form_Load()
On Error GoTo ProcErr
Me.Recordset.AddNew
If strNewProc <> "" Then
Me.txtProcessID = strNewProc
Me.cboProcessType.SetFocus
End If
End Sub
I don't know if that is the best way to pass the value. I open frmProcess
because another field is needed to complete the record; otherwise I expect I
could use some sort of Recordset AddNew code to add NewData as a new record
in tblProcess.
The reason for setting the focus to txtHidden (it could have been another
control) is that the user is presented with the cboProcess drop-down list
otherwise.
If the user clicks Yes at the New Process message box, frmProcess opens with
the Not In List value (the text the user tried to add to cboProcess) in
txtProcessID and the cursor in cboProcessType. If the user selects a
ProcessType from cboProcessType the record is updated, and the new process
ends up in cboProcessID back on the subform. If the user does not select a
value, Before Update validation code prompts the user to select a
ProcessType. If the user selects a ProcessType, all is well, just as if the
user had selected the ProcessType without being prompted..
This is part of the validation code in frmProcess:
If MsgBox("Process Type is needed", vbOKCancel) = vbCancel Then
Me.Undo
End If
If the user selects Cancel the new record is not added. That is as it
should be. The annoyance occurs in that the Not In List event back at
cboProcess on the subform generates the standard message "The text you
entered isn't an item on the list".
The first Me.Undo (after strNewProc = NewData) was an attempt to clear the
record that includes the NotInList value, but it didn't help.
The reason I need to go through all of this is that in my experience if a
user can click a button, sooner or later they will do so. Somebody could
say "Yes" to adding a new process, then realize that isn't what they meant
to do. It would be good if users always proceed the right way, but they
don't. I need to allow for that. The user needs to be able to close
frmProcess without adding a record.