NotInList event troubles

DaDeViL · August 11th, 2005, 10:41 AM

Hello, I can't seem to get my NotInList event working. If I enter a value that doesnt exist in a table:

it asks me if I would like to add the new value,
I click YES,
and then get the message that the new data has been added
I click OK
then I see the combo box is expanded ( the values in the combo box still doesnt have the new value)
I try clicking on another control
prompts me again asking if I want to enter the new value(starting the loop all over again)

To get out of the loop I am forced to select a value that is on the list and move on.

When I check my table to see if it has been updated, the new value exists, the combo box is just not requeried or updated like it is supposed to be.

Here is the code:

Code:

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

 Response = Access.acDataErrContinue
    If MsgBox("The route " & Chr(34) & NewData & Chr(34) & " is not in the list of available routes. " & Chr(10) & _
                            "Would you like to enter a new route?", vbYesNo + vbQuestion, "Insert Route?") = vbYes Then



        'The user clicked yes, so add the route
        Dim strInsertRoute, strRouteNumberText As String
        Dim ResultQuery As Variant

        Dim intRouteNumberID As Integer

        strInsertRoute = "INSERT INTO ROUTE_NUMBER(ROUTE_NUMBER) VALUES('" & NewData & "');"

        ResultQuery = "SELECT @@Identity FROM ROUTE_NUMBER"

        ' call sub to run the queries


            ' Create Recordset --------------------------------
            Dim rlRecordSet As New ADODB.Recordset            '|
            ' -------------------------------------------------

            Call GetRecords(ResultQuery, rlRecordSet, strInsertRoute)

            ' Close Recordset --------------------------------
            rlRecordSet.Close                                '|
            Set rlRecordSet = Nothing                        '|
            ' ------------------------------------------------



        MsgBox "Route " & Chr(34) & NewData & Chr(34) & " has been added to the list.", vbOKOnly + vbExclamation _
                , "Route Added!"

        Response = Access.acDataErrAdded

    Else
        'The user clicked no, so undo what they entered

        Response = Access.acDataErrContinue

    End If

    If Err Then
            MsgBox "An error occurred. Please try again."
            Response = Access.acDataErrContinue
    Else
            Response = Access.acDataErrAdded
            Response = Access.acDataErrContinue
    End If


End Sub

Thanks!

ÃaÃeViÂ£

sdilucca · August 11th, 2005, 10:45 AM

you should requery your controls if you change the data behind them. are you doing that?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

DaDeViL · August 11th, 2005, 10:54 AM

Yes, i tried a requery in the AfterUpdate, but it did not work..any idea what event I should put a requery in?

Thanks

ÃaÃeViÂ£

sdilucca · August 11th, 2005, 12:32 PM

show me your GetRecords code

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

DaDeViL · August 11th, 2005, 01:18 PM

Here is GetRecords:

Code:

Public Function GetRecords(ByVal strSQL As String, ByRef rstRecordSet As ADODB.Recordset, Optional ByRef actionSQL As Variant = "") As Boolean



    If (actionSQL <> "") Then

        Dim cmdCommand As New ADODB.Command

    End If



    Dim rstCommand As New ADODB.Command


    If (actionSQL <> "") Then


        MainConnection.Execute actionSQL, dbFailOnError


        With cmdCommand
            .ActiveConnection = MainConnection
            .CommandText = actionSQL
            .CommandType = adCmdText
        End With

    End If

  'Defines our command object


  With rstCommand
    .ActiveConnection = MainConnection
    .CommandText = strSQL
    .CommandType = adCmdText

  End With

  With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open rstCommand
  End With



    If (actionSQL <> "") Then
        Set cmdCommand = Nothing
     End If
    Set rstCommand = Nothing


End Function

The ADODB connection: MainConnection is declared as a global variable, and a separate function is used to open a new connection when the form is opened.

Thanks!!

ÃaÃeViÂ£

sdilucca · August 11th, 2005, 01:42 PM

i know this is kind of getting of the subject, but i guess i have the right to learn too :-) .... what is the purposed of cmdCommand? I never actually see you use it? still working on ur stuff

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

DaDeViL · August 11th, 2005, 01:54 PM

ADO is really really confusing to me for some reason lol, I dont really know why cmdCommand is in there because the Execute command only uses actionSQL.
GetRecords is actually a combination of two different functions i put together, so when i wrote it i assued cmdCommand was needed, but by the looks of it, seems its just a hood ornament for the function.

I was so frustrated with ADO that after I got the function finally working I was happy with NEVER looking at it again! lol

ÃaÃeViÂ£

sdilucca · August 11th, 2005, 02:03 PM

i don't see what the purpose of rlRecordSet is. I figured you were going to make it the RowSource for your combobox. But I don't see you doing that. Does anything actually change when you run this code?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

DaDeViL · August 11th, 2005, 02:12 PM

Well, I use GetRecords to insert data into the database, but the RecordSet parameter to the function is not optional, so
I HAVE to create a new RecordSet(rlRecordSet) to throw to the function. But this is a convenient way of doing it. Suppose
I need the ID of the record I just entered into the database using strInsertRoute (and for a NotInList event, I assumed I needed it),
then all i have to do is set

Code:

ResultQuery = "SELECT @@Identity FROM ROUTE_NUMBER"

(as the function already does)

So now if I type

Code:

 MsgBox rlRecordSet.Fields(0)

right after I call GetRecords function, it will prompt the ID of the record I just inserted, and this code actually works...I used it to test and make sure that data is being entered into the database table when I enter new data into the combo box.

This way, if I cant get my NotInList event working properly, I have an alternate way to make sure the typed route ID is recorded with the rest of the form info.

RowSource for Combo box is this:

SELECT DISTINCT ROUTE_NUMBER.ROUTE_NUMBER, ROUTE_NUMBER.PK_ROUTE_NUMBER FROM ROUTE_NUMBER WHERE ROUTE_NUMBER.ROUTE_NUMBER IS NOT NULL ORDER BY ROUTE_NUMBER.ROUTE_NUMBER;

and bound column is 1.

ÃaÃeViÂ£

sdilucca · August 11th, 2005, 02:39 PM

have you tried to requery on Before Update?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."