Continuous form textbox pre-population

[mcclunyboy]

Hi,

I am really hoping to use a continuous form to allow users to quickly enter any number of records. One of the fields in this continuous form is an ID (not a foreign key, actually not a key in the database but crucial for other purposes) which is incremental from the last one stored in a table.

I am able to use a combo box and a query to get the ID but I cannot save the value to the text box on the first record, and then requery and get the next id when the user moves onto the next record..and so forth...

Can anyone advise a method for resolving this?

Thanks

 

[CJ_London]

why not just use an autonumber?

 

[mcclunyboy]

The ID is updated outside of the database, whilst the database will have 99% of the records related to this "ID" it will not be 100%. An Autonumber would be wrong if an ID is incorrectly used/amended/added outside the database without being internal. The ID source has also changed twice in a year as it is being worked on, originally it was prefixed by alphanumerics but now it is an integer. I could change it to autonumber but then would I lose the data already in the table (thousands of records), whilst these have a value in this ID field they are not sequential/indexed by it (the table has other primary/foreign keys).

The ID "relates" the records in the database to another system, a foreign key but not within Access. The current field is a "Number" set as Long Integer.

Is it possible to change the field to autonumber without losing existing data?

 

[CJ_London]

Is it possible to change the field to autonumber without losing existing data?

Yes - subject to the existing data meets autonumber rules i.e. is a long type or smaller and there are no duplicates.

If you are not sure, take a copy of the table and edit the copy to see what happens.

 

[mcclunyboy]

ultimately that didn't work for me, first of all Access complains about editing an existing field to Autonumber but even so the column does contain the odd null and out-of-range integers.

Regardless I managed to get a solution working by using the "after-insert" event of the form control. Simply requerying the combo box query works perfectly so far.

Thanks