Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locking the sub form (Access 2000)

    This is problably pretty easy but I have searched and could not find an answer.

    I have a data entry form that has a subform. Everything is linked properly. (Primary_id to foreign_id). The master (Parent) form is frmBatchHeader and the subform is frmDocInfo. I want to make sure that someone is not able to enter anything in the subform without first entering in the master form. Otherwise the record is lost and can not join back to the master form.

    Any quick suggestions?

    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locking the sub form (Access 2000)

    Do some testing to make sure I'm correct on this, but I believe you get an error in most situations when you add a record on a subform with out having a main form record. If you want to be absolutely sure you have the necessary main record values set, you could lock and disable the subform control until after the necessary fields have been filled in, and when they have, run a small segment of code that unlocks and enables the subform control. Then of course you will need to force control back to the main form when the subform record has been entered. Let us know how you fare.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking the sub form (Access 2000)

    I don't get an error. I just get a #deleted# filling in the fields. It does not lock me out. By the way, this is a SQL Server back end. When I do it with local tables on Access, it just leaves the foreign_id blank. Therefore, not linking back to the master primary_id.

    Also, what would I set the lock and disable method on? master form current, onopen?

    Thanks

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

    Re: Locking the sub form (Access 2000)

    You can use the On Current event of the main form, something like
    <code>
    Private Sub Form_Current()
    With Me.SubformName
    .Enabled = Not IsNull(Me.Primary_ID)
    .Locked = IsNull(Me.Primary_ID)
    End With
    End Sub
    </code>
    where SubformName is the name of the subform as a control on the main form (this is not necessarily the same as the name of the subform in the database window). You can use the same code in the After Update event of the control bound to Primary_ID, assuming that the user enters a value for this field:
    <code>
    Private Sub Primary_ID_AfterUpdate()
    With Me.SubformName
    .Enabled = Not IsNull(Me.Primary_ID)
    .Locked = IsNull(Me.Primary_ID)
    End With
    End Sub
    </code>
    That should cover it.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locking the sub form (Access 2000)

    The fact that it is SQL Server shouldn't change things much, except that you may not have the ability to change the table design. But the table you are dealing with should have nulls NOT allowed in it's design which would cause an error when the user attempts to add the subform record. You should be able to do the same thing with the Access version of the table. Of course it's not a very friendly error message, so you should probably do something like I suggested in my previous post, and Hans have given you some sample code for.
    Wendell

  6. #6
    New Lounger
    Join Date
    Nov 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking the sub form (Access 2000)

    How do you create a subform within a form? What is the best and easiest way? Thanks.
    Jen

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking the sub form (Access 2000)

    Jen,
    I create my subform by itself, and then insert the subform, and use the wizard to link the two...if there is necessary programming on the form, it's best to wait until after the form is created and inserted, because you will have to refer to the parent form while identifying objects on the child form (hope that makes sense)....

    Jeremy
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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