Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    20

    Unanswered: Replicating a Field

    Morning All

    I am writing an Archive DB however its been some time since I tackledcode so I am a bit rusty. I am opening a form via a command button and on open I want the ID field to copy to the new form so that the records are linked. I am leaving both forms open to be safe but I keep getting an error that I can't assign a value to that object. Any ideas would be great. The code I have used is

    Private Sub Form_Open(Cancel As Integer)
    Me!UniqueID = Forms!ArchiveForm!UniqueID

    End Sub

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    make sure me.uniqueID is not bound to a field in the underlying record source that is an autonumber

    if your new form is sourced onto a query - be sure it is an update-able query

    can you guarantee that every time that form opens that me.uniqueID is going to be a blank field?? you might put this vba code into the AfterUpdate event of another control and add an

    If IsNull(Me.UniqueID) then

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is this 2nd form a data entry type form or are you opening it and then sending a docmd.gotorecord,,acnewrec command?

    If you're opening it and then adding a new record, in the code before you add the new record, set the defaultvalue of the ID field to the ID on your 1st form. ex:
    Forms!My2ndFormName!MyIDField.DefaultValue = Forms!My1stFormName!MyIDField.

    As NTC stated, make sure the field you're trying to write a value to is not an autonumber field (test it by opening the form and manually entering an ID.)

    Otherwise, try this (referencing the ID fields directly versus using me):
    Forms!MyFormName!UniqueID = Forms!ArchiveForm!UniqueID

    You have to set this value like you would when entering a new record. I have a feeling though that your code for opening the 2nd form/setting the ID needs some modification to correspond how the form's are active/being updated.

    As NTC suggested, test isnull(me!MyIDField) (I also use msgbox(me!MyIDField) in code to make sure I'm passing non-null values.)
    Last edited by pkstormy; 11-13-09 at 20:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2009
    Posts
    20
    Thanks for the info guys if I tell you what I'm trying to achieve it may become clearer. As I said the database is for archiving old documents so form 1 will be the main archive record and form two will be a record to book out that archive. I am trying to copy the archive ID into form 2 then create a booking out record from there. The unique ID however refuses to copy back to the booking out record table.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What I usually do for situations like this is run an append query (making sure the primary key of the table design doesn't allow duplication where necessary.)

    The expression in the query (to append to the RecordID type field) is =Forms!My1stForm!RecordID). After the record has been appended to the 2nd archiving table (by running the append query), then it's a matter of opening the 2nd form using the "WHERE" clause in the docmd.openform. ie. Docmd.openform "my2ndForm",,"[RecordID] = " & Forms!My1stForm!RecordID & ""

    Note that you can use the docmd.setwarnings false and docmd.setwarnings true to turn the warnings off and then on again so don't get the "You are about to append x records" message doesn't appear.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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