[AccessD] Not in List and adding to a table

That didn't do the trick, the error seems to be happening at the line where
I am trying to insert into the table.
But thanks for the reply.
Nancy

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Liz Doering
Sent: Wednesday, April 23, 2003 12:36 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Not in List and adding to a table


Nancy,

This line:          ctl.RowSource = ctl.RowSource & ";" & NewData

is definitely causing you problems, as it is trying to set the rowsource of
your combo box to include whatever was typed in there at the end of the
query I'm believing you are using for a rowsource. (ex:  Select * from
MyTable; Joe).

I recognize the line as being straight from the Help example; I think
removing it will clear up most, if not all, of your problem.

Liz Doering
Symphony Information Services
liz at symphonyinfo.com
www.symphonyinfo.com





-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Nancy Lytle
Sent: Wednesday, April 23, 2003 10: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


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