Very strange (?)

[an]

Hello!

I have T_A as base to F_A, with records A1 to A9;
and T_B as base to SubF_B, with records B1 to Bn.

In adition, the relathionships T_A with T_B, is one to
many through numeric field.

When I add new record with command button in F_A the
record A1 in T_A, is automaticly renumbered to A10 and new
Records is 11, when it would have to be A10.

Why? And as I can decide this, please?

Thanks in advance.
an

 

[Michel Walsh]

Hi,


You use an Autonumber? don't use them to get an INTERRUPTED continuous
sequence. As example, if you UNDO the new record WITHOUT saving it, then, a
new new record will use 12, leaving you with a hole for 10, and 11, deleted
records (or undone while new). You can compact the database, that may gives
back 10, for the moment, after the compaction, but will you oblige yours
users to always compact the db after an undo of a new record? no.

Use 1+DMax("myField", "myTable"), make the field UNIQUE (no duplicated
values) and, in a multi-users environment, trap onError Form event:

if two users read the DMax, they get the same value, but only one will
be able to "save" the record. The other user will get an error, trappable in
the FORM onError event subroutine. That second user is likely to see the
VBA code simply recompute

1+DMax(...)

and will try to save the record, again. That complexity is required only in
a multi-users environment.




Hoping it may help,
Vanderghast, Access MVP

 

[an]

Thanks for your reply.

I have Number fields in relationships.
I tried your solution but obtained next error:

Compile error:
Expected: label or statement or end of statement.

(?)
an

 

[Michel Walsh]

Hi,


If the field is used as control source for the form control, you can
use, in the Form onCurrent:

If Me.NewRecord then
Me.Id= 1+DMax("id", "tableNameHere")
End If


I assumed the control name is "id", and that the field name is also "id".


Does that generates an error?



Vanderghast, Access MVP

 

[an]

Exactly, MW.

Work fine.
Many thanks.
Have you a good weekend.
an