Temp/Main RecNo Increments

[Praveen Manne]

Hi

I have 2 tables. One is the main and second is temp. Both the tables are
identical in structure and data. I will do all the calculations in the temp
table and post all the records in the temp table to the main table, when I'm
finished with my calculations. Here I have a problem ..

Both the tables contains RecNo as its primary key (as both are identical)
and which is an autoincrement fields.

for eg:
I have 10 records whose RecNo are 1, 2,3 ..10 in the Main table.
and when I open the form for the TEMP table, I want the RecNo field to be
updated with value 11
and if I go to second record in the Temp table, the RecNo should be 12 and
so on ......


How is this possible? What is the code for doing this? Please Help

Thanks
Praveen Manne

 

[GVaught]

The temp table should not have a primary key nor be set as Autonumber. As
long as you are appending the records to the main table, the main table
needs to set the primary key value.

If you expect the temp table's to update matching values in the main table,
then you have a different issue. You will have the RecNo in the temp table,
but it won't be set as a primary key nor autonumber. It will be set as a
number field. The values entered in the temp table must match to the
corresponding record value in the main table. Then you must write VBA code
that will update the main table record based on the temp table's matching
record or SQL statement that will issue an Update query.

 

[John Vinson]

Hi

I have 2 tables. One is the main and second is temp. Both the tables are
identical in structure and data. I will do all the calculations in the temp
table and post all the records in the temp table to the main table, when I'm
finished with my calculations. Here I have a problem ..

Ummm... why?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Are you specifically storing the calculations for some purpose that
you haven't mentioned?

Both the tables contains RecNo as its primary key (as both are identical)
and which is an autoincrement fields.

I don't think that you can have an autonumber "spanning" two tables.
You might want to use a Long Integer field and increment it
programmatically; you could use a Form to enter the data (table
datasheets don't have usable events) with code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Long
iNext = DMax("[RecNo]", "[MainTable]")
If iNext > DMax("[RecNo]", "TempTable]") Then
Me!txtRecNo = iNext + 1
Else
Me!txtRecNo = DMax("[RecNo]", "TempTable]") + 1
End If
End Sub

 

[Praveen Manne]

Hi John,

Thanks for explaining that to me. It worked. Thanks!

Praveen Manne

Hi

I have 2 tables. One is the main and second is temp. Both the tables are
identical in structure and data. I will do all the calculations in the temp
table and post all the records in the temp table to the main table, when I'm
finished with my calculations. Here I have a problem ..

Ummm... why?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Are you specifically storing the calculations for some purpose that
you haven't mentioned?

Both the tables contains RecNo as its primary key (as both are identical)
and which is an autoincrement fields.

I don't think that you can have an autonumber "spanning" two tables.
You might want to use a Long Integer field and increment it
programmatically; you could use a Form to enter the data (table
datasheets don't have usable events) with code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Long
iNext = DMax("[RecNo]", "[MainTable]")
If iNext > DMax("[RecNo]", "TempTable]") Then
Me!txtRecNo = iNext + 1
Else
Me!txtRecNo = DMax("[RecNo]", "TempTable]") + 1
End If
End Sub