Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access Forms - Changing Text Box to Drop Down


(!)

jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
01-May-2012, 10:36 AM #1
Solved: Access Forms - Changing Text Box to Drop Down
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?
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
01-May-2012, 11:27 AM #2
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.
jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
01-May-2012, 12:02 PM #3
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?
OBP's Avatar
OBP OBP is online now OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
01-May-2012, 01:30 PM #4
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.
__________________
OBP
I do not give up easily
jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
01-May-2012, 02:21 PM #5
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.
OBP's Avatar
OBP OBP is online now OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
02-May-2012, 05:39 AM #6
Are you trying to "select" a Manager or "find" a Manager?
jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
02-May-2012, 08:10 AM #7
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.
OBP's Avatar
OBP OBP is online now OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
02-May-2012, 08:25 AM #8
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.
jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
02-May-2012, 09:15 AM #9
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.
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
02-May-2012, 09:23 AM #10
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.
jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
02-May-2012, 09:32 AM #11
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
File Type: zip HfDatabase - Dummy Data.zip (218.0 KB, 14 views)
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
02-May-2012, 10:14 AM #12
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
File Type: zip HfDatabase - Dummy Data1.zip (182.3 KB, 26 views)
jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
02-May-2012, 03:15 PM #13
Everything works. Thanks so much for the help, guys.
jaykojc's Avatar
jaykojc jaykojc is offline
Member with 13 posts.
THREAD STARTER
 
Join Date: Apr 2012
09-May-2012, 10:28 AM #14
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?
Rockn's Avatar
Computer Specs
Member with 21,189 posts.
 
Join Date: Jul 2001
Location: Somalia of the North, MN
Experience: Disenfranchised American
09-May-2012, 10:31 AM #15
The MgrID is used in column 1 of the dropdown list. Just reference the Me.Dropdownlist control and pass that value.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access, forms

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