NotInList

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
I

Ivor Williams

I have two forms, one has a combo box from which I choose a customer name.
The second form is to enter new customer information. If the name entered in
the combo box is not in the list, I want to have the second form open so the
customer information can be entered and when the second form is closed, have
the customer name entered in the combo box of the first form. Can someone
help me with some NotInList event code, please?

Ivor
 
Ivor

Check Access HELP for the NotInList example, and the "return" codes used to
indicate that a new value has been added. This is the way to get the newly
created value back into the combo box.

From (questionable) memory, the constant is something like:

acDataErrAdded

Good luck!

Jeff Boyce
<Access MVP>
 
Ivor,



=========================================================
'This sub adds one field, but could be expanded to more fields

Private Sub City_NotInList(NewData As String, Response As Integer)

'Suppress the default error message.

Response = acDataErrContinue

' Prompt user to verify if they wish to add a new value.

If MsgBox("The city of " & NewData & " is not in list. Add it?",
vbYesNo) = vbYes Then

'Open a recordset of the Cities Table.

Dim db As Database
Dim rstCity As Recordset
Dim sqlCities As String
Set db = CurrentDb()
sqlCities = "Select * From Cities"
Set rstCity = db.OpenRecordset(sqlCities, dbOpenDynaset)
'Add a new City with the value that is stored in the variable NewData.

rstCity.AddNew
rstCity![City] = NewData
rstCity.Update
'Inform the combo box that the desired item has been added to the list.
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
rstCity.Close 'Close the recordset
End If

End Sub



=========================================================
'This sub uses a form to add data

Private Sub cboSalesRepCode_NotInList( _
NewData As String, Response As Integer)

Dim intResponse As Integer
Dim strMsg As String

strMsg = NewData & " isn't an existing sales rep. Add a new sales rep?"
intResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Sales Rep")
Select Case intResponse

Case vbYes
DoCmd.OpenForm "frmSalesReps", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData
' Stop here and wait until the form goes away.
If dtxIsLoaded("frmSalesReps") Then
Me!cboSalesRepCode = Forms!frmSalesReps![txtSalesRepCode]
Response = acDataErrAdded
DoCmd.Close acForm, "frmSalesReps"
Else
Response = acDataErrContinue
End If

Case vbNo
Response = acDataErrContinue
End Select

End Sub



HTH

Steve
 
Back
Top