Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating ComboBox Values (Access 2000)

    I want to create a form with a combo box list of values. The user should be able to pick from the list or add an new value, which will then be available for future records.
    Arrgh! I'm missing something simple here and I know I've done it before - but cannot remember ...

    TIA

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating ComboBox Values (Access 2000)

    <hr>and its List Only property must be set to True.
    <hr>

    Mr Picky here....

    It is the Limit to List property. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Updating ComboBox Values (Access 2000)

    Thanks! Should teach me to reply without looking at Access! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

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

    Re: Updating ComboBox Values (Access 2000)

    <P ID="edit" class=small>(Edited by HansV on 22-May-03 20:12. Corrected mistake)</P>The combo box must have Table/query as Row Source Type, a table or query as Row Source, and its LimitToList (not ListOnly, thanks SteveH!) property must be set to True.

    You must write code in the Not In List event to add the value typed by the user to the table/query. This can be done by a simple append query, or by opening another form that lets the user enter additional information. The event procedure has two arguments: NewData is the value entered by the user, and Response can be set by you to specify how the data was handled. It can be set to:
    acDataErrAdded = you have added the new data to the Row Source
    acDataErrContinue = you have not added the new data; don't display an error message
    acDataErrDisplay = you have not added the new data; display the standard error message (this is the default)

    Example:

    Private Sub txtCustomer_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    If MsgBox("Customer " & NewData & " does not occur in the list." & vbCrLf & _
    "Do you want to add it?", vbYesNo + vbQuestion, "Customer") = vbYes Then
    strSQL = "INSERT INTO tblCustomers (Customer) VALUES (" & Chr(34) & NewData) & Chr(34) & ")"
    CurrentDb.Execute strSQL
    Response = acDataErrAdded
    Else
    [txtCustomer].Undo
    Response = acDataErrContinue
    End If
    End Sub

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Updating ComboBox Values (Access 2000)

    <P ID="edit" class=small>(Edited by WebGenii on 30-Jun-03 11:51. )</P>What is the Chr (34) for?

    Curious
    <img src=/w3timages/redline.gif width=33% height=2>
    some time later ...
    Nevermind, answered my own question it is the symbol for double quotes.
    BTW, I think the strSQL should be:
    strSQL = "INSERT INTO TableName (FieldName) VALUES " & Chr(40) & Chr(34) & NewData & Chr(34) & Chr(41)
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Updating ComboBox Values (Access 2000)

    Chr(34) gives you a double quote. It's a method of wrapping the string in double quotes without going crazy trying to figure out how many you need to use.
    Charlotte

Posting Permissions

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