[Solved] Automatically display AutoField value before saving

[LarrySabo]

Greetings,

I have a very simple inventory table containing AssetID as an Integer, AutoValue field and a form to be used for data entry. The AssetID field in the form displays as "AutoField" when the form is opened for entry of a new record's data. How can I have the AssetID value automatically display before entering the data and saving the record?

The AssetID (a consecutive number) will be written onto the inventory item, which will then be described in the rest of the form.

I've been reading so many posts of how it might be done, that my head is swimming and I'm still clueless. Any help will be gratefully received.

[Villeroy]

Before saving there is no AutoField value.

[LarrySabo]

Thanks for your reply. Is it possible (before entering data into the form) to display what the value would be if it were saved? That way, I could write that value onto the asset, enter the descriptive details, then add another record or save the current record without having to save an empty record and re-open it just to get the asset ID number?

[MTP]

You might be able to use SQL like

Code: Select all

SELECT MAX("AssetID") + 1 FROM "YourTableName"

[Sliderule]

Just as an FYI, the solution offered by user MTP above, may work in most ( some ) instances, it also, may NOT work in other instances.

The reason is because, an AUTOVALUE generated by the database back-end ( HSQL or any other database engine ) is stored internally in the database engine. And, it will always be assigned the next value in the database engine. However, if the user INSERTed a new row, and, then deleted it, the NEXT value might NOT be MAX("AssetID") + 1

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[LarrySabo]

Thanks kindly for both of your replies. @MTP, is there a way to have that command invoked automatically when I open the form for data entry? Will the value automatically be displayed in the field that now reads "AutoField"? If it were automatic, I suppose each time I open the form, the value would be incremented even if I just browse existing records. In that case, when I do want to create a new record, the value might be substantially higher than one higher than that in last record created.

Amazing how such an apparently simple requirement can be so complex to fulfill (for an an old guy like me. (I used to be an assembly language programmer at IBM but that was almost 50 years ago.)

[MTP]

You can have that SQL as the source for a control field. I think you'd want to drop the field that now reads "AutoField" and replace it with a field that displays the results of that SQL command. It doesn't actually write anything to the table (you're not allowed to write to an AutoField), so you wouldn't have to worry about browsing adding any increments. This would require one field to be used to see existing AssetIDs and a separate field to be used to see the next ID in line.

If you are using the same form to browse existing records as to enter new ones (so you'd want the field to display existing AssetIDs in addition to displaying the next integer on a new row), that would be more complicated. I'd need to do some testing on something like the form you are using before offering any suggestions.

[LarrySabo]

Thanks again, MTP. The form I am designing is as shown here: http://prntscr.com/550vyd. The Drive Ref. # is the AssetID field. I'll try what you have suggested, see how that works and report back.

Cheers, Larry

[LarrySabo]

Before continuing with this thread, I have to read up on macros, SQL commands and variables. I created an SQL statement as suggested and it ran successfully, but I have no idea where to find the result and how to incorporate it into the form. Not knowing whether or where there is a separate variable for the result of the SQL command, I added a numeric field to the table (and to the form) called "Max AssetID" but have no idea if I should have done that nor how to populate it. Running the SQL command doesn't fill it, but then, why would it since it's not specified in the command?

So I'm hopeless at this point until I do some reading. I'll come back when I either have some progress to report or have given up hope completely.

[LarrySabo]

Okay, I have given the query an alias for the result ("Max AssetID") and when I double-click the query, it shows an appropriate value. Now, I just have to figure out how to trigger this query and have this result appear on the form when I want to add a new record. The form now looks like this: http://i.imgur.com/j0UJXJ2.png. I'll keep reading.

[Villeroy]

Open your form.
Click the 5th button on toolbar "Form Design" to get the form navigator.
Right-click the your form and add a new subform.
Bind it to the max query.
Add a text box to display the number.

Subforms update automatically every time when the parent form updates. In this case you don't need any binding (Parent.X=Child.X) between the two forms because the subform always shows the same record with a changing value.

[LarrySabo]

Thanks Villeroy, I'll try that.

[LarrySabo]

Hi again,

My Query looks like this: http://prntscr.com/563b54
I added a subform as shown here: http://prntscr.com/5633r1
Created a text box as shown here: http://prntscr.com/5635h2
When I exit Design mode to look at the data view, I get:

Code: Select all

The data content could not be loaded.
Not a condition in statement [SELECT MAX( "AssetID") +1 AS "Max
Asset ID" FROM "Drives On Hand" WHERE ("Max Asset ID" =
:link_from_Max_Asset_ID)]

Suggestions?

[Villeroy]

Code: Select all

SELECT MAX( "AssetID") +1 AS "Max Asset ID" FROM "Drives On Hand"

adds 1 to the maximum AssetID

[LarrySabo]

That's the plan, but the Max AssetID field in the table remains empty, even though the script runs okay (as an SQL command). My script reads exactly as you have written it (SELECT MAX( "AssetID" ) + 1 AS "Max Asset ID" FROM "Drives On Hand"). I have no idea whether Max AssetID is a system variable or a table field. I created it as a table field, thinking it would be filled with the maximum AssetID when that script runs. I also gave the script that name as an Alias.

I think I'll just throw in the towel and enter data, save the record, then re-read the record to verify the AssetID value. It's all too much for my feeble brain; I'm down to my few remaining synapses so will quit before I'm completely brain-dead. Thanks anyway for the helpfulness.

Edit: I got it to work! All I had to do was delete the field "Max Asset ID" from the Drives On Hand table! Thanks for all the help!
Edit #2: Whoops! That was premature. It displays on all existing records but not when I want to create a new record. Argh!!!!!

What I'll do, is just go to last record (>|), note the "New Record Asset ID" (which is what I've labelled the computed field), write that number on the item, press New Record then complete the form. Good enough. I'm done, for now. Thanks again. Here's how the form looks now: http://prntscr.com/565f5q

Edit #3: If I delete records, the "New Record Asset ID" field changes to equal the number of remaining records+1, which is not the number given when I create another record. However, when I click Save, the Asset ID and the just-entered data displays, so that's good enough.