I have a functions that adds a sequence number to a table, what i need to do that I do not have the expertize is based on the FT7CPT look up another table and create a record depending on what type of person it is. If it is an medx than I need to create a record with a pro fee and facility fee. if it is not than i need to create a record with a global fee. In each field is the xref to the item i need. so i need to replace the FT7CPT with the item number and create a new row. here is the code i used to sequence the items in the table.

Public Function SetChargeItem()

Dim db As Database
Dim rs As DAO.Recordset

Dim currentCode As String
Dim seq As Long


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tmp_FT_seg ORDER BY TBL_DAILY_BILLING_NAME, Patno, TBL_DAILY_BILLING_FSEQ, SEQUENCE, FT7CPT")

currentCode = ""

With rs

Do While Not .EOF

If currentCode <> !PatNo Then
currentCode = !PatNo
seq = 1
Else
seq = seq + 1
End If

' Based on FT7CPT code need to lookup the value in another table called TBL_INV_IMPORT
' tbl_inv_import has four columns CPT Code, Profield, Facilityfield, and global field.
' Need to return the value in one of three fields into FT7cpt depending on the following
' if medx than need to create one record with number in pro field
' if medx need to create another record with number in facility field
' if not medx than need to create one record with global field
' loop through each tmp_ft_seq record performing this routine..



.Edit
!sequence = seq
.Update
.MoveNext
Loop

End With

Set rs = Nothing
Set db = Nothing

End Function