[AccessD] Not in List and adding to a table

Change your code to this using ADO:

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

    Dim rs As Object
    Dim strSQL As String

    Set rs = New ADODB.Recordset
    
    ' Open a new recordset
    strSQL = "SELECT * FROM lkptbTechLead
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

    ' Prompt user to verify they wish to add new value.
    Response = MsgBox("Value is not in list. Add it?", vbYesNo)
 
    If Response = vbYes Then
        ' Insert New record into this RecordSet
        rs.AddNew
        rs.Fields("[Tech Lead]") = NewData
        rs.Update
        ' Record added, so cancel Access's default processing
        Response = acDataErrAdded
    Else
        ' Clear the name in the Combobox
        cboTechLead = Null
        ' We have handled the error, so tell Access not to put up
        ' its own default error.
        Response = acDataErrContinue
    End If
   
 


John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

Home: 253.588.2139
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
                and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Nancy Lytle
Sent: Wednesday, April 23, 2003 8:55 AM
To: Accessd
Subject: [AccessD] Not in List and adding to a table


I am trying to use the Not in List event of a combo box to both add the new
data to the control's rowsource and also to a table that consists of only
two fields, one of which is an autonumber primary key.

But everytime I run it I get a message box:  Enter Parameter Value, with the
value of the combo box listed with a text box to enter into.  I just hit
okay, it updated the value list, said it was appending a record, then said
it couldn't do it due to it creating a key violation. How can it create a
key violation if I am inserting new data?

I'm sure I'm overlooking something simple but its driving me crazy, more
than usual anyway.

Thanks,
Nancy L

CODE Currently being used:

Private Sub cboTechLead_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
    ' Return Control object that points to combo box.
    Set ctl = Me!cboTechLead

    ' Prompt user to verify they wish to add new value.
    If MsgBox("Value is not in list. Add it?", _
         vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data
        ' is being added.

    	  Response = acDataErrAdded
        ctl.RowSource = ctl.RowSource & ";" & NewData
        DoCmd.RunSQL "insert into lkptbTechLead([Tech Lead]) values (" &
NewData & ")"
    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

End Sub

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com