One front End - Multiple Backends

[John W. Vinson]

1. Open Form A and SubForm A:
2. Pick Items in subform which populate Table A: Auto#: 1, 2, 3 and 4
3. Click some button...then Transfer Values in Table A to Table K
4. Table K now as numbers 1, 2, 3, 4
5. Time goes on...1 thru 4 goes on daily
6. Now 6 months later I compact the back-end db
7. Open form A and Subform A:
8. Pick Items in subform which populate Table A: Auto#: 1, 2, 3 and 4
9. Click some button...then Transfer Values in Table A to Table K (now I have
TWO, 1's, TWO 2's, TWO 3's, etc...This is the problem...if I understand what
compacting does...

Thanks for the explanation.

All I can say is... Autonumbers don't work as you are assuming, and should not
be used in this way. You CANNOT make *ANY* assumption about what the value of
an as-yet-unassigned autonumber might be, other than that (barring bugs, which
did happen in some releases) it will not duplicate any existing value in its
table.

If TableB has an Autonumber field, and you don't have any relationships
established on the TableA autonumber value, you could change the append query
in (3) to include all fields EXCEPT the autonumber. TableB's autonumber would
assign values 5, 6, 7, 8 - or 312, 313, 314, 315 - to the records transferred
from TableA. Would this resolve your issue?

 

[disneygoof via AccessMonster.com]

I don't know it will help or not...I need to dig into it. I am not assuming
what the number will be, or at least that's not my intension. Again, I need
to get back into what was happening and see what I need to do to correct it.
I am sure I am not explaining or understanding something right...

Thanks John...

David

1. Open Form A and SubForm A:
2. Pick Items in subform which populate Table A: Auto#: 1, 2, 3 and 4

[quoted text clipped - 7 lines]

TWO, 1's, TWO 2's, TWO 3's, etc...This is the problem...if I understand what
compacting does...

Thanks for the explanation.

All I can say is... Autonumbers don't work as you are assuming, and should not
be used in this way. You CANNOT make *ANY* assumption about what the value of
an as-yet-unassigned autonumber might be, other than that (barring bugs, which
did happen in some releases) it will not duplicate any existing value in its
table.

If TableB has an Autonumber field, and you don't have any relationships
established on the TableA autonumber value, you could change the append query
in (3) to include all fields EXCEPT the autonumber. TableB's autonumber would
assign values 5, 6, 7, 8 - or 312, 313, 314, 315 - to the records transferred
from TableA. Would this resolve your issue?

 

[John W. Vinson]

I am not assuming
what the number will be, or at least that's not my intension.

Well... it appears that you are. You say

8. Pick Items in subform which populate Table A: Auto#: 1, 2, 3 and 4
9. Click some button...then Transfer Values in Table A to Table K (now I have
TWO, 1's, TWO 2's, TWO 3's, etc...This is the problem...if I understand what
compacting does...


so you're expecting that an Autonumber added to TableA will somehow know not
to duplicate any records in TableK. That expectation is unrealistic.
Autonumbers added to TableA will have no knowledge of what's in TableK, and
there's absolutely nothing to prevent them from assigning duplicates.

I think you've just been lucky. Compaction WILL cause the problem; but so will
addint records to TableK outside your form, or running an append query on it,
or many other possibilities.

 

[disneygoof via AccessMonster.com]

Yes...many problems...and Yes, I have been lucky so far...but luckily I found
the problem and can correct it before something does do drastically wrong....
thanks