Primary key on new record - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Primary key on new record

Krzysztof Kaminski

2 years ago

Hello
I have run into issue which I can not figure out.
I have a database where I store documents. It consists of fields describing the document and name of the actual file (pdf/png/jpeg). Actual files are stored on a network share.
The idea was to upload the file and fill in the form manually. However I need to be carefull not to overwrite existing files, all are stored in one place. So I thought that I will copy the file and rename it with [PrimaryKey].extension, this should do the trick. The problem is that with a new record I do not know the actual value of PrimaryKey is.
The database lives on SQL Server, Access is frontned. I am out of ideas, any help apreciated. :)

	Kevin Robertson @Reply 2 years ago
Try refreshing or requerying the form so the AutoNumber becomes available. Use either `Me.Refresh` or `Me.RecordSet.Requery`.

	John Davy @Reply 2 years ago
Hi Krzysztof I assume you have a Split database. What file are you trying to upload? Please try to explain it one more time for me. Tell me exactly what you are trying to do. John

Krzysztof Kaminski OP @Reply

2 years ago

Hi
refresh or requery will not work, there is no id.
I have a form where I can fill all the data I want and also on this form I have made a button to upload a file to specific directory.
I am uploading pdf, jpeg and png files. When You use the button for the upload you get a window to select the file, then I check for different things which can mess up the process (is the destination direcory available? Is the selected file extension allowed? Is there already a file with the same name at destination? etc) and after that I am moving the file to the destination with changed name to primarykey.extension and set filename field. Everything works fine if record is already in the database and primarykey is available. The problem arises with new record because there is no id.
This is the line where i have problem
FileName = "tableName" & me.id & "." & FileExtension
if there is no PrimaryKey (me.id) the result would be tableName.FileExtension for each record without id (each new).

	Krzysztof Kaminski OP @Reply 2 years ago
Temporarly I have made a check if me.id is available, if not then I use different file naming convention: FileName = Format(Now, "yyyymmdd_hhnnss") & "." & FileExtension This does not guarantee however unique file name.

	Kevin Robertson @Reply 2 years ago
Can't you set a field in code to create the ID? For example set a DateTime field to the current date.

	Juan C Rivera @Reply 2 years ago
Richard did this in his ABCD add the date /time to the file this way if you need to keep the old file for historical. Just thinking if you use the id- you can only use the document name 1 time but if the date /time is diffrent this can be a benefit. I do this for my daily reports on a construction site.

	Krzysztof Kaminski OP @Reply 2 years ago
Ofcourse! Thank You Kevin. I have a field with creation_date and it needs to be filled after insert anyway so I used it in my button sub. Me.created = Now Me.Refresh and voila, id available :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View