[AccessD] Not in List and adding to a table

Thanks alot that really helped, I ended up having to created 3 fields in the
table, the name, the project ID and the date it was entered or changed. Plus
I added the ctl.rowsource code to update the value list I was using.
Many thank also to John Ruff for his ADO solution.  I think the problem had
to do with the primary key.

Nancy

Here is the code I used:
Private Sub cboTechLead_NotInList(NewData As String, Response As Integer)
On Error GoTo eh
Dim ctl As Control
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMake As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [CR ID],[Tech Lead], EntryDate from
lkptbTechLead")
strMake = Me.[CR ID]

    ' Return Control object that points to combo box.
    Set ctl = Me.cboTechLead
    ' Prompt user to verify they wish to add new value.
    If MsgBox("That name 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
        With rs
            .AddNew
            ![CR ID] = strMake
            ![Tech Lead] = NewData
            ![EntryDate] = Now()
            .Update
        End With

    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

ex:  Set db = Nothing
     Set rs = Nothing
     Exit Sub
eh:  MsgBox Err.Description
     GoTo ex
End Sub

-----Original Message-----
From: Liz Doering [mailto:liz at symphonyinfo.com]
Sent: Wednesday, April 23, 2003 12:53 PM
To: nancy.lytle at auatac.com
Subject: RE: [AccessD] Not in List and adding to a table


Nancy,

Here's the whole procedure that I use when I do this.  You'll see that I
open the recordset I want to add to, rather than using an append query.

Hope this helps!

Liz

Private Sub Model_NotInList(NewData As String, Response As Integer)
On Error GoTo eh
Dim ctl As Control
Dim db As Database
Dim rs As Recordset
Dim strMake As String
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from MakeModel")
strMake = Me.Make

    ' Return Control object that points to combo box.
    Set ctl = Me.Model
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Make and Model are not in list. Add them?", _
         vbOKCancel) = vbOK Then
        ' Set Response argument to indicate that data
        ' is being added.
        Response = acDataErrAdded
        With rs
            .AddNew
            !Make = strMake
            !Model = NewData
            .Update
        End With

    Else
    ' If user chooses Cancel, suppress error message
    ' and undo changes.
        Response = acDataErrContinue
        ctl.Undo
    End If

ex:  Set db = Nothing
     Set rs = Nothing
     Exit Sub
eh:  MsgBox Err.description
     GoTo ex
End Sub

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

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