Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2009
    Location
    UK
    Posts
    19

    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.

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    You can populate a recordset with the data you need and then on the Form's Open or Load event you can assign the values to the correct control.

    Then when you click a Save button you can use an Append query to insert the records into the table.

    Does that make sense? If not let me know and I'll post more specifics.

    C

  3. #3
    Join Date
    Apr 2009
    Location
    UK
    Posts
    19
    Thank you very much for your quick response. As I am a novice with Access I would appreciate some more specific instructions. Thanks again.

  4. #4
    Join Date
    Apr 2009
    Location
    UK
    Posts
    19
    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
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
    MsgBox "Select the record to duplicate."
    Else
    'Duplicate the main record: add to form's clone.
    With Me.RecordsetClone
    .AddNew
    !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.

    .Update

    'Display the new duplicate.
    Me.Bookmark = .LastModified

    End With

    'Unlock the following field for edit. Activate both the Undo, Save and Delete buttons

    Me.txtTitle.Locked = False
    Me.txtDescription.Locked = False
    Me.txtOutcome.Locked = False
    Me.txtStartDate.Locked = False
    Me.txtEndDate.Locked = False
    Me.cboStartTime.Locked = False
    Me.cboEndTime.Locked = False
    Me.cboOtherRequestor.Locked = False
    Me.cboRequestor.Locked = False
    Me.cboLocationID.Locked = False
    Me.cboCategoryID.Locked = False
    Me.cboSubmitter.Locked = False

    Me.txtStartDate.Enabled = True
    Me.txtEndDate.Enabled = True
    Me.cboStartTime.Enabled = True
    Me.cboEndTime.Enabled = True
    Me.cmdUndoRecord.Enabled = True
    Me.cmdDeleteRecord.Enabled = True
    blnConfirmUndo = True
    End If

    Exit_Handler:
    Exit Sub

    Me.cmdSaveRecord.Enabled = True
    Me.cmdUndoRecord.Enabled = True
    Me.cmdDeleteRecord.Enabled = True

    End Sub

    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.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What are the rules for what the primary key should be?

    What is the name of the field which is your primary key?

    Basically, it would be adding a line such as:

    !PrimaryKeyField = SomeFormulaThatGivesYourNextPrimaryKey

    to your code, after the .AddNew and before the .Update
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Apr 2009
    Location
    UK
    Posts
    19
    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.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Curious.

    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

    ![Job Number] = "WHATEVER"

    If you find it, you can alter to

    ![Job Number] = "FillMeIn"

    or similar.

    Check it out and let me know how ya go
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Apr 2009
    Location
    UK
    Posts
    19
    Thanks for your reply.
    I will look at the code a bit closer and will let you know how I get on but it will have to wait a while as I am off on a two week trip.
    Thanks again for the time being

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Enjoy your trip!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •