Re: Storing text from a combo box in a separate table
- From: "Jeanette Cunningham" <nnn@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 10 Jan 2008 16:51:04 +1100
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" <JJ1109@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E3096D93-1E3D-4640-B7CC-352318F5D39E@xxxxxxxxxxxxxxxx
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" wrote:
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
.
- References:
- Re: Storing text from a combo box in a separate table
- From: Jeanette Cunningham
- Re: Storing text from a combo box in a separate table
- From: JJ1109
- Re: Storing text from a combo box in a separate table
- From: Jeanette Cunningham
- Re: Storing text from a combo box in a separate table
- Prev by Date: Re: Hierarchical combos
- Next by Date: <no subject>
- Previous by thread: Re: Storing text from a combo box in a separate table
- Next by thread: DB HELP PLASE
- Index(es):
Relevant Pages
|
