Not_In_List Events...
You want provide Users with a list of values to be entered. When they make a selection that is not in the list you have two choices, deny the new data or allow the new entry. Either way, no one likes the Access generated error when the value is not in the list.
You want provide Users with a list of values to be entered. When they make a selection that is not in the list you have two choices, deny the new data or allow the new entry. Either way, no one likes the Access generated error when the value is not in the list.
The difficult I do immediately, the impossible takes a little bit longer.
Private Sub cboAssociateID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboAssociateID_NotInList
Dim intAnswer As Integer
intAnswer = MsgBox("Would you like to add this Associate to the list?", vbYesNo + vbQuestion, "Not in List")
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "sfrAddNewAssociate", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If
Exit_cboAssociateID_NotInList:
Exit Sub
Err_cboAssociateID_NotInList:
MsgBox Err.Description
Resume Exit_cboAssociateID_NotInList
End Sub
On Error GoTo Err_cboAssociateID_NotInList
Dim intAnswer As Integer
intAnswer = MsgBox("Would you like to add this Associate to the list?", vbYesNo + vbQuestion, "Not in List")
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "sfrAddNewAssociate", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If
Exit_cboAssociateID_NotInList:
Exit Sub
Err_cboAssociateID_NotInList:
MsgBox Err.Description
Resume Exit_cboAssociateID_NotInList
End Sub
Hmm, but suppose we want to add a value that has two parts like First Name Last Name? No need to exit where you are, we'll just handle that with the Not_In_List event as well...
Design
| Tips (Main) |
| Home |
Private Sub cboSelect_NotInList(NewData As String, Response As Integer)
MsgBox NewData & " is not in the list, " & vbCrLf & _
"please choose an item from the list." _
, vbExclamation, "Not in List"
Me.cboSelect.Undo
Response = acDataErrContinue
End Sub
MsgBox NewData & " is not in the list, " & vbCrLf & _
"please choose an item from the list." _
, vbExclamation, "Not in List"
Me.cboSelect.Undo
Response = acDataErrContinue
End Sub
Private Sub cboTemperID_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
strSQL = "Add '" & NewData & "' as new Temper?"
If MsgBox(strSQL, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
strSQL = "INSERT INTO tlkpTemperCodes ( tcTemperID ) " & _
"SELECT """ & NewData & """ AS TemperID;"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If
End Sub
Dim strSQL As String
strSQL = "Add '" & NewData & "' as new Temper?"
If MsgBox(strSQL, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
strSQL = "INSERT INTO tlkpTemperCodes ( tcTemperID ) " & _
"SELECT """ & NewData & """ AS TemperID;"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If
End Sub
With a little code...
The above turns into...
If you want to accept new values...
