Autonumber on Primary key causing a problem?

[Tony Williams]

I have a form based on a tablea.
The form has a tabcontrol with 6 tabs. On each tab is a form that is based
on different tables, tablea, tableb, tablec, tabled, tablee, tablef. Each
form on each tab has a control based on the field codenbr in each table.
All the tables are linked on a control whose field has the same name in
each table, codenbr.
There is a control, codenbr, on my main form where I input a new record in
tablea and as I open each tab control a record is created in the other
tables and the value of each codenbr is populated with the value from my
main form.
On two of the tabs as the record is created in their forms there is also an
index created called hindex and vindex in the two tables table c and tablef.
These are needed to link these tables to other tables in the database that
are not on this particular form. The indexes are consecutive numbers in
Autonumber fields and are the primary keys of the two tables in question.
However as soon as I input any data into my main form I get an error message
to say that the Automation ID is not available. Obviously there is a problem
with the Autonumbering of these index fields.
Can I achieve the same end with the DMax function to automatically create
the consecutive values for each hindex and vindex ?
I'm using Access 2003
Hope I've explained that, would really appreciate some help.
Thanks
Tony

 

[John W. Vinson]

I get an error message
to say that the Automation ID is not available. Obviously there is a problem
with the Autonumbering of these index fields.

That's not obvious to me, at all. An Autonumber does not typically have the
name "Automation ID". Did *you* assign that fieldname to some field in one or
more of your tables? If not, I strongly suspect that there is some completely
unrelated problem with your code or with the database.

Can I achieve the same end with the DMax function to automatically create
the consecutive values for each hindex and vindex ?

Yes, using the form's Beforeinsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!hindex = NZ(DMax("[hindex]", "[tablename]")) + 1
End Sub

but I fear that this will leave the (autonumber-unrelated) Automation ID
problem unaffected.

John W. Vinson [MVP]

 

[Tony Williams]

Thanks John I'll try that.
Tony

I get an error message
to say that the Automation ID is not available. Obviously there is a
problem
with the Autonumbering of these index fields.

That's not obvious to me, at all. An Autonumber does not typically have
the
name "Automation ID". Did *you* assign that fieldname to some field in one
or
more of your tables? If not, I strongly suspect that there is some
completely
unrelated problem with your code or with the database.

Can I achieve the same end with the DMax function to automatically create
the consecutive values for each hindex and vindex ?

Yes, using the form's Beforeinsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!hindex = NZ(DMax("[hindex]", "[tablename]")) + 1
End Sub

but I fear that this will leave the (autonumber-unrelated) Automation ID
problem unaffected.

John W. Vinson [MVP]