notinlist problems

  • Thread starter Thread starter kahn_williams via AccessMonster.com
  • Start date Start date
K

kahn_williams via AccessMonster.com

Will somebody please help me out.
I have a form with a combo box (Combo8) that updates another combo box
(Order) and both are based on a query. The boxes are already synchronized.
Combo8 contains CustomerID and Order contains OrderID. I'm trying to add new
records (Customer Information) through this form. I've tried several codes
from online and none have worked. Any ideas?
 
If CustomerID is an AutoNumber, you will not be able to use NotInList to add
a new record.

Instead, use the DblClick of the combo (or put a button beside the combo if
you prefer) to open the customers form to a new record, and in the
AfterUpdate event of the *form*, Requery the combo so it hears about the new
record.
 
I tried it, but I'm really new to Access and don't know what I'm doing wrong.
My code is pasted below.
Please take a look at it. CustomerID is a Number, not autonumber.



Private Sub Combo8_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_Combo8_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)

' Ask the user to input a new Customer ID.
Msg = "Please enter a unique " & vbCr & "Customer ID."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("CustomerID", dbInteger, NewID)
' If the NewID already exists, ask for another new unique
' CustomerID
Do Until Rs.NoMatch
NewID = InputBox("Customer ID " & NewID & " already exists." & _
vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("CustomerID", dbInteger, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the CustomerID field.
Rs![CustomerID] = NewID
' Assign the NewData argument to the CompanyName field.
Rs![CustomerID] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_Combo8_NotInList:
Exit Sub
Err_Combo8_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue


End Sub



Allen said:
If CustomerID is an AutoNumber, you will not be able to use NotInList to add
a new record.

Instead, use the DblClick of the combo (or put a button beside the combo if
you prefer) to open the customers form to a new record, and in the
AfterUpdate event of the *form*, Requery the combo so it hears about the new
record.
Will somebody please help me out.
I have a form with a combo box (Combo8) that updates another combo box
Click to expand...
[quoted text clipped - 4 lines]
codes
from online and none have worked. Any ideas?
Click to expand...
Click to expand...
 
This won't work unless the CustomerID is the bound column of the combo, and
the combo is *showing* the number (i.e. it's not a zero-width column.)

Then when the user enters a new number into the combo (not a new name), you
can assign the NewData to the field:
Rs![CustomerID] = CLng(NewData)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

kahn_williams via AccessMonster.com said:
I tried it, but I'm really new to Access and don't know what I'm doing
wrong.
My code is pasted below.
Please take a look at it. CustomerID is a Number, not autonumber.



Private Sub Combo8_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_Combo8_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)

' Ask the user to input a new Customer ID.
Msg = "Please enter a unique " & vbCr & "Customer ID."
NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("CustomerID", dbInteger, NewID)
' If the NewID already exists, ask for another new unique
' CustomerID
Do Until Rs.NoMatch
NewID = InputBox("Customer ID " & NewID & " already exists." & _
vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("CustomerID", dbInteger, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the CustomerID field.
Rs![CustomerID] = NewID
' Assign the NewData argument to the CompanyName field.
Rs![CustomerID] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_Combo8_NotInList:
Exit Sub
Err_Combo8_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue


End Sub



Allen said:
If CustomerID is an AutoNumber, you will not be able to use NotInList to
add
a new record.

Instead, use the DblClick of the combo (or put a button beside the combo
if
you prefer) to open the customers form to a new record, and in the
AfterUpdate event of the *form*, Requery the combo so it hears about the
new
record.
Will somebody please help me out.
I have a form with a combo box (Combo8) that updates another combo box
Click to expand...
[quoted text clipped - 4 lines]
codes
from online and none have worked. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
I added the code to the loop in the end but it didn't work. The code below
works in taking the data. It doesn't open the form to enter the data, nor
does it update the combo box. It does update the Customers table.



Private Sub Combo8_DblClick(Cancel As Integer)
DoCmd.OpenForm "Info", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=NewData

End Sub

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

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_Combo8_NotInList

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, set the Response
' argument to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again."
Else
' If the user chose to add a new customer, open a recordset
' using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Customers", dbOpenDynaset)

' Ask the user to input a new Customer ID.
Msg = "Please enter a unique " & vbCr & "Customer ID."

NewID = InputBox(Msg)
Rs.FindFirst BuildCriteria("CustomerID", dbInteger, NewID)
' If the NewID already exists, ask for another new unique
' CustomerID
Do Until Rs.NoMatch
NewID = InputBox("Customer ID " & NewID & " already exists." & _
vbCr & vbCr & Msg, NewID & " Already Exists")
Rs.FindFirst BuildCriteria("CustomerID", dbInteger, NewID)
Loop
' Create a new record.
Rs.AddNew
' Assign the NewID to the CustomerID field.


'Rs![CustomerID] = NewID
' Assign the NewData argument to the CompanyName field.
'Rs![CustomerID] = NewData
' Save the record.
Rs.Update

' Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_Combo8_NotInList:
Exit Sub
Err_Combo8_NotInList:
' An unexpected error occurred, display the normal error message.
MsgBox Err.Description
' Set the Response argument to suppress an error message and undo
' changes.
Response = acDataErrContinue


End Sub


Private Sub Form_AfterUpdate()
Me.Combo8.Requery
End Sub

Private Sub order_AfterUpdate()
' Find the record that matches the control.
Dim Rs As Object

Set Rs = Me.Recordset.Clone
Rs.FindFirst "[OrderID] = " & Str(Nz(Me![Order], 0))
If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
Me.Order.Requery
End Sub





Allen said:
This won't work unless the CustomerID is the bound column of the combo, and
the combo is *showing* the number (i.e. it's not a zero-width column.)

Then when the user enters a new number into the combo (not a new name), you
can assign the NewData to the field:
Rs![CustomerID] = CLng(NewData)
I tried it, but I'm really new to Access and don't know what I'm doing
wrong.
Click to expand...
[quoted text clipped - 79 lines]
Click to expand...
 
The CustomerID is the bound column and it shows the original ID's, not the
ones I'm adding.

Allen said:
This won't work unless the CustomerID is the bound column of the combo, and
the combo is *showing* the number (i.e. it's not a zero-width column.)

Then when the user enters a new number into the combo (not a new name), you
can assign the NewData to the field:
Rs![CustomerID] = CLng(NewData)
I tried it, but I'm really new to Access and don't know what I'm doing
wrong.
Click to expand...
[quoted text clipped - 79 lines]
Click to expand...
 
Back
Top