Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Check subform for new record then update fields

    Hi all,
    I want to:

    'Check to see if the child record (subform) is a new record,
    'if yes then copy some details from parent record to child record (subform).
    'Parent Form is [eEmployer1HODetails]
    'Child Form (subform) is [usysbeEmployer2SiteDetailsSubform]


    I have a button which is located on the Parent form which has an on click event as follows:




    Private Sub CopyHO2Site_Click()

    On Error GoTo Err_CopyHO2Site_Click

    'Check to see if the parent record is a new record, if yes then display message and abort.
    If Me.NewRecord Then

    Call MsgBox("An Employer must be selected before you can Copy Head Office Details to Site Details. " _
    & vbCrLf & "" _
    & vbCrLf & "Click on OK then select an Employer record, using the record selectors at the bottom of the page." _
    , vbExclamation, "EMPLOYER RECORD NOT SELECTED")
    GoTo Exit_CopyHO2Site_Click
    End If


    If Forms![eEmployer1HODetails]![usysbeEmployer2SiteDetailsSubform].NewRecord Then .....................
    Note that using the above line of code creates an error message = "object does not support this property or method"

    But if I use the following code to check if a field within the child form (subform) is null then I can get it to work:




    If IsNull(Forms![eEmployer1HODetails]![usysbeEmployer2SiteDetailsSubform].Form.[SiteName]) Then

    Forms![eEmployer1HODetails]![usysbeEmployer2SiteDetailsSubform].Form.[SiteName].Value = Forms![eEmployer1HODetails]![HOOrgName].Value
    Forms![eEmployer1HODetails]![usysbeEmployer2SiteDetailsSubform].Form.[SiteAddress].Value = Forms![eEmployer1HODetails]![HOOrgAddress].Value
    Forms![eEmployer1HODetails]![usysbeEmployer2SiteDetailsSubform].Form.[SiteAddress2].Value = Forms![eEmployer1HODetails]![HOOrgAddress2].Value

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    End If

    Exit_CopyHO2Site_Click:
    Exit Sub

    Err_CopyHO2Site_Click:
    MsgBox Err.Description
    Resume Exit_CopyHO2Site_Click

    End Sub



    Is there a way to check if the child form is a new record? Can you please supply some code that would fit in with what I am trying to do above?

    Thanks heaps for any assistance.
    Last edited by Karen Day; 11-08-09 at 05:40. Reason: provide more detail because noone is answering

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Karen Day View Post
    Hi all,
    I want to:

    'Check to see if the child record (subform) is a new record,
    'if yes then copy some details from parent record to child record (subform).
    'Parent Form is [eEmployer1HODetails]
    'Child Form (subform) is [usysbeEmployer2SiteDetailsSubform]

    Is there a way to check if the child form is a new record? Can you please supply some code that would fit in with what I am trying to do above?

    Thanks heaps for any assistance.
    What I usually do to test for a new record is see if the autonumber field (which you should have!) on the form isnull or not (see note below to reference it from a subform).

    ex:
    If isnull(me!MyAutoNumberFieldName) then
    msgbox "On new record"
    else
    msgbox "On created record."
    end if

    Only problem is if you're adding a new record, you have to pay careful attention to when the autonumber field is actually populated within your tab order (and the record no longer becomes a new record, especially if you're actually writing (in code) to fields on the form/subform.)

    Another thing I'll do is have an unbound checkbox field (called: IsNewRec) on the form and set the value of this checkbox to true when the "Add New" button is pushed and then set it to false in the OnCurrent event of the form (which would fire when the user goes to another record.) You can then base code to check if me!IsNewRec = True then.... to do your other code.

    ** Note that if you need to check the value of either the autonumber or checkbox field (or other field) within a subform or other form, you can always simply directly reference the form/field itself in your code. Ex:

    If isnull(Forms!MyMainFormName!MyAutonumberField) then....

    or to reference a field on the subform
    If isnull(Forms!MyMainFormName!MySubFormName!MyAutonu mberField) then....

    (pardon the space between u and m)

    as far as copying the values (given that 1. the recordset is updatable and updating the values doesn't voilate any rules), your code might look like this:
    Forms!MyMainFormName!MySubformName!SomeField = Forms!MyMainFormName!SomeField.

    But you have to pay careful attention to what updating values automatically in code does and how they fire compared to direct data entry. I usually test first with direct data entry into the table and then test again using the automation.

    **** What I often prefer to do versus actually updating the values, is to first set the Default value of those fields and then issue a docmd.gotorecord,,acnew (or before whatever code to go to a new record.)
    Ex:
    Forms!MyMainFormName!MySubFormName!SomeField.defau ltValue = "=" & Forms!MyMainFormName!SomeField
    or this (I can't recall offhand which way works)
    Forms!MyMainFormName!MySubFormName!SomeField.defau ltValue = "= '" & Forms!MyMainFormName!SomeField & "'"

    (pardon the space between u and l)

    This way the autonumber field is not generated (and other required values can be entered before actually appending the new record to the database). It avoids the error you get as if you were just trying to update the same fields you do in code and haven't populated the other required fields or violated some value of the field's limitation. (Note: I often test entering a record in the table itself by just populating the same type of fields (that I use in code) to make sure the record is saved when using the 1st method above.) Otherwise, depending on your tab order of the form/subform, it could be trying to append the new record after your code to write the values and you thus get an error because other required fields are blank.

    I hope that makes sense.
    Last edited by pkstormy; 11-08-09 at 16:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also note, this command:
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    should not be necessary with an autonumber field in the table (which you SHOULD have). (this code is based on not having an autonumber field in the table which helps MSAccess know when to save the record on the form. Save yourself several headaches and simply put an autonumber field in the table/form. You should then never need to use the acsaverecord command anywhere (I have never had to use it in 25+ years of coding - but I also always use an autonumber in all my DATA type tables). You'll also save yourself other headaches by having an autonumber field in the table.

    If for some reason, you then need to generate the new autonumber on the form/refresh the values, you issue a simple: Refresh command (just the word "Refresh") in your code versus the acsaverecord. But with an autonumber field on the form, MSAccess will do all this automatically dependant upon your tab order and you'll have less mult-users-all-accessing-the-same-table type problems.

    Note also: you can easily create an autonumber type field in a data table that doesn't have one without affecting the data. You just can't have 2 autonumber type fields in the same table. Typical names of autonumber type fields are: ID, RecID, RecordID, PersonID, ClassID, NotesID, etc...etc... but usually have ID in the name.
    Last edited by pkstormy; 11-08-09 at 17:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2002
    Posts
    157
    Oops I feel so dumb, sometimes you can't see the trees for the forest!!!!!!!
    So simple.
    I was so intent on having some code that involved .newrecord on a subform that I did not even think to use checking the ID field (which is on the form).

    I have also removed the following command as you ave suggested:
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    I really appreciate your assistance with this.

    Thanks heaps

Posting Permissions

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