Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Lounger
    Join Date
    Nov 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto update combo boxes (2002)

    My database was created to track charitable contributions. The linked fields are DonorID, CharityID and PurposeID. These are autonumbers on one table and numeric on the transactions table. On the Contribution Entry Form these fields are combo boxes that are driven by queries that sort the information alphabetically to make life easier for the end-user. The list drops down just fine, but when a choice is made an error message appears that I am trying to enter text into a numeric field. I also want to make the charities table and the purpose table update automatically via the entry form. I have change the "limit to list" to "no" every where I can find it, but I still get the error message that a corresponding entry cannot be found on the linked table. My goal is to have an entry form that drops down a list in alphabetical order, however, if what I need isn't on the list I can add a new entry without closing the form and editing the linked table and then returning to the form.

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

    Re: Auto update combo boxes (2002)

    The error that you are trying to enter text into a numeric field means that there is a mismatch between the row source of the combo box and the control source.

    For example, if you have a combo box for donors, the control source will be DonorID, a numeric field. The row source of the combo box should have DonorID as first column, preferably hidden. It should be a query based on the donors table, that returns DonorID and DonorName, sorted on DonorName. The combo box must have Column Count set to 2, and Column Widths set to 0", 1". This hides the DonorID field, while it will still be the field that is stored.

    If you want to be able to add items to the list in a combo box, you must write code for the On Not In List event of the combo box. We can help you with that, but could you indicate how much experience you have with writing VBA code in Access? It doesn't matter if you have very little, or none at all, but it's useful to know how detailed the reply should be.

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

    Re: Auto update combo boxes (2002)

    It sounds like your combobox may be bound to the DonorID field but you have set the bound *column* to a name instead of the DonorID. You can hide the DonorID field, and you can sort on name in populating the list, but the DonorID, needs to be the bound column. If you set the bound column to anything other than the first column, LimitToList is automatically true, regardless of the setting you enter. You actually need to set the LimitToList property to Yes and then add code to a procedure triggered by the NotInList event to add a new record to the underlying tables. I'm attaching a text file containing a generic routine that can be called from any combobox's NotInList event if the routine is placed in a standard module. It will give you an idea of how to proceed.
    Attached Files Attached Files
    Charlotte

  4. #4
    Lounger
    Join Date
    Nov 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    Wow. That was easy - you're the best!. Too bad I have already pulled all my hair out! I have taken care of problem one (the text vs. numeric error). I do not have any experience in VBA other than my Very Bad Attitude right now. I take it that my approach using "Limit to List" as "no" was not the right solution. Is it supposed to be "yes"? I was reading some other posts and your replies and I got the impression I was not even close in the way I set up my combo box. Therefore, your reply will need as much detail as you can possibly gather.

  5. #5
    Lounger
    Join Date
    Nov 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    Charlotte: Thanks for the reply. As I noted to Hans, I have solved one problem and your response to my VBA problem was very thorough, however, it might as well have been in Greek. I have saved the text of your attachment - now the question is how do I get it from there to my form? And where do I have to insert my table names, etc. to make it work in my database?

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

    Re: Auto update combo boxes (2002)

    You must set the Limit to List property to Yes if you want to be able to add items to the list. In fact, after changing the Column Count to 2, and setting the first Column Width to 0, Limit to List will automatically be set to Yes, and you won't be able to set it to No any more.

    Because Limit to List is set to Yes, entering a name that is not in the list will cause the Not In List event to occur. You can write VBA code to handle this event. I'll try to explain how to do this step by step below, but first the general idea.

    The Not In List event procedure has two arguments: NewData and Response. NewData is the text entered by the user, and Response can be set by you, as designer, to tell Access what to do:
    <UL><LI>acDataErrAdded means that you added the item to the Row Source of the combo box; Access will refresh the list automatically and suppress the standard error message.
    <LI>acDataErrContinue means that you didn't add the item to the Row Source, and that Access doesn't need to show the standard error message
    <LI>acDataErrDisplay means that Access should display the standard error message.[/list]In the following, I will use the following names. You must replace them with the actual names you are using:
    Donors table: tblDonors
    Fields in this table: DonorID, DonorName
    Combo box on form: cbxDonor

    Here are the steps:
    <UL><LI>Open the form in design view.
    <LI>Select cbxDonor.
    <LI>Activate the Event tab of the Properties window.
    <LI>Click in the On Not In List event.
    <LI>Select Event Procedure from the dropdown list.
    <LI>Click the Builder button (the three dots ... to the right of the dropdown arrow.)
    <LI>You're taken to the Visual Basic Editor, with the first and last line of the event procedure already in place.
    <LI>Make the procedure look like this (with the appropriate names substituted.)

    Private Sub cbxDonor_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    ' Ask user if (s)he is sure.
    If MsgBox("The name " & NewData & " does not occur in the list. " & vbCrLf & _
    "Do you want to add it?", vbYesNo + vbQuestion) = vbYes Then
    ' Yes, execute SQL to insert name into table
    strSQL = "INSERT INTO [tblDonor] ( [DonorName] ) " & _
    "VALUES ( " & Chr(34) & NewData & Chr(34) & " )"
    CurrentDb.Execute strSQL
    ' Tell Access that we added the item
    Response = acDataErrAdded
    Else
    ' No, undo the change
    Me.cbxDonor.Undo
    ' And tell Access not to display the error message
    Response = acDataErrContinue
    End If
    End Sub

    <LI>Note: the underscore _ at the end of a few lines is the continuation character; it siginifies that the instruction continues on the next line. There *must* be a space before the underscore,
    <LI>After entering this code, switch back to Access (Alt+F11).
    <LI>Close and save the form.[/list]Finally, open the form again and try entering a new name in the Donor combo box.

  7. #7
    Lounger
    Join Date
    Nov 2003
    Location
    St. Paul, Minnesota, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    Hans, I don't know you, but I love you! My database now works exactly as I wanted it to and everyone at the office thinks I'm wonderful. Thank you, thank you!

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    Question....
    I did everything this example shows, however with one small glitch...
    Currently when the form opens, the field "part number" is blank, but the rest of the fields are filled in. They do correct them selves once a part number is selected. However, when I entered a part number not on the list and choose to "Add" the part, the rest of the fields remain filled in (but do not save to the new records data).
    Is there away to add something to the code to tell it once a new record has been added to "blank" out the remaining fields and set focus to "Field 2"?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto update combo boxes (2002)

    How many columns are you displaying in the Combo Box? In general, these kind of combos have 2 columns, with the bound column being hidden (i.e. zero width). If you are adding a new record for the combo, it is presumably on a form other than for the "part number" columns other than the actual part record primary key and the descripton aren't usually necessary.
    Wendell

  10. #10
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    Here is the DB.
    Excuse the multiple "Comments" in the coding, but I have been trying so many items and wanted to keep track so I did not try something over, that did not work.
    What I need is for the user to be able to pick a item and update its information, or add a new item is needed.
    Attached Files Attached Files

  11. #11
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    Okay,
    I think I found the correct post!
    Can anyone take a look at the zip copy and advise me where I am going wrong and a possible solution?

  12. #12
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    I have attached a copy of the DB.
    Do you have any suggestions as to how I may do what I need?

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

    Re: Auto update combo boxes (2002)

    Quite frankly, I understand very little of what you're trying to do. The combo box for part number is unbound, and its row source is the same as the record source of the form. If the user selects a part number, the fields of the current record are overwritten by the fields of the record corresponding to the selected part number. The purpose of this action is not clear to me.

    There is an easy explanation why a new record is not blank. Each of the displayed fields has a default value, for eaxample the default for location is "EAF", and the default for current month is 0.

  14. #14
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto update combo boxes (2002)

    I understand that what I currently have may not be what I need.....

    However, what I am trying to do has not changed...

    1) Open the form
    2) Allow the user to select a part number
    3) Adjust the contents of the fields associated with that part number

    OR

    2) If the part number does not exist, add the part number
    3) Add content to the fields associated witht the part number.

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

    Re: Auto update combo boxes (2002)

    I still don't understand why the row source of the combo box is the same as the record source of the form. If the user selects an existing part number or enters a new one, where should the result of this choice end up?

Page 1 of 2 12 LastLast

Posting Permissions

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