Solved Best way to concatenate a file name

[HillTJ]

Friends, this is probably quite simple, but i thought i'd get your recommendations. I have a form PK 'QuoteID'. On that form is a bound continuous subform FK 'QuoteCommentID' my intention is to capture the path to an 'attachment' field & i got this going fine. Now, i wish to give the attached comment a sensible filename. I wish to use a combination of both the QuoteID & QuoteCommentId as the filename.

The problem is that on a new record the QuoteCommentId does not exist (obviously) till the form is saved. So my filename is missing this bit.

Shall i force a save prior to generating each filename or lookup the previous records QuoteCommentId and add 1 to it?

What do you recommend.

 

[theDBguy]

Can you default the filename to use zero when there's no comment yet? In any case, why would you need a filename for a nonexistent comment?

 

[arnelgp]

if QuouteID is Autonumber, you can get it Ahead.
paste this in a module:

'arnelgp
' https://stackoverflow.com/questions/6498221/retrieve-next-autonumber-for-access-table
Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    NextAutonumber = cat.Tables(pTable).Columns(pAutonumField).Properties("Seed")
    Set cat = Nothing
End Function

now add code to the form's BeforeInsert event to get the next autonumber:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtFilename = NextAutonumber("QuoteTableName", "QuoteID")
End Sub

 

[HillTJ]

@arnelgp , thanks will have a go in the morning. Cheers.

 

[HillTJ]

@theDBguy , so at a new record, the user drags a email over the control, fires the 'on dirty' code which saves a copy of said email with a unique file name comprising the QuoteId PK & the QuoteCommentID FK in a dated folder where the be resides. The problem was that until the record had been saved, no QuoteCommentID had been generated.

I'm refining the code of my earlier post from a couple of years ago for a new application. I don't know how to link this post to that, but i guess you could search for something like 'email drag and drop'. If not, i'd be happy to share the code. I did not write, it but have made some amendments/improvements to suit myself.

 

[Gasman]

You go to your old post and copy the url.
Then you use the link icon in this thread and paste that url

 

[HillTJ]

@arnelgp , Works great. A simpletrick to add to my list of utilities. Thanks.

 

[Sun_Force]

if QuouteID is Autonumber, you can get it Ahead.
paste this in a module:

'arnelgp
' https://stackoverflow.com/questions/6498221/retrieve-next-autonumber-for-access-table
Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    NextAutonumber = cat.Tables(pTable).Columns(pAutonumField).Properties("Seed")
    Set cat = Nothing
End Function

now add code to the form's BeforeInsert event to get the next autonumber:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!txtFilename = NextAutonumber("QuoteTableName", "QuoteID")
End Sub

@arnelgp
Thanks for this code. I was looking for something like this for a while.
I added the following to your code to find the primary key itself. So I don't need to pass primary field parameter to your function:

Public Function MyPrimaryKey(tbl As String) As String
  
    Dim db As DAO.Database
    Dim objIndex As Index
    Dim tb As DAO.TableDef
         
    Set db = CurrentDb
    Set tb = db.TableDefs(tbl)
     
    For Each objIndex In tb.Indexes
        If objIndex.Primary Then
            MyPrimaryKey = Right(objIndex.Fields, Len(objIndex.Fields) - 1)
            Exit Function
        End If
    Next

End Function

 

[moke123]

I'm not sure if it applies to you but the code from stack overflow comes with a caveat:

Note this approach could give the wrong result in a multi-user situation ... if another user can sneak an INSERT in between the time you retrieve the next autonumber and you actually do your INSERT. If it's critical, you could verify whether you got the value you expected by checking SELECT @@Identity after the INSERT.

 

[Sun_Force]

I'm not sure if it applies to you but the code from stack overflow comes with a caveat:

@moke123
Thanks for the warning. I didn't visit the page, neither thought about this.
I also realized that code doesn't work for sql server linked tables.

 

[HillTJ]

@moke123 , i understand, but how do incorporate what uou are recommending. This is new to me Cheers & thanks for the heads-up.

 

[moke123]

@moke123 , i understand, but how do incorporate what uou are recommending. This is new to me Cheers & thanks for the heads-up.

I dont think I recommended anything, I just pointed out a possible bug.

Without seeing your code its tough to recommend anything.

Can you do an explicit save before running your code?
Can you save the file to a staging folder and then rename it and move it when the record is complete?

 

[HillTJ]

Moke123, so once i've got the next autonumber, i now force a save. I guess this technique is not foolproof in a multiuser environment, but pretty unlikely that the autonumber becomes hijacked by another user. Thanks, for pointing this out. I may revisit this concept and use a diferent sequencing, say a date stamp or something instead.

 

[arnelgp]

you "initially" want to get the autonumber, right?
this number is then "provisional" (temporary).
when saving, check if the number is "already" used.
then check for new one when it is already in used
and save that number.

or you could implement a locking, scheme.
if anybody need to save a record, he need to
Lock a specific "dummy" table first.

if he succeed, he can go and save the record.
if it fails, meaning another user has a lock on the table,
and oviouly, saving his record.
you need to wait, and check your "autonumber" again
and try to get a lock before saving your record.

 

[HillTJ]

@arnelgp , thanks but may change my logic to use a date/time serial. I may use the quoteID from the main form & append a date/time to it. Just seems easier in the long run. It's extremely unlikely that another user could generate the same date serial.