vba - how to create sequence -

vba - how to create sequence -

i exporting file ms access table local, doing vba coding. need create sequence file names in such manner,

file1pn00001 file1pn00002 file1pn00003 ... ...

i doing next code

private sub command0_click() dim filename string dim intchoice integer dim strpath string dim lsproc querydef dim db database set db = currentdb() set lsproc = db.createquerydef("") 'make file dialog visible user strfilepath = browsefolder("please select path export neutrality study file to") if strfilepath <> "" phone call msgbox(strfilepath, vbinformation, "save path") else msgbox "please provide file path before exporting!", vbcritical + vbokonly end if filename = strfilepath & "file1pn" & format(txtbal_number, "000000") & ".txt" docmd.transfertext acexportdelim, , "t1", filename, false end sub

i confused how create sequence that, how create sequence , how increment value 1 every time when code runs. please help me out thanks.

you can sorted if have logging system. first need create table nil fancy, simple table 2 columns.

tbl_filenamelog -------------------------------------------------------------------------- field name | info type | comments ------------+---------------+--------------------------------------------- fileid | number | utilize auto number, future safe. | | utilize number, can edit it. | | create sure primary key. exportdate | date/time | date field store date.

now can edit code like.

private sub command0_click() dim strpath string, filename string dim lngchoice long dim dbobj database set dbobj = currentdb() 'make file dialog visible user strfilepath = browsefolder("please select path export neutrality study file to") if strfilepath <> "" msgbox "exporting file : " & strfilepath, vbinformation, "save path" else msgbox "please provide file path before exporting!", vbcritical + vbokonly exit sub end if lngchoice = nz(dmax("fileid", "tbl_filenamelog"), 0) + 1 filename = strfilepath & "file1pn" & format(lngchoice, "000000") & ".txt" docmd.transfertext acexportdelim, , "t1", filename, false dbobj.execute "insert tbl_filenamelog (fileid, exportdate) values (" & _ lngchoice & ", " & cdbl(date()) & ")" set dbobj = nil end sub

so first time when code run, max id in table. since there no entry create utilize of nz function , assign 0+1, id of 1 obtained. export happens specified location. @ same time entry file log set stating id has been assigned. next time when code runs, file log table since id of 1 availabe not utilize 2. on , forth....

this way not rely upon file system. has own log, if file moved or deleted still able provide consistent/continious numbering. hope helps !

vba ms-access access-vba ms-access-2003