Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Drop Down List (2003)

    I an item is not in a drop down list, is there a way of adding it, and getting the database to store it in the appropriate field, and remember it for next time?

    Rob

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List (2003)

    You must set the Limit to List property of the combo box to Yes, and write code in the On Not In List event. See <post#=146637>post 146637</post#> for examples of how to use this event. Post back with details if you need specific help.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Drop Down List (2003)

    If the values are in another table rather than a list from the current table, then Hans' advice is correct. If the drop down shows a list of values in the current table, it can be done directly with the LimitToList set to no, but you would then need to requery the combobox. You'll need to provide more information if the problem is a variation on those two situations.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Drop Down List (2003)

    Yes, there are some values in the field (Specification) in the table tblInfo, but I want to be able to add to that table so they will appear the next time the form is used. I've tried adapting the code from Hans, but it doesn't seem to work.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List (2003)

    1. Is tblInfo different from the record source of the form?
    2. Can you provide the following properties of the combo box?
    - Column Count (in the Format tab of the Properties window)
    - Column Widths
    - Row Source Type (in the Data tab)
    - Row Source
    - Bound Column
    - Limit To List
    - Name (in the Other tab)

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Drop Down List (2003)

    1. No
    2. Can you provide the following properties of the combo box?
    - Column Count =1
    - Column Widths =2.54
    - Row Source Type (in the Data tab) =Value List
    - Row Source ="";"L99";"L169";"LM25"
    - Bound Column=1
    - Limit To List=No
    - Name (in the Other tab) = Combo64

    Regards,

    Rob

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List (2003)

    If the Row Source Type of a combo box is Value List, the values are not taken from a table. You can add the new item to the list, but it will disappear when the user closes the form. You must set the Row Source Type to Table/Query and the Row Source to the name of a table or query (or an SQL statement) if you want the list values to be stored permanently.

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Drop Down List (2003)

    I've added a table, set the Row Source and the Row Source Type, and added the code. Now I'm getting the runtime error code '3134, which seems to be at the 'CurrentDb.Execute strSQL, dbFailOnError' bit of the code. I've addapted it so the table names etc, are relevant.

    Regards,

    Rob

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List (2003)

    That means that there must be an error in the strSQL string - error 3134 is "Syntax error in INSERT INTO statement."

    Could you post the code for the NotInList event you have now?

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Drop Down List (2003)

    Private Sub Combo64_NotInList(NewData As String, Response As Integer)
    If MsgBox("The type " & NewData & " does not occur not in the list." & vbCrLf & _
    "Do you want to add it?", vbYesNo + vbQuestion) = vbYes Then
    Dim strSQL As String
    strSQL = "INSERT INTO Materials Spec (Materials Specification) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    cboCombo64.Undo
    Response = acDataErrContinue
    End If

    End Sub

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Drop Down List (2003)

    You have table names and field names with spaces in them. This confuses Access and SQL. The remedy is to enclose the names in square brackets:

    <code>strSQL = "INSERT INTO [Materials Spec] ([Materials Specification]) VALUES (" & _</code>
    <code>Chr$(34) & NewData & Chr$(34) & ")"</code>

    (This is precisely why I avoid using spaces in the names of database objects and of fields.)

Posting Permissions

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