Update an Id field on import of data with my own id from another table Using VB
What I'm currently doing is importing a text file. The below code ImportTCN() works fine. What I would like this code to also do is update the id field, which will be null, to a number from my id table something like this:
tTcn!ID = Nz(dmax ("ID","tID")) +1
Public Function ImportTCN()
Dim epath As String
epath = DLookup("TCNImportLocation
DoCmd.TransferText acImportFixed, "TCN Import Specification", "tTCN", epath, no
End Function
tTcn!ID = Nz(dmax ("ID","tID")) +1
Public Function ImportTCN()
Dim epath As String
epath = DLookup("TCNImportLocation
DoCmd.TransferText acImportFixed, "TCN Import Specification", "tTCN", epath, no
End Function
ASKER
that is close, but what it is doing is only updating the last record. It also picked the last id number out of the table and added one. I changed it to dmin and it added 1 to it and made the last record 2. What I need it to do is Update the first record to 001 next record to 002 etc.
you should have specified that in your original post.
ASKER
sorry I didn't explain it well enough on the first
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks that did it
Dim epath As String
epath = DLookup("TCNImportLocation
DoCmd.TransferText acImportFixed, "TCN Import Specification", "tTCN", epath, no
'update the field
currentdb.execute "update tTCN set [ID]=" & nz(dmax("ID","tID"))+1 & " where [ID] Is null"
End Function