Full Version: inserting new record OnNotInList
UtterAccess Forums > Microsoft® Access > Access Forms
killerwhale65
hi,
I use the following code to insert a new record when i enter an unknown value in a combobox. After that the form should display my freshly created record.
CODE
Private Sub Combo20_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rs As DAO.Recordset, lngNewStockID As Long
On Error GoTo Err_ICAO_NotInList
        If NewData = "" Then Exit Sub
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblStock", dbOpenDynaset)
        ' Create a new record.
        rs.AddNew
        rs![SKU] = NewData
        ' Save the record.
        rs.Update
              
        lngNewStockID = rs.Fields("StockID")
        rs.Close
        Set rs = Nothing
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[StockID] = " & lngNewStockID
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
        
        
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
Exit_ICAO_NotInList:
       Exit Sub
Err_ICAO_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

But upon entering a new value i get this error:
CODE
The changes you requested to the table were not successfull because they would create duplicate values in the index, primary key, or relationship. Change the data in the field that contains duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

What is wrong?
RuralGuy
Is "tblStock" the query/table the main form is bound to? If so, it is very bad practice to modify the bound table other than through the controls on the form. Using a recordset causes errors as you have discovered. Normally a ComboBox has a Row Source of another table/query other than the bound table/query of the main form so that you can insert values from the other table/query into the bound table/query. The NotInList is best used to add records to this other table/query. What are you trying to do with this ComboBox?
killerwhale65
hi,
I use the combo to select an ID, and then the appropriate record should be displayed in the form.
If the ID does not exist yet, it should be created.
RuralGuy
Aren't you using an AutoNumber as an ID? Then just adding a new record would create a new ID. My original admonition still stands. Sorry.
killerwhale65
i have an autonumber as PK, but thats not the ID i enter in the combobox.
killerwhale65
other thing: i do not know in front if an ID i will enter will be already in the box. So i cannot know in front if i will have to make a new record. Thats why i just enter it in the combo and if its not there then it should be created.
RuralGuy
Without knowing more about your form and tables it is difficult to give more specific advice. In any event, the FindFirst code should be in the AfterUpdate event of the ComboBox and *not* the NotInList code.
dallr
Set the "Limit To List" property of the combo to .. Yes.

Now .. here is an example of what you need for the "On Not In List" event of the combobox ...
Code:
CODE
          
        
Private Sub YourComboName_NotInList(NewData As String, Response As Integer)        
        
On Error GoTo Err_YourComboName_NotInList        
        
Dim strMsg As String        
        
Dim ctl As Control        
        
Dim strSQL As String        
        

et ctl = Me.YourComboName
strMsg = NewData & " is not in list. Add it?"
' Prompt user to verify they wish to add new value
If MsgBox(strMsg, vbYesNo) = vbYes Then
  ' Set Response argument to indicate that data is being added
  Response = acDataErrAdded
  ' Add string in NewData argument to products table
  strSQL = "INSERT INTO tblYourTable (YourFieldName) SELECT '" & NewData & "'"
  CurrentDb.Execute strSQL, dbFailOnError
Else
  ' If user chooses Cancel, suppress error message and undo changes
  Response = acDataErrContinue
  ctl.Undo
End If

Exit_YourComboName_NotInList:        
        
  Exit Sub        
        
        
        
Err_YourComboName_NotInList:        
        
  MsgBox Err.Number & ":" & Err.Description        
        
  Resume Exit_YourComboName_NotInList        
        
        
        
End Sub

