Creating forms for data entry

[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

[F3K Total]

Hello,

(1) Can this be done?

Yes, for sure!

(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

[syouchnow]

Thanks for your help, that looks like just what I need.

Steve

[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

[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

[TotalCareChiro]

... 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?

[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");

[TotalCareChiro]

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?