need help with auto number etc...

BradyL

New member
Local time
Today, 23:21
Joined
Jun 5, 2002
Messages
9
All,

I had originally started this topic in the Forms Forum, but to my discovery it has nothing to do with forms and my question has really changed, so I thought I'd ask here...

My table callled licenses has a primary key field called licenseID. I am connecting to postgreSQL server. When I send all the required fields (minus the licenseID) to the server, the server generates an autonumber for the licenseID. This is working properly as immediately after I hit the enter key in the access table, the server shows the new record with the proper licenseID.
HOWEVER, after hitting the enterkey, the new record is displayed in ACCESS as an exact duplicate of the previous record. The newly added record cannot be found in the table unless the table is sorted, opened and closed, etc...

Also, if I enter a number for the licenseID rather than letting the server take care of it, there are no problems and no duplicates. The the only issue is when adding new records without the licenseID.

Bringing the autonumber to the access side is not an option as the table properties can't be changed. And again, the record is displayed correctly on the SQL side so I'm pretty sure the problem is within Acces. Any help would be greatly appretiated.

Thanks!

Brady
 
I think you must have some code behind your form that is causing the problem. So, I'll just make a few explaintory comments.

When your form is based on a linked ODBC table (or query of that table) and the table has an autonumber primary key - the only way for you to "see" the newly generated autonumber (without closing and reopening the form) is to place a save button on your form. In the save button, use the following to save the current record:
DoCmd.RunCommand acCmdSaveRecord
After the record is saved, the assigned autonumber should show in the bound autonumber control. This behaviour is different from a linked or local Access table. The autonumber assigned in this case will show immediatly as soon as any character is typed into any control on the form.

If you are not getting an error message when you attempt to input a duplicate autonumber yourself, it is because the autonumber is NOT defined as a primary key or unique index in your SQL server table.
 
Pat and others,

First, thanks for the response Pat. A few remarks to your comments. While the problem arose at the form level, I don't think the problem is with code or the form, since the same exact problem occurs at the table level (as I attempted to describe in my first message). In reference to your last question, the server does not allow duplicate ID fields to be entered.

If I use that save command, or enter the data directly into the table and hit choose saveRecord from the menu, it "appears" that a duplicate has been created.

Any more help would be great.

BR,

Brady
 

Users who are viewing this thread

Back
Top Bottom