Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NotInList-Debugging (97-sr-2)

    I'm having the worst time with this not inlist event code.
    I grabbed it straight off the MSsite and customized it just a touch, but it should be working...
    It goes through all of the steps then adds a blank record..
    Oddly enough, the Limit To List is set to yes, because I can't change that property without getting an error
    about the column widths and the bound column. What am I doing wrong?
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Anyway, here's the code:
    <font color=blue>Private Sub cbo_Dist_Div_NotInList(NewData As String, Response As Integer)
    Dim Db As Database
    Dim Rs As Recordset
    Dim Msg As String
    Dim CR As String

    CR = Chr$(13)

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    ' If the user chose not to add a District/Division, set the Response
    ' argument to suppress an error message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox "Please try again."
    Else
    ' If the user chose to add a new customer, open a recordset
    ' using the Dist_Div table.
    Set Db = CurrentDb
    Set Rs = Db.OpenRecordset("Dist_Div")
    ' Let code execution continue if a run-time error occurs.
    On Error Resume Next
    ' Create a new record.
    Rs.AddNew
    ' Ask the user to input a new District/Division.
    Msg = "Please enter the new District, Division or Area Office."
    Rs![Dist_Div] = InputBox(Msg)
    ' Assign the NewData argument to the CompanyName field.
    Rs![Dist_Div] = NewData
    ' Save the record.
    Rs.Update

    If Err Then
    ' If a run-time error occurred while attempting to add a new
    ' record, set the Response argument to suppress an error
    ' message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox Error$ & CR & CR & "Please try again.", vbExclamation
    Else
    ' If a run-time error did not occur, set Response argument
    ' to indicate that new data is being added.
    Response = acDataErrAdded
    End If

    End If
    End Sub
    </font color=blue>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: NotInList-Debugging (97-sr-2)

    Hi,
    You lost me with this bit:
    Rs![Dist_Div] = InputBox(Msg)
    ' Assign the NewData argument to the CompanyName field.
    Rs![Dist_Div] = NewData

    where you appear to be assigning one value to the field, then immediately assigning a different value to that field. Is it possible that the data type you're trying to assign to that field is incompatible but your On Error Resume Next simply glosses over that so you end up with an empty record?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    Probably,
    The sample code was intended to add a new ID number and then take the input from the inputbox,
    and assign that as the value of field 2 in the new record.
    After it goes through that rigamarole, it gives me the item not found in the collection,
    which tells me it's still confined to the list or something similar.
    My main problem is, I can't get the Limit To List property in the form to "NO".

    <font color=blue>Here's the original script:</font color=blue>
    Private Sub CustomerID_NotInList (NewData As String, Response As _
    Integer)
    Dim Db As Database
    Dim Rs As Recordset
    Dim Msg As String
    Dim CR As String

    CR = Chr$(13)

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    ' If the user chose not to add a customer, set the Response
    ' argument to suppress an error message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox "Please try again."
    Else
    ' If the user chose to add a new customer, open a recordset
    ' using the Customers table.
    Set Db = CurrentDB
    Set Rs = Db.OpenRecordset("Customers", DB_OPEN_TABLE)
    ' Let code execution continue if a run-time error occurs.
    On Error Resume Next
    ' Create a new record.
    Rs.AddNew
    ' Ask the user to input a new Customer ID.
    Msg = "Please enter a unique 5-character Customer ID."
    Rs![CustomerID] = InputBox(Msg)
    ' Assign the NewData argument to the CompanyName field.
    Rs![CompanyName] = NewData
    ' Save the record.
    Rs.Update

    If Err Then
    ' If a run-time error occurred while attempting to add a new
    ' record, set the Response argument to suppress an error
    ' message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox Error$ & CR & CR & "Please try again.",vbExclamation
    Else
    ' If a run-time error did not occur, set Response argument
    ' to indicate that new data is being added.
    Response = acDataErrAdded
    End If

    End If
    End Sub

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    If you want to use your code in the Not In List event then the Limit To List property must be set to Yes. If you want to have Access add whatever is input into your combo box then delete the Not In List code and set the Limit To List to No.Check Help for more information on the Limit to List property.

    hth,
    Jack

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    That doesn't work for me..for some reason.

    My look-up value in the table binds the ID(col1) but the actual value is col2
    Everytime I try to change the Limit To List property I get a "can't change until I alter column widths."
    When I reset the column widths I see numeric values in the box(ID-numbers I'm sure)
    and then a message that it's the wrong dataype, when I attempt textual input.
    What the.... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  6. #6
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    Here is your original code:

    Rs![Dist_Div] = InputBox(Msg)
    ' Assign the NewData argument to the CompanyName field.
    Rs![Dist_Div] = NewData

    First you save the value that the user inputs into the InputBox into a field called 'Dist_Div'. In the next line of code you put whatever was entered into the combo box into the same field. These should be different fields in your table and probably the reason you are getting the wrong Data Type is that you are trying to put text into a numeric field. I am not sure what else to tell you to do....

    hth,
    Jack

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    Yeah, I spotted all of that.
    That's because the sample code was asking the user to enter 2 values.
    I commented out one of two in both cases.
    Still won't work..but I'll keep trying.

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    My only other suggestion would be to put a Stop in your code and step through it one line at a time and see what values the variables are holding, etc.

    Good luck!

    Jack

  9. #9
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    I do not use the Lookup tab in a table as I prefer to make them as I need them. This is just a personal preference, but it works for me. Anyway, I am glad you found your problem.

    Jack

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    Cool,
    I figured it all out.
    It was much easier than I thought.

  11. #11
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList-Debugging (97-sr-2)

    As it turns out, I am completely 'off 'on how to set the restrict to list.
    That is the root of my problem.
    Everyday I find yet another hole to patch in my learning process.
    <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •