1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access Forms - Changing Text Box to Drop Down

Discussion in 'Business Applications' started by jaykojc, May 1, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    Hi all. I am sure this is a fairly basic question which shows a lack of understanding of how Access works, so if its stupid, I apologize in advance...

    I used the form builder to create a new input form for data. There is one selection for "Manager", which defaults to a text box that you can navigate with the arrows on the bottom of the sub-form, which has the manager id (primary key), the date, and the return associated with it. It works as intended, but the text box won't work long term.

    I expect to have a decent number of managers, so I'd like to make that a drop down box so the user can select it, not have to scroll through one by one. I changed it to a combo box, and specified the query to populate the box. When I select a different manager than the default, I get an error which says, "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    I think its trying to create a new manager with the selected name from the combo box, but it won't allow duplicates (which it shouldn't). What am I missing conceptually here?
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    You probably have the current form bound to the table and using the dropdown will change what is in the underlying table. Probably bound to the managers table.
     
  3. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    The box in the Data tab called "Row Source" represents the bound, correct? I have it linked to the query to pull up the data and that didn't work. Then I tried the table directly and change the bound column to the MgrName field, but when using the table it will only populate with the MgrId's. The fields in the MgrTbl are MgrId, MgrName, StatusId, StrategyId. If I want it to be a list of all available MgrNames, how do I bound it that way?
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    It should be bound to the Manager ID but display the manager Name.
    You have the mgrId as the Control Source and column 1 in the combo with it's width set to 0cm. You have the MgrName as the second column with the width set to around 2.5cm.
    That stores the ID but displays the name as it should.
     
  5. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    Thanks for the help guys. Everything looks right, the box pulls the right field and the left/right clickers on the bottom take me forward or back a manager. When I click one of the manager names in the drop down, that doesn't work (when you click it just makes the 'this ain't right' sound). Do I need to specify somewhere what to do when you select from the box? An 'on click' or 'on change' bit? Or do I have one of the bits within the properties screwed up so it doesn't accept the selection?

    Again, thanks for all the help. I realize these questions are 101.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Are you trying to "select" a Manager or "find" a Manager?
     
  7. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    Select. I'd like to be able to pick the manager in the drop down box to limit the returns in the sub query below.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Does the mainform/subform have the Master/Child links set?
    When you select a Manager it puts the value in the ManagerId field which should then work on the subform.
     
  9. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    Well I was able to see that the problem was that MgrTbl.MgrId was an autonumber field, so I changed it to a regular number and reestablished the relationships in the table between MgrTbl.MgrId and ReturnTbl.MgrId. But then when I try to change the manager via the box, it says, "Cannot perform cascading operation. It would result in a duplicate key in table "."

    The relationship between MgrTbl.MgrId and ReturnTbl.MgrId is one-to-many, enforce referential integrity, cascade update related records, cascade delete related records.
     
  10. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    Is it possible to post a sample database? It could probably be sorted out in 5 minutes and we can tell you how to fix it.
     
  11. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    Here it is; I took out most of the data except a couple of sample managers. The form I'm trying to make work is "MgrReturnQuery1."
     

    Attached Files:

  12. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    Here ya go. The main form itself does not need to be bound to a table or any data source at all so I deleted that. Created a query to populate the sub form and had the dropdown lists afterUpdate event requery the sub form.
     

    Attached Files:

  13. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    Everything works. Thanks so much for the help, guys.
     
  14. jaykojc

    jaykojc Thread Starter

    Joined:
    Apr 30, 2012
    Messages:
    18
    How does one take the MgrId from the parent form and use it as the default in a new record for the subform? It is being used (but hidden) in the parent form; can I just pass from one to the other and default the new record?
     
  15. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    The MgrID is used in column 1 of the dropdown list. Just reference the Me.Dropdownlist control and pass that value.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1051546