Autonumbering field

[Lourry]

Hello All,

my form is connected to an external data source in Oracle through ODBC. When I link my form to the Oracle table, it automatically maps the datatypes in Access 97. However, there is a field called "ID" which is an autonumber field but when I link it to Access, it mapped the datatype to text. And when I try to change it to autonumber, it says I can't change property on a linked table.

Now, when I create new records, that field is blank since it doesn't do the autonumbering. Is there a way to change it to autonumber or is there a code that I can write so that when the user clicks on the add record button, that field is automatically populated by a number that is the ID number of the last record plus 1??

Thanks in advance!
-Lory

 

[scriverb]

If the field is designated as an AutoNumber field in Oracle why is it not autoincrementing by itself when you perform an add new record? I am not an Oracle programmer but that makes no sense to me.

If you need to manipulate it yourself then you can use the DMax function to retrieve the maximum value from the table and increment it with code before you add the record.

DMax("[autonumberfield]", "[tablename]") + 1

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Lourry]

Thanks sciverb!

I put in that code in the Add Record button as

numID = DMax("[ID]", "[Details]") + 1
Me.txt_ID.Value = numID

It gave me an error:

Circular reference caused by alias 'ID' in query definition's SELECT list

 

[scriverb]

Let's just try getting the value that we need. Let's put a stop in the code and see if the DMax function is retrieving and incrementing the value properly. Just put a stop after the code listed above and using the Immediate window check for the value of numID.

If it gives the correct value then the problem exists in adding the records to the Oracle table.

let me know what you find.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

I am inclined to think that you shouldn't be messing with this autonumber field at all. Just update the other fields on the form and save the record. Oracle should autoincrement the field when the record is added. Don't worry about what ACCESS does to the field type when it shows up in the Linked table field description. Have you tried that?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Lourry]

I tried not putting anything in the ID field but it doesn't get populated and Oracle complains since that field cannot be null when added to the table. So, i know Oracle doesn't do the autonumbering.

I stop after the code and numID gets the value NULL

Is there some syntax problems as I am using Access 97.

Thanks again!

 

[scriverb]

Let me ask you. What is the table name in Oracle and then look at the LINKED table name in ACCESS. Are they the same? I'm betting that they are not. The DMAX function needs the name identfied in ACCESS to function correctly.

Check this out and get back with me.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Lourry]

Thanks for the quick reply.

I am using the name that is in Access. The name that shows up under the Table tab.

 

[Lourry]

The field in Oracle is VARCHAR2 and it got mapped as TEXT in A97. Does this make a difference since it is not a number field?

 

[PHV]

The field in Oracle is VARCHAR2
So it is definitively NOT an autonumber.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

PHV: Thanks for that. Any ideas about the DMax function not coming up with the largest value in the [ID] field in this ODBC linked table. I don't have the ability to set up an ODBC connection here at my office so I can't test it out to get it right. With this datatype it looks like we must insert the value manually. So, if the DMax function doesn't work then a recordset rs.movelast may be the answer.

Ideas?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Lourry]

I tried using SELECT MAX(Details.ID) AS Biggest FROM Details but it doesn't return anything, then I tried val(SELECT MAX(Details.ID) AS Biggest FROM Details) and it returns a 0.

Scriverb: If I use rs.movelast, how do I get the last record's value of the ID field so I can increment it by one?

Thanks again for all the help!

 

[scriverb]

You could give this a try:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim vMaxRecordNumb as Long
Set db = CurrentDB
Set rs = db.OpenRecordset("[i]yourtablename[/i]", dbOpenDynaset)
rs.MoveLast
[red]Me.txt_ID.Value = rs("ID") + 1[/red]
rs.close
db.close

Don't know if this will do it for you but, give it a try.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Lourry]

it works!!

THank you soooo much Scriverb!! Really appreciate all the help. Thanks again!

 

[scriverb]

Great. Glad to be able to help you. You see in Oracle there is an object that keeps track of the next sequence number. Probably the Oracle programmers are using that object to populate the ID field. But, while here in ACCESS we don't have the ability to use it. So, this code should work for you.

Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]