Is it possible to create a form that only allows data entry to one column if another column is empty?
for example I have column A,B and C. Data should not be allowed in C if there is data in B and visa versa.
See only a solution per macro:
SUB SwitchEnabled(oEvent AS OBJECT)
oField = oEvent.Source.Model
oFieldTarget = oField.Parent.getByName(oField.Tag)
IF oField.Text = "" THEN
oFieldTarget.Enabled = True
ELSE
oFieldTarget.Enabled = False
END IF
END SUB
Name of the field, which should be enabled or disabled has to be added in “Additional Information” of the field properties. So both fields could use the same code.
Thanks letting me know. I have very little experience with macros. Where do I enter the code you have provided?
First go to Tools → Macros → Organize Macros → Basic. On the left choose your database file. Then press “New” to get a start.
Copy the code I have posted to the editor.
Save the code.
Open your form for editing.
Mark the first control where data should be decided to allow → right Mouse click → Control Properties.
In General → Additional Information write the name of the second control (something like “txtName” - you could see the name if you mark the other control or open the form navigator).
In Events → Text modified press the button with … . You could choose the macro.
Do the same for the second control. Name in additional information has to be the first control.
Hope this helps.
menu:Tools>SQL…
ALTER TABLE "table_name" CHECK (("A" IS NULL AND NOT "B" IS NULL) OR ("B" IS NULL AND NOT "A" IS NULL))
[Run]
menu:View>refresh tables
1 Like
@Villeroy : Setting a check constraint will work if you try to save the data from the form. But it won’t prevent for input data into both fields of the form. Handling for a user would be better: Don’t type anything in a field if the other field has content. Check could be a good solution if somebody will input data without a form. It will save integrity of data.
Remember also: Both fields could be NULL - or didn’t I understand the question the right way? So the code should contain “OR”, not “AND NOT”.
1 Like
I’m aware of this. However, if you really want to prevent invalid data, the CHECK constraint is the way to go. The database won’t accept both or missing values no matter if you try programmatically, by table editing or through a form. A form has to handle this restriction in order to be user friendly. This is what your macro does.
However, the table in question violates first normal form. it should be possible to avoid the whole problem with one mandatory column for either value A or B and a boolean which specifies if the column means either B or C.
Thought the same. Why two fields, where every time one of them should be NULL. One field and a boolean will be enough. Might be @goedible has created a table with fields with different data types…
Thanks all for the suggestions @Villeroy @RobertG and for questioning the purpose.
The purpose is:
-a form to input: {cultivation practices} + {dates} or {phenological event} on a table called [annual_cultivation].
-a form to input: {phenological_event} + {date} on a table called [Phenology_dates]
-a query to return {cultivation practice} + {date} on a table called [cultivation_dates]
the tables are done. Now I’m working on the query.
I use something very similar in a cash register. Apart from date, time, primary key, who, what, remark, there are 2 columns
| VAL | INCOMING |
|---|---|
| 50,00 € | TRUE |
| 28,39 € | FALSE |
| 30,00 € | TRUE |
| 250,00 € | FALSE |
| 211,27 € | FALSE |
| 32,00 € | TRUE |
| 6,99 € | FALSE |
| 6,26 € | FALSE |
| 100,00 € | TRUE |
A query can derive debit and credit from these two columns.
SELECT *, casewhen("INCOMING", "VAL", NULL) AS "Credit", casewhen("INCOMING", NULL, "VAL")AS "Debit" FROM "PAYMENTS"
or the actual value of the transaction as positive or negative amount.
SELECT *, "VAL" * casewhen("INCOMING", 1, -1) AS "Amount" FROM "PAYMENTS"