Page 1 of 1
Creating forms for data entry
Posted: Thu Jul 05, 2012 11:15 pm
by syouchnow
Hi All:
I'm a bit confused and it may be my inexperience with BASE. I created a database with several tables. The main table is a list of projects and has various fields within it including ProjectManager, LocationOfProject, Supplier, etc. Each of these fields is a foreign key into an associated table. So there is a ProjectManager table, a Location table, Supplier table etc.
So I created forms for the tables and can easily enter data for ProjectManager etc. What I'd like to do is create a form for entering the data for the Projects table that have fields that will display a list menu for the data in associated tables so the person entering the data can select the appropriate entry.
So the question(s) is/are:
(1) Can this be done?
(2) If so, how?
Any help is appreciated.
Steve Y
Re: Creating forms for data entry
Posted: Sat Jul 07, 2012 9:38 am
by F3K Total
Hello,
syouchnow wrote:(1) Can this be done?
Yes, for sure!
syouchnow wrote:(2) If so, how?
See attached example.
Your keyword is "List Box". I've put three of them into the Table Control of form "projects".
The bound table of the MainForm is table "projects", entries in the List Boxes are made via SQL, e.g.
Code: Select all
SELECT "Name", "ID_PM" FROM "ProjectManager"
If you don't like them to be in the Table Control you can see one single List Box upright.
To show the associated datas i bound two subforms to the MainForm.
Greats R
Re: Creating forms for data entry
Posted: Mon Jul 09, 2012 10:04 pm
by syouchnow
Thanks for your help, that looks like just what I need.
Steve
Re: Creating forms for data entry
Posted: Wed Jul 11, 2012 6:34 pm
by TotalCareChiro
I have a problem in creating a form for Data Entry and figured that I'd just piggyback on another thread as the OP may run into this problem.
I have a table (Customers) whose PK (Customer_ID) is generated via autovalue. I have created a form for simple text entry but have left the Customer_ID field off the form because it should be generated with each record; however, every time I try and input a record through the form I get the following error:
Code: Select all
Error: Attempt to insert null into non-nullable column: Column: Customer_ID table: Customers ... *stuff*
I'm not a stranger to SQL so I know that this error means that the form is trying to execute an INSERT INTO command without a required variable: but this is supposed to be generated automatically with each record. I'm at a loss as to how to fix this!
Your help is greatly appreciated!
-Justin
Re: Creating forms for data entry
Posted: Wed Jul 11, 2012 7:29 pm
by Villeroy
... but this is supposed to be generated automatically with each record.
menu:Tools>SQL...
Code: Select all
ALTER TABLE "Customers" ALTER COLUMN "Customer_ID" INT GENERATED BY DEFAULT AS IDENTITY
Re: Creating forms for data entry
Posted: Wed Jul 11, 2012 8:08 pm
by TotalCareChiro
Villeroy wrote:... but this is supposed to be generated automatically with each record.
menu:Tools>SQL...
Code: Select all
ALTER TABLE "Customers" ALTER COLUMN "Customer_ID" INT GENERATED BY DEFAULT AS IDENTITY
Error received:
Code: Select all
1: Column is referenced in constraint or view: SYS_REF_SYS_FK_100_104 in statement [ALTER TABLE "Customers" ALTER COLUMN "Customer_ID" INT GENERATED BY DEFAULT AS IDENTITY]
Is the database read only?
Re: Creating forms for data entry
Posted: Wed Jul 11, 2012 8:54 pm
by Villeroy
Remove the relation and try again.
Code: Select all
ALTER TABLE "Other Table" DROP CONSTRAINT "SYS_REF_SYS_FK_100_104";
ALTER TABLE "Customers" ALTER COLUMN "Customer_ID" INT GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE "Other Table" ADD CONSTRAINT "Customer_Foreign_Key" FOREIGN KEY ("Customer_ID") REFERENCES "Customers"("Customer_ID");
Re: Creating forms for data entry
Posted: Wed Jul 11, 2012 9:54 pm
by TotalCareChiro
Villeroy wrote:Remove the relation and try again.
Code: Select all
ALTER TABLE "Other Table" DROP CONSTRAINT "SYS_REF_SYS_FK_100_104";
ALTER TABLE "Customers" ALTER COLUMN "Customer_ID" INT GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE "Other Table" ADD CONSTRAINT "Customer_Foreign_Key" FOREIGN KEY ("Customer_ID") REFERENCES "Customers"("Customer_ID");
Attempted this code, received the following error:
Code: Select all
1: Constraint not found SYS_REF_SYS_FK_100_104 in table: SALES in statement [ALTER TABLE "SALES" DROP CONSTRAINT "SYS_REF_SYS_FK_100_104"]
Under the suspicion that it may be the other table related to Customers, I altered the code to affect the other table: Treatments and received:
Code: Select all
2: Constraint not found SYS_REF_SYS_FK_100_104 in table: Treatments in statement [ALTER TABLE "Treatments" DROP CONSTRAINT "SYS_REF_SYS_FK_100_104"]
Further tested by eliminating the second and third ALTER TABLE commands and received the following error:
Code: Select all
3: Constraint not found SYS_REF_SYS_FK_100_104 in table: SALES in statement [ALTER TABLE "SALES" DROP CONSTRAINT "SYS_REF_SYS_FK_100_104"]
Finally, altered code line 1 to read:
Code: Select all
ALTER TABLE "Customers" DROP CONSTRAINT "SYS_REF_SYS_FK_100_104"
and received this error:
Code: Select all
4: Attempt to drop a system constraint in statement [ALTER TABLE "Customers" DROP CONSTRAINT "SYS_REF_SYS_FK_100_104"]
Here is the graphical table of relations for reference.
Do you think that it would be easier to just scratch everything and start over? I don't have more than a couple test entries in the tables. Not worried about preserving data because it isn't real O_&. Suggestions?