Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Posts
    36

    Unanswered: Referencing a control on a subform

    I'm trying to check if a control on the subform is null. I've the following line of code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Form!FrmOrderInfoMain!SubFrmOrderInfo.ItemC ode) Then
    Msgbox "Empty ItemCode"
    End If
    End Sub

    ..whereby FrmOrderInfoMain is the parent form and SubFrmOrderInfo is the subform. I get an error for this code as Im not sure what is the correct code to use. Thanks for the help.


    Swee

  2. #2
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    On your parent form you placed a subform 'container' that can hold a (sub)form. The name of this 'container' should be used, not the name of a (sub)form placed in it.

    E.g.
    Parent form: FrmOrderInfoMain
    'Container' : subTest
    child form : SubFrmOrderInfo

    HTML Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      If IsNull(Me!subTest.ItemCode) Then
        Msgbox "Empty ItemCode"
      End If
    End Sub

    P.S. Use 'Me' instead of FrmOrderInfoMain if sub is on this form. This is faster and more 'readable'

  3. #3
    Join Date
    Aug 2004
    Posts
    36
    I get what you mean, Robje. Thanks for the reply.

    I have another question in mind. I have a data entry form. Let's say I have filled up all the required fields in the main and sub form, and then I decide not to save the form. I want to have a button that upon clicking, will discard all the data on the form and not save it in the tables. How do I write the required code? Currently I have thought of this:

    Code:
    Dim strMsg As String, strTitle As String 
    strMsg = "Do You Want to Save This Record?"
    strTitle = " Save Record ?"
    If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then 
         Me.Undo    
    End If
    DoCmd.GoToRecord , , acLast
    However Me.Undo only gets rid of the data entered in the main form before the focus enters the subform. I think Undo is not the way to delete the record in the table. How do I achieve that? Appreciative of any input. Thanks!
    Last edited by machix; 09-03-04 at 06:54. Reason: Typo

  4. #4
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    You have to write a delete query to delete this record, using the id (or whatever) on the form as parameter value (s).
    Same to do if the 'undo' button is outside the subform.
    However, next statement is easier:
    Code:
      Me.subForm.SetFocus
      DoCmd.RunCommand acCmdDeleteRecord
      Me.SetFocus
    If you're still on the parent form (yet not left it), the record cannot be deleted, but you have to do the undo. You have to check this first (if formname.newrecord then...)

    Hope this helps.

    Grtz,

    Rob
    Last edited by Robje73; 09-03-04 at 07:55. Reason: Easier method

  5. #5
    Join Date
    Aug 2004
    Posts
    36
    I'm not sure what you mean by that. Where do I post the code and how do I create the delete query?

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    Just as an unrelated "aside" .. when you are referring to things in Microsoft Access, always change the names of controls so that they are slightly-different from the names of database fields. There is an ambiguity otherwise, that can bite you.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Quote Originally Posted by machix
    I'm not sure what you mean by that. Where do I post the code and how do I create the delete query?
    Just use next piece of code:
    Code:
      Me.subForm.SetFocus
      DoCmd.RunCommand acCmdDeleteRecord
      Me.SetFocus
    The current record in the form where the focus is, will be deleted.

  8. #8
    Join Date
    Aug 2004
    Posts
    36
    What do you mean by next piece of code. I put it in the click event of the button and when I have a record in the subform, the record is deleted when I click the button. However, the main form's record is not deleted. Is there a way for me to write the code in such a way that when I click on it, both the subform and the main form's data will not be stored in the table?

    Thanks for the help,
    Swee

  9. #9
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    After this piece of code, the focus is back on the parent form. You can delete the current record the same way:
    DoCmd.RunCommand acCmdDeleteRecord
    This should work.

  10. #10
    Join Date
    Aug 2004
    Posts
    36
    Do you mean I click on the delete button twice? I've added my sample database and I hope that you will understand my scenario better now. When I first click on the button, the record in the subform is deleted but not the main form. Clicking the button again on the main form produces an error.

    Note: The form in question is FrmOrderInfoMain.

    Regards,
    Swee
    Attached Files Attached Files
    Last edited by machix; 09-06-04 at 06:47.

  11. #11
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Hmm, it seems it won't work that way. However, you can do the following:
    Add POID from tblOrderInfoMain to the query and place this field hidden on the form. Next, write a delete query where this POID field on the form will be used as a parameter.
    In the code, after deleting the subform record, execute this delete query.

    I just saw you checked only the update cascade, why not the delete cascade? You only need to delete the main record, and all the rest will be deleted.

Posting Permissions

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