Assigning order numbers to a record

[tpowers]

Hello everyone I am trying to assign ID numbers to certain records in my database and I have already run a query that tells me which records need these ID numbers and I have alread created the ID numbers in another table. Now I am trying to assign these ID numbers to the certain records, with out creating duplicate ID numbers.

example:

Record Number ID Number
123 321
555 444
876 678

I am using access 2k,

Thank you very much for any help in advance,

TPowers

 

[formerTexan]

Hi

If your question is simply how to prevent adding duplicates, then open up the appropriate table in design view, go to properties of the field in question and set it to disallow duplicates.

If your questions go beyond this, then please return and elaborate.

Cheers,
Bill

 

[tpowers]

Yes, my question goes beyond the duplicate issue, I have 2 tables and these 2 table have nothing to do with each other, there is a series of numbers in one table that need to be assigned to the series of numbers in the second table. So in the first table there is say numbers 1 - 100 and in the second table there is numbers 5560 - 5660. I am trying to get the 1 - 100 assinged to the 5560 - 5660. Meaning 1 = 5560
2 = 5561
3 = 5562
4 = 5563
I hope this makes more sense....

Thank you for your help in advance,

TPowers

 

[gearybish]

If what you are trying to do is replace 5560 with 1, 5561 with 2, and so on in table 2 then I would try this:

Public Sub Update_tbl2()

Dim Db As Database
Dim rst As DAO.Recordset
Dim intNewNum As Integer
Dim intOldNum As Integer

Set Db = CurrentDb
Set rst = Db.OpenRecordset("tbl2")

If rst.EOF Then
Exit Sub
End If

rst.MoveFirst

intOldNum = 5560
intNewNum = 1

Do Until rst.EOF Or intNewNum > 100
If rst!tbl2Id = intOldNum Then
rst.Edit
rst!tbl2Id = intNewNum
rst.Update
intOldNum = intOldNum + 1
intNewNum = intNewNum + 1
End If

rst.MoveNext

Loop

rst.Close

End Sub

 

[TheAceMan1]

How are ya tpowers . . . . .

For both tables, are the fields you've stipulated [blue]non-primarykey types[/blue] with [blue]each table having its own seperate primarykey?[/blue].

Or are they [blue]both primarykeys[/blue] . . . or what?

See Ya! . . . . . .