Avatar of Hiken
Hiken
Flag for United States of America asked on

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", "tSetDefaults")
    DoCmd.TransferText acImportFixed, "TCN Import Specification", "tTCN", epath, no

End Function




Microsoft Access

Avatar of undefined
Last Comment
Hiken

8/22/2022 - Mon
Rey Obrero (Capricorn1)

Public Function ImportTCN()

Dim epath As String

    epath = DLookup("TCNImportLocation", "tSetDefaults")
    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
Hiken

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.  
Rey Obrero (Capricorn1)

you should have specified that in your original post.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Hiken

ASKER
sorry I didn't explain it well enough on the first
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Hiken

ASKER
Thanks that did it