Storing text from a combo box in a separate table

[JJ1109]

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

 

[Jeanette Cunningham]

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

 

[JJ1109]

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

 

[Jeanette Cunningham]

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

 

[JJ1109]

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

 

[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

 

[JJ1109]

thanks Jeanette!
I'll give it a go first thing tomorrow and let you know

thanks again
Chris

 

[JJ1109]

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

 

[JJ1109]

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