[Solved] Macro to update form field

[joaofmateus]

Good evening everyone
I urgently need to build a macro that fetches the maximum number from one table and adds one more to it, and with the number obtained, it updates a certain field of the forms.
The table is the "tMOVIMENTOS"
The forms are the "fMOVIMENTOS"
The forms field is "ID_MOV" (the field must be blank for it to be updated)

Could someone help me?

[Nocton]

Some more information would be helpful and you may not need a macro.
When you say:
- "that fetches the maximum number from one table" is the maximum number a particular number in a field, the maximum number in a field list or is it the (maximum) number of records in the table?
- "it updates a certain field of the forms." and "The forms field is "ID_MOV" (the field must be blank for it to be updated)" then what is the name of the table containing ID_MOV that the form is connected to?

Also, why must ID_MOV be blank for it to be updated? Do you mean that you only want it updated once?

[joaofmateus]

Hello Nocto
After Christmas parties, I'm back.I'll try to explain what's going on:
1. For losing data I decided to work with HDBSQL as back end and Base as front end.
2. When importing the tables the "ID_MOV" table of the table "tMOVIMENTOS", which was self-incremented, is no longer, it will soon have to be introduced at the time of the launch, which is done through the form "fMOVIMENTOS"
3. Only. I want this to be done in new records, since the IDs of the existing records should never be changed, so I was hoping that, whenever the "fMOVIMENTOS" form (through which the "tMOVIMENTOS" table is maintained) was changed and the field "ID_MOV" was blank was updated with the last "ID_MOV" of the table "tMOVIMENTOS" + 1. I wanted this to be done through macro.
Did I make myself understood this time?

[F3K Total]

Hi,

When importing the tables the "ID_MOV" table of the table "tMOVIMENTOS", which was self-incremented, is no longer ...

That's not good, but can be fixed by executing the following SQL-commands via Tools/SQL ...:

Code: Select all

ALTER TABLE "tMOVIMENTOS" ALTER COLUMN "ID_MOV"  SET NOT NULL;
ALTER TABLE "tMOVIMENTOS" ADD PRIMARY KEY ("ID_MOV");
ALTER TABLE "tMOVIMENTOS" ALTER COLUMN "ID_MOV" INTEGER GENERATED BY DEFAULT AS IDENTITY

R

[Nocton]

Thank you, joaofmateus, for the extra information.
But you still have not said what you mean by "that fetches the maximum number from one table".

[Villeroy]

Another one who wants to control the creation of id numbers instead of leaving it to the database engine.

[Villeroy]

If you only want to display which ID is propably the next ID for a new record, then you may implement something like this: download/file.php?id=24806 where a subform queries the record count of some table. Just calculate Max(ID)+1 for your purpose.

[joaofmateus]

Thank you all for your help.

Following your suggestions, I have already retreated the table field to automatic and worked.
However, because a few years ago I had problems with "ACESS" with a field of this type, I preferred it to continue to be generated at the time of loading the record.

Together an example of what I intended with a RMG macro I did not get it to work.

If anyone can and will try to make it work, thank you in advance.

Here is the example link:
https://drive.google.com/open?id=1bHRBm ... F4GV7vyUzM

[Nocton]

I now see that you are trying to 'manually' update ID_MOV as the Primary Key for your table. Like Villeroy, I would strongly recommend that you let the database engine do the work. You say you had problems with ACCESS, however I think you are much more likely to get problems doing it this way than when using AutoValue. Over many years and many applications I have never had a problem with AutoValue in Base (or with ACCESS Either).

[joaofmateus]

Once again grateful to all. All solved and working.
If anyone is interested in seeing how, you can follow this link

https://forum.openoffice.org/es/forum/d ... hp?id=7667

[Nocton]

Pleased that you have achieved what you wanted to do. However, like Villeroy, I still think that the straightforward AutoValue would be best. Your solution allows the creation of empty records with a new ID_MOV but no other data - just keep clicking on the forward/next record button - so you need to fix that problem.

[Villeroy]

Indeed, far too complicated with no benefit.