Girard Aquino wrote:
I created a database with two tables. The purpose of this database is
to monitor what supplies (SUPPLIES table) were given to who (CLIENT
table), when they were given, and how many. I hope I have my structure
correct, but if there;s any room for improvement, it would be more
than welcome.
Basically, what I want to happen is:
- client approaches staff and asks for supply
- staff enters ID number and if
- client already has an entry in database, staff just adds the new
supplies the client needs which goes to the SUPPLIES table
- client is not in database, staff creates a new entry in the CLIENT
table and enters supplies given accordingly.
For the CLIENT table i have the following fields: ID, FirstName,
LastName. For the SUPPLIES table, I have: ID, Date Given, Item, Quantity.
I've set the ID as primary key in the CLIENT table, and made a
relationship between the corresponding fields in the CLIENT and SUPPLY
tables.
I created a form using the wizard and set the SUPPLY as a sub-form of
the main form which uses the fields of the CLIENT table (e.g. ID,
FirstName, LastName). Now when I enter data in the ID, say 1234, if
there is an existing entry under the Supplies table that is associated
with the primary key, in this case, ID in the CLIENT table, the items
show up on the form.
What I would like to happen is to have the FirstName and LastName show
up as well when an existing ID is entered in the form that is already
existing in the CLIENT table. It's kinda like a search form, that
tells you when there is an existing entry already. Do i need to create
a separate table that will duplicate the FirstName and LastName fields
so that they show up when an existing ID in entered? Or should there
be a separate form to do "searching" and another one to enter new data?
One of the many problems is that when the form is opened, it is placed
on the first entry so that the user enters an ID that is already
existing, it does show up the corresponding entries in the SUPPLIES
table, but the action is treated as trying to replace the primary key
in the CLIENT table, which is the ID. I encounter errors here. I
wonder how I can get around this.
I hope someone could help me out as I've never had the need to create
a database, hence, I have limited understanding of how to properly
structure tables and fields, when there is a need to create new tables
and establish new relationships - not to mention that the more complex
tasks like macros and some programming. Thanks in advance!
Hello Girard,
Actually you are quite close to having what you want. There is a problem
with your tables with regards to the relationship link between them but
after that most of the functionality you want is already thee in the
Base application.
The tables.
You need to add one new column to the Supplies table:
ClientID - type = integer, AutoValue = NO, EntryRequired = YES
You can add this to the end of the table columns, that's fine.
Now - it sounds as if you created a relation, using
Tools->Relationships, between Client.ID -> Supplies.ID, yes? This is
wrong. The relationship that you describe in your text is actually
Client.ID -> Supplies.ClientID. I hope that you can see the difference
here. The ID field in each of the tables is used to uniquely identify a
row in the respective table and therefore can't represent a relationship
between the two tables. By adding the ClientID field to the Supplies
table and NOT having it be part of the Primary Key for the table allows
it to be used for this relationship (linking) function.
So, if you did create the relationship described please remove it and if
you like create the proper relation.
Fine with that done then lets look at what form or forms you really need.
A single form solution might be something like this.
Two options here
1) Grab the file with the form I want to use for an example in the next
email at
HA! Tried to upload this odb file to the wiki.....not allowed..LOL..had
to make the same change to the forums also.
http://baseanswers.com/downloads/oooSupplies_mailinglist.odb
2) Create the form using the Form Wizard and then apply 12 simple
changes, listed in detail later.
Either way the real email I want to send is about how to work with the
form...to follow pretty quickly.
Drew
---------------------The details of making the form
------------------------------
In the table section of the Base document window right click on the
Client table and select 'Form Wizard'
On the first page of the wizard select all of the columns from the
Client table.
next
Place a tick mark in 'Add subform'
If you have created a relation between the Client and Supply table,
using the Relationship window, then in the list box 'Which relation do
you want to add' table Supplies will be listed.
If so then select 'Subform based on existing relation' and select
Supplies with your mouse.
Otherwise - you have not created the relation in which case select
'Subform based on manual selection of fields'.
Either way now 'next'
In the drop down 'Tables or queries' ensure that Supplies is selected.
Add all available fields to the form.
next
Ensure that the two top drop down lists contain 'ClientID' and 'ID',
left to right respectively.
next
In the section 'Arrangement of the main form' select the second from
left style; Colmnar - Labels on top
In the section 'Arrangement of the subform' select, the default; As data
sheet
next
In the Steps list on the left you could now select Step 9 - Set name
On this page select Modify the form.
Give your form a name - I used 'Disperse Supplies'
Finish
Sorry if that was over kill on how to use the form wizard. But this is
what I wanted to get us to.
The new form in opened in design mode and I would like you to make a few
changes to some controls - in one case at least I think the form wizard
should actually be changed to default this change.
What I find to be the best way to work on forms is using the form
navigator. (opened with a tool button found on 'Form Design' tool bar)
If you created the form as described the form navigator displays a tree
view containing ten items.
The top level of the tree has one item "Forms", the name of a container.
[Really not sure why that is displayed at all IMO...but there it is]
At the next level is again one item 'MainForm', this is the name of a
Dataform control. This control is again a type of container and it
allows access to both the fields in your database table and to controls
on your form.
One more level down on the tree you will find seven items; SubForm,
lblID, fmtID, lblFirstName, txtFirstName, lblLastName, txtLastName
SubForm - this is another Dataform' control. In this case it is the
access point to the Supplies table and includes information on the link
we made from the Client table to the Supplies table.
For the remaining items they are the names of the controls added to the
form within the wizard.
Each name has two parts;
The first 3 characters tell you the type of control. lbl = LabelField,
fmt = FormattedField, txt = Text Box
The rest of the controls name is the name of the column in your database
that this control is 'bound' to. (bound meaning that data is
automatically synchronized between the database tables and the on screen
form)
Finally one level under the item SubForm is a single item; SubForm_Grid.
This is the name of the Table control used to display the columns from
the Supplies table.
Onto the changes I would suggest then:
In the Form navigator window select fmtID. Right mouse click and select
'properties' to open the controls properties editor.
Change 1 -
Change the property 'Read-only' to Yes. This control is bound to a
tables Primary Key field and should, almost always, be a read only
display. (this is the change that I feel should be the default setting
for PK fields)
Change 2 -
Change the property 'Tab stop' to No. This is more a personal preference
change I suppose - but I think it usually makes sense when dealing with
Primary Key fields.
In the Form navigator window select SubForm. The Dataform control not
the grid control.
If the 'property editor' is still open the contents changed to display
the properties for this control - otherwise open it again.
Change 3 -
Select the 'Data' tab in the property editor.
Change the property 'Navigator bar' to No.
Now be sure that you have a clear view of the SubForm_grid table
control. Move the control property editor to the side if you need to.
The SubForm_Grid has one display column for each data data column you
selected in the Form wizard. You can choose which of the columns to
display or hide and control certain properties for each column.
Use the mouse to select the column named ID in the SubForm_Grid control.
Again the control properties dialog's contents change to showing the
available properties for this grid controls column.
Change 4 -
Change the property 'Read-Only' to Yes. (Again this is the primary key
field for the Supplies table, and even though in the next change you
will hide it...it is best to be careful with your keys :>)
Change 5 -
Right mouse click on the string ID in the SubForm_Grid controls header row.
In the context menu that appears select 'Hide Columns'. (Although the
column will be hidden by default now it can still be displayed when the
form is used for data entry - again using the same context menu)
Change 6 -
Use your mouse to select the column header "Date Given"
Set the property 'Read-Only' to Yes.
Change 7 -
Click on the tab 'Data' in the property editor
Change the property 'Input Required' to No.
Change 8 -
Use your mouse to select the column header "Quantity"
Change the property "Default value" to 1
Change 9 -
Right mouse click on the header cell for ClientID
select 'Hide Columns'.
Change 10 -
use your mouse and in the form navigator window select the item SubForm_Grid
The control property editor's now has the contents for the properties of
the overall SubForm_Grid versus the individual columns per the last few
changes.
For the Font property - click on the little button to open the Font
property editor and pick something you find legible.
(in my case I went with Verdana (I know I know...it's not free...) -
Bold - 11 points - and A different color also.
Size and arrange the grid columns as you prefer.
Save and close the form..
Open the SQL window. ( Tools->SQL)
Change 11 -
Copy and paste the following command into the SQL window and click on
the execute button.
ALTER TABLE "Supplies" ALTER COLUMN "Date Given" SET DEFAULT CURRENT_DATE
Change 12 -
Now set the default value for the Quantity column with:
ALTER TABLE "Supplies" ALTER COLUMN "Quantity" SET DEFAULT 1
It's a good spot to save the file at.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
