 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / January 2008Storing text from a combo box in a separate table
Hi, quite new to this database thing :) I'm using Access 2000, I'm trying to get a form to display a drop down box that has values from one table, and to store those values in another table. I've managed the first part, no worries, however I can't get the actual text to be stored in the new table: it stores the key value instead. I'll try to describe what I have here. I'm trying to track people using consumables in my workplace. A form is filled in (on paper, how retro!) saying who wants it, what they want and how much of it they want. I want to put that info into the database so we can see who's using what and how often (to justify expenses). So Table1 is a list of Employees. Table2 is a list of consumables. Table 3 is everything else (date, quantity, name of person doing the request and name of requested consumable). So obviously I want a form with a box where i select the name of the person from the Employees table, then that name gets stored in the name field in Table 3. And the same for the Consumable: select from a list generated from Table2 and store the name in the relevant field in Table3. Like I said, I can't get the name displayed, only the Key. I'm guessing there's something wrong with relationships I've set-up, however I don't fully understand them: I've linked the primary key in Table1 to the Primary in Table3, and the primary in Table2 to the primary in Table3. However if i try to link things differently, it all falls apart and I can't see anything when I try to view the form: I can see it in design view but can't see anything when I switch to "view" mode. Hope I've explained that in an understandable way, and sorry for the long post! cheers JJ Hi, you have set it up exactly the correct way. Store only the ID value in the other table. Use a query when you want to view the data. The query will combine both tables, and it will show the names instead of the ID value - this is how a database works. Jeanette Cunningham > Hi, quite new to this database thing :) I'm using Access 2000, I'm trying > to [quoted text clipped - 42 lines] > cheers > JJ Thanks for the fast reply Jeanette! I think I'm a bit dense: when i made a query to look at the data, it simply shows up exactly what I see when I open the table itself. Can you give me a hint further, or point me to a nice Access-For-Dense People website?! thanks! Chris > Hi, > you have set it up exactly the correct way. Store only the ID value in the [quoted text clipped - 51 lines] > > cheers > > JJ It would be easier if you would post the details about your 3 tables. name of table name of primary key field name of foreign key field (if it has one) which table is related one-to-many or many-to-many to which other tables include the name of the employees' name field as well Jeanette Cunningham > Thanks for the fast reply Jeanette! > [quoted text clipped - 74 lines] >> > cheers >> > JJ Thanks again Jeanette. Here's the information. The three tables are as follows: Table 1: Consumables_Info (the list of consumables) Primary Key field is called "ConsID" and is just an Autonumber field The only other field is "Consumable". Table 2: Users_info (list of employees) Primary Key field is called "UserID" and is just an Autonumber field Employee field is called "Name" (pretty original huh ;)) Table 3: Ordered_consumables (where all the data ends up) Primary Key is "ID" and is just an Autonumber field Other fields are "Date" (ordering date), "ConsumableUser" (person who ordered it, from the list in Users_info), "UsedConsumable" (from the list in Consumables_Info) and "Quantity". The relationships are as follows: I simply dragged the primary key from Consumables_Info to the primary key of Ordered_consumables, and dragged the primary key from User_info to the primary key of Ordered_consumables as well. I'm not sure about foreign keys or the one-to-many or many-to-many thing as I don't understand that properly yet, so I just described exactly how i made the relationship :/ The plan is to have a form where I enter the date and the quantity manually, and select the other two from a combo-box. Then ideally I'll be able to show something (report, query, whatever) that lists who ordered what and when. thanks for all your time! Chris > It would be easier if you would post the details about your 3 tables. > name of table [quoted text clipped - 4 lines] > > Jeanette Cunningham Chris, Thanks for posting the tables info I requested. I am going to suggest some modification to your tables. Consumables_Info --no change Users_Info --change Name to UserName Ordered_Consumables --OrderID >>Primary Key autonumber --UserID Number data type, Long Integer, clear the 0 from its default value set its Required property to yes --ConsID Number data type, Long Integer, clear the 0 from its default value set its Required property to yes --OrderDate DateTime data type, you could set the default to =Now() this will put todays date and time in your form without you having to type it, you can easily select the date and type a different one if you wish --Quantity Number data type, Single data type, clear the 0 from its default value To set up the relationships, drag the primary key from Consumables_Info to the ConsID in tblOrdered_Consumables, set Enforce Referential Integrity to yes drag the primary key from Users-Info to the UserID in tblOrdered_Consumables, set Enforce Referential Integrity to yes Save the relationships window To make the data entry form use the form wizard. --Create form by using wizard --choose Ordered_Consumables for the table --add all of the available fields --click Next --choose Tabular for the layout --name your form --click Finish Open the form in design view --select the textbox called OrderID and set its Visible property to No --delete the textbox for UserID --delete the textbox for ConsID click the Save icon on the toolbar Now we will add 2 combos to the form --with the wizard turned on --click the symbol on the toolbox for combo and hover over the form --drag the outline of a rectangle and release the mouse --on the dialog that opens choose the 1st option --choose the Users-Info table --choose both fields --sort the UserName field in ascending order --make sure the checkbox for Kide key column is turned on --choose the second option Store that value in this field and choose UserID from the dropdown --choose a name and save Repeat the same process to add a combo for ConsID, but choose the Consumables_Info table and use the Consumables_Info table, store the value in ConsID Open the form in design view. Move the text and combo boxes enough so that you can use them. Save the form and open it to enter data. Enter some test data. If everything works OK, you can improve the appearance of the form. Note: I changed the name of the Date field to OrderDate and the name of the Name field to UserName Both Date and Name are reserved words in Access and shouldn't be used as field names. I have checked my instructions and don't expect there are any mistakes in the process, but if you have any problems with it, please post back. Good luck with this Jeanette Cunningham > Thanks again Jeanette. Here's the information. > [quoted text clipped - 42 lines] >> >> Jeanette Cunningham thanks Jeanette! I'll give it a go first thing tomorrow and let you know :) thanks again Chris Jeannete, works swimmingly! Now i'll try to work out the query thing so i can get a report of all the things that have been ordered by whom. thanks again Chris Done! thanks for your help, you've been invaluable... now I can start hunting down those people who are using things too much ;) cheers Chris > Jeannete, > [quoted text clipped - 3 lines] > thanks again > Chris |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.