Try this code i got it from Utteraccess but i cannot remember who originally posted it.
killerwhale65
>
Oalready have another findfirst in the afterupdate, that will display the record in case the item i enter in the combobox already exists:
CODE
Private Sub Combo20_AfterUpdate()
' Find the record that matches the control.
    
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[StockID] = " & Str(Nz(Me![Combo20], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
killerwhale65
>
This works, but it also does not display the newly entered record, and thats what i need.
RuralGuy
I don't think you understand what events are taking place. The AfterUpdate event occurs after the NotInList
event. Using acDataErrAdded in your NotInList code causes the ComboBox to Requery right after the NotInList
event and therefore if you have added the NewData to the underlying query/table of the ComboBox, the
NewData will be available for the AfterUpdate event to find. In your case, when you have NewData you have
been doing two FindFirst each time. Your FindFirst code *could* look like:
rs.FindFirst "[StockID] = " & Me!Combo20.Column(0)
Edited by: RuralGuy on Wed Dec 28 10:58:05 EST 2005.
killerwhale65
where should i put this? In the notinlist or afterupdate? In the afterupdate it doesnt seem to work.
lso, if the acDataErr causes the error, can i just delete it?
RuralGuy
If you are talking to me then comment out the FindFirst code in the NotInList event
and just use the AfterUpdate event. It is important you set Response to either
acDataErrAdded or acDataErrContinue in the NotInList event so Access will know what to do.
Edited by: RuralGuy on Wed Dec 28 11:06:29 EST 2005.
killerwhale65
still not working. The record is added but it always moves to the first record instead of displaying the new one.
For the record, once again my codes:
CODE
  Private Sub Combo20_AfterUpdate()
' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
'    rs.FindFirst "[StockID] = " & Str(Nz(Me![Combo20], 0))
    rs.FindFirst "[StockID] = " & Me!Combo20.Column(0)
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

CODE
Private Sub Combo20_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rs As DAO.Recordset, lngNewStockID As Long
On Error GoTo Err_ICAO_NotInList
        If NewData = "" Then Exit Sub
        ' If the user chose to add a new customer, open a recordset
        ' using the Customers table.
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblStock", dbOpenDynaset)
        ' Create a new record.
        rs.AddNew
        rs![SKU] = NewData
        ' Save the record.
        rs.Update
'        lngNewStockID = rs.Fields("StockID")
'        rs.Close
'        Set rs = Nothing
'        Set rs = Me.Recordset.Clone
'        rs.FindFirst "[StockID] = " & lngNewStockID
'        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded
Exit_ICAO_NotInList:
       Exit Sub
Err_ICAO_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
RuralGuy
Let's see why the AfterUpdate code is not working properly shall we? Add this code:
s.FindFirst "[StockID] = " & Me!Combo20.Column(0)
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Could NOT find [" & Me!Combo20.Column(0) & "]"
End If
and you commented out a little too much:
' lngNewStockID = rs.Fields("StockID")
' rs.Close <-- Uncomment this line
' Set rs = Nothing <-- Uncomment this line
killerwhale65
ok, the messagebox does not appear, and it still moves to the first record upon entering a new value. Entering a known value works fine.
RuralGuy
If Not rs.EOF Then
MsgBox "Found [" & Me!Combo20.Column(0) & "]"
Me.Bookmark = rs.Bookmark
Else
MsgBox "Could NOT find [" & Me!Combo20.Column(0) & "]"
End If
killerwhale65
it says "Found [80]" and then moves to the first record.
pon entering a known value it says "Found [4]" and then moves to the correct record.
RuralGuy
Let's try requerying the recordset for the form.
Private Sub Combo20_AfterUpdate()
' Find the record that matches the control.
Me.Requery
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[StockID] = " & Me!Combo20.Column(0)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
killerwhale65
PERFECT thanks!!!
RuralGuy
Outstanding!! Don't forget to remove all of our diagnostic code.
killerwhale65
rgr will do.
Thanks again.
dallr
I dont understand what do you mean it works but it does not show the new record? The whole purpose of the code is to insert new information in the combo box. It is not necessarily going to create a new record. Maybe you need to setfocus (Me.YourControlName.Setfocus) to another control in the code i suggest before a new record is created. But for now I dont what to jump to any conclusions.
How lets start from the top. What exactually do u want to do? (explain in detail). Or post the database for us to look at.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.