Unanswered: Prepopulate a form with data which can be edited and saved
I need to be able to open a form which shows a new record with the various controls on this form already showing data (pre-populated). It should be possible to edit the data in the controls (if so required). Once completed the data should of course be saved in a table as a new record. However I do not want existing data in the table to be changed / edited. The form on opening just needs to show data to make editing easier and quicker. I was thinking if a template would be the answer or whatever. The problem is that even if there is such a thing as a template exists. I have no idea how to do it. Any help will be greatly appreciated.
Further to my earlier message I now found out that one can add a Command Button on the form in Form Design and in the Command Button Wizard select in Categories ‘Record Operations’ and in Actions ‘Duplicate Record’.
This adds the following code in the On Click Event Procedure.
Private Sub cmdDuplicateRecord_Click()
'A Duplicate Record is already a save record.
On Error GoTo Exit_Handler
'Purpose: Duplicate the main form record
'Save and edits first
If Me.Dirty Then
Me.Dirty = False
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
'Duplicate the main record: add to form's clone.
!Title = Me.txtTitle
!Description = Me.txtDescription
!Outcome = Me.txtOutcome
!Submitter = Me.cboSubmitter
!OtherRequestor = Me.cboOtherRequestor
!Requestor = Me.cboRequestor
!LocationID = Me.cboLocationID
!CategoryID = Me.cboCategoryID
!DateTimeStart = Now()
!DateTimeEnd = Now()
'etc for other fields.
'Display the new duplicate.
Me.Bookmark = .LastModified
'Unlock the following field for edit. Activate both the Undo, Save and Delete buttons
This indeed creates a duplicate form / new record but there is an error message which is because the Primary Key field forms part of the duplicated record. The primary key field has a text Data Type (required for our purpose) and in the field Properties is set to ‘Yes(No Duplicates). This is because we don’t want the primary key to appear more than once.
Is there any way to modify the code so that the form is duplicated but automatically creates a next primary key?
I will be grateful for any assistance you can offer.
Thank you StarTrekker for your reply which looks like a possibility but I do not know how to proceed with it.
The name of the field which is the primary key is ‘Job Number’ and is set to Text Data Type. The Job Numbers do not follow a specific format. For example some job numbers have the format JN000, but others may have a different format like PP000. It should be possible to manually enter or edit a Job Number as long as the person doing it makes sure there is no duplicate. In Field Properties Indexed is set to ‘Yes (No Duplicates)’
When I click the Duplicate button the new form with all fields already populated shows a Job Number which is already in the table and as duplicate Job Numbers are not allowed this gives the error message. A solution would be that the duplicated form shows a Job Number which is say 1 higher than the one which was copied. The Job Number will be edited like several of the other fields.
It would be great if you could tell me how this possibly can be achieved.
There is no code dealing with your primary key, so theory says that when you get to your duplicated record, the primary key should be blank. Is there a default value for the primary key perhaps? Maybe all that needs doing is to clear that?
Of course, it could be part of the "etc for other fields" part, but it's impossible to tell that.
Look at your code again, looking for the bit that says