Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding Records (2000)

    I am trying to create a form that will allow users to select a specific employee (via combo boxes) and get pertinent data (i.e., SSN, last name, first name, etc.) in text boxes underneath the the combo box. When they select the user, I want them to be able to create a brand new record with some of the same information, but to be able to make modifications and save it as a new record.

    For example:

    I select John Smith.

    John Smith's data displays.

    I want to modify his hourly wage -- and I want this to be an entirely new record.

    Is this possible? I was thinking that I could set the "Allow Additions" property to "yes" -- but it just gives me a new blank record through the record selector buttons on the bottom of the form. What would the event procedure be in order that I could even write VB code to get the default values to be what has been selected?

    Thanks!
    Kindra

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records (2000)

    You should first duplicate the record, then modify one or more fields. Don't modify first and duplicate later - you'll end up with two identical modified records.

    Interactively, select the following options from the Edit menu, in this order:

    Select Record
    Copy
    Paste Append

    The VBA equivalent of this is

    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records (2000)

    When I try to do this manually (not in VB Code) I get a series of errors:

    Field cannot be updated
    Update or CancelUpdate without AddNew or Edit

    etc.

    Any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records (2000)

    Can the user add new records in the form?
    Were you on an existing record when you tried this?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records (2000)

    Yes to both. The properties are set to:

    Allow Edits, Deletions, Additions = Yes
    Data Entry = No

    If I set Data Entry to "yes" then the form will not find the record when I select one.

    Kindra

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records (2000)

    You shouldn't set DataEntry to Yes; if this somewhat confusingly named property is set to Yes, you can only enter new records, so that's not what you want.

    Do you have fields that shouldn't be duplicated because they would cause a key conflict? (Except AutoNumber fields; Access takes care of those automatically)

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records (2000)

    My primary keys are: Social Security Number and Department. Hmmm... that makes sense now. I want to keep a history of the pay rate for the employee, so I need to define something additional as the primary key so that I can duplicate the record.

    I'm not going to be in the office until Monday, I'll try that then.

    Thanks again!

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records (2000)

    Okay, now I did set the DataEntry property to "No" and created a new button that will allow the user to add a record, called cmdAdd. I also changed my primary key to an AutoNumber field so I wouldn't have to worry about it. Here's my VB Code:

    Private Sub cmdAdd_Click()
    On Error GoTo Err_cmdAdd_Click

    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    DoCmd.GoToRecord , , acNewRec
    RunCommand acCmdPasteAppend

    Exit_cmdAdd_Click:
    Exit Sub

    Err_cmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click

    End Sub

    When I try to execute this command, it says that it couldn't paste the records and that it pasted them into a "Paste Errors" table.

    <img src=/S/broke.gif border=0 alt=broke width=31 height=23>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records (2000)

    Do you have other unique indexes on the table?

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records (2000)

    The source of the form is actually a query of 2 different tables. The table that I *thought* was giving me problems I autonumbered.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records (2000)

    In that case, do you have a unique index in either of the two tables (apart from AutoNumber fields)?

  12. #12
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records (2000)

    I have modified both to make the AutoNumber the primary key.

    Now when I try to click on my cmdAdd, I get a "You can't go to the specified record" error.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records (2000)

    1. Open the query that acts as record source if the form. Can you add a new record?
    2. If yes, open the form itself. Can you add a new record (not by the code, but manually)?

  14. #14
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding Records (2000)

    I WAS able to add records before I changed the primary keys to AutoNumber, but now that they're both AutoNumber, I can't add a record either to the query or to the form.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding Records (2000)

    Can you tell us what the structure of the two tables is (fields, indexes, primary key) and the relationship between the two tables (which fields), and the design of the query? Thx.

Page 1 of 2 12 LastLast

Posting Permissions

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