[Solved] Pre-initializing a date on a form

[johncraw]

I am trying to work my way through a Base application. This will be used to enter and manage charges. I am now at the entering stage. Here is what I am trying to do:
- Open a form for input of a new record. The data is based on an existing table. This I can do
- There is another field that will hold the account for the charge. This is a listbox, also loaded from a table.

This I cannot do
- When the form opens I want a field initialized with today's date. I can overwrite this date if needed.
- When I enter the first record and add it:
- The record will be added
- The form will point to a new record.
- The date will be the last entered date
- The listbox will contain the entry of the last record.
I cannot find sufficient information to answer this. If anyone has a solution, or can point me to the correct source, I would appreciate it.

Thanks in advance.
John Crawford

[Villeroy]

This can be (mostly)achieved with DEFAULT CURRENT_DATE on database level and a subform for any new record. The main form allows for editing, the subform allows for insertion of new records using the parent form's master fields by default.

For the default date on database level: Tools>SQL...

Code: Select all

ALTER TABLE "Table1" ALTER COLUMN "Date" SET DEFAULT CURRENT_DATE

download/file.php?id=22304 contains 2 forms with different solutions to the problem. Date and Category are taken from a parent form. The user adds Value only to any new record.
The above SQL statement adds the current date to field "Date" in "Table1" of that database.
Database-menu:Edit>Database>Advanced... "Form data input checks for required fields"=OFF (otherwise the Base form would complain about the missing date).

[johncraw]

Villeroy:
Thank you for your response. I guess I need to understand more before I can ask the right question.
I ran the query, but what is it supposed to do? It completed successfully, but I see no difference when I try to add data.
I downloaded the link that you sent. It does not seem to do what I need either. The main form is loaded from the "filter" table. What I want is it to load today's date. I had hoped that if I entered the current date, into the date field, (for example 12/27/13) that on the 28th it would indicate 12/28/13. No such luck.
I use a macro when the form loads, to point to a new record. At that point I want to fill the date value with today's date. It looks, from your explanation that, the query will set the value, but that sets it in the database. I need it set on the form.
If this makes any more sense, perhaps there is some other insight. Otherwise, I will just go away and try to figure out what is the correct question to ask.
It is frustrating that I have not found, so far, one document that describes object/item--> properties-->methods. It seems that I am forever experimenting (sometimes correctly sometimes not), guessing, or finding an example and trying to modify it so it accomplishes what I need. For example, when setting a date control on a form there are two properties "repeat" and "Delay". I cannot find any information on the purpose of these items. This makes it very difficult to learn.
John

[Villeroy]

I ran the query, but what is it supposed to do? It completed successfully, but I see no difference when I try to add data.

Open my table "Table1" and add new records without entering any date. When you save the record or move to another one, the empty date field gets today's date. The same happens when using a form.
The first form document's main form is bound to record #0 of table "Filter": SELECT * FROM "Filter" WHERE "FID"=0.
Any subform is filtered by the parent form's master fields AND it inherits the master field values when inserting a new record. BUT this requires that the parent form is bound to some table. The "Filter" table fulfills this annoying requirement. No more, no less.
The subform is bound to the actual data table and set up to take new records only. Notice the >* icon on the row selector.
--------------------
Then there is a second form document which has the actual data table as main form and a subform bound to the very same table appending new records only by entering a new Value. Date and Category are taken from the parent form's master fields in the currently selected row.
When you move the tabular main form to the new record and enter a new value to the blue subform, the entry will fail because there is no Category and all the fields are mandatory (NOT NULL).
---------------------
I assume that you ran the DEFAULT CURRENT_DATE query in my database and that you turned off the form validation (Edit>Database>Advanced...)

- When the form opens I want a field initialized with today's date. I can overwrite this date if needed.

Open my second form document, navigate to the tabular main form's new record and enter some record with a Category, a Value and any Date or none. Without Date the new record will get the current date.

- The record will be added

Yes, it will. Keep this new record selected for further reference.

- The form will point to a new record.

The blue subform always points to a new record inheriting its slave field values from the parent form's master fields (see data properties of the subform).
Now enter new Values into the blue box.

- The date will be the last entered date
- The listbox will contain the entry of the last record.

This is what happens for each Value you enter into the blue box.
Select another record in the tabular main form and the blue box will add new values with the same Date and Category as selected in the main form.

It may be irritating that you don't see the new records until you refresh the tabular main form (which may be "expensive" and navigates to the first record).
You may add some navigation controls to the subform or enable the navigation toolbar which allows you to navigate back to this session's newly entered values.

[johncraw]

Thank you. I "think" I understand.
I will need to look at it further to really understand. Your explanation has helped greatly.
John

[Villeroy]

Screenshot of navigator and subform properties for new data only with default values from master fields.

Main form and subform are bound to "Table1".
The subform takes the Date and Category from its parent.
"Add data only" is the important property here.

[MikeytheMagnificent]

One small point Mr. Villeroy
Your form1 wont give the correct date, as is, because for reasons unknown you applied limits in the date control min and max properties from 1/1/2007 to 31/12/2010 so if I remove date checking as you asked TODAY defaults to 1/1/2007 if If the box is left blank, conversely if I replace checking and add a date out of range say 12/12/2013 that reverts to 31/12/2010 but i I do checking but put none for date I get your data input error as you said.
Form 2 is a bit basic with no provision for calendar controls or dropdown option picking and would suggest if rejecting the defaults becomes normal the form becomes difficult. if the default date is always accepted then hiding the date field removes that tabstop because strangely even if you had made the visible date read-only it does not remove the tabstop , perhaps its just a bug, perhaps not, can anyone comment
I abandoned a very similar thread some 3 months ago. I need to grab the time when I create each new record and again as when it's completed .I'll start that thread again tomorrow. Please keep an eye out

[Villeroy]

Thank you very much. I don't know how the limitatation got into that form. Sometimes I copy form controls carelessly from other documents, sometimes I don't think carefully enough. Hundreds of quick&dirty database demos do have errors. I'll replace this .odb later today.
Done: download/file.php?id=22304

[MikeytheMagnificent]

The main thing is that others, particularly novices are helped where anomalies and mistakes like this creep in and knowing how to self-correct them to improve their own skills.
Now what about the read-only nonesense of which I spoke. Is it a bug?

[Arineckaig]

if you had made the visible date read-only it does not remove the tabstop , perhaps its just a bug

FWIW and as I understand it, the tabstop property is available to a grid/table form control but not to individual column form controls models contained within a grid/table: possibly a bug, but could also be a desirable feature.