Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Red face Unanswered: Form Controlling Sub Form

    Hi all,

    I have a database which is being used for storing name, order #, part number and serial to that part number.

    Name and Order Number is setup on the main form, part number and Serial Number is setup on the sub form. I have many users that use this database and I have recently noticed a problem which I need your help with:

    If a user chooses his/her name and enters order number and then accidentally closes the database, then the database stores it like that, with just name and order number. The problem with that is that I want each user to enter the rest of the info as well. For example, if they enter name and order number then they have to enter at least one part number and one serial before they can exit.



    To fix the problem I went ahead and made part number and serial in the sub form required but that didn’t help at all.



    Any suggestions



    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try making the field(s) required at the table level.

  3. #3
    Join Date
    Jul 2005
    Posts
    144

    still not working

    I did that it didnt work, i have also tried something different but its not working tell me what am i doing wrong?

    I have inserted an After Insert event into the main form

    If DCount("*", "Order", "[OrderID] = " & Me.txtOrderID) =0 Then
    MsgBox "You need to fill in at least one part", vbOKOnly
    Me.OrderDetailsSubform.SetFocus
    End If

    After typing the code above I received the debug mesg saying " Method or data member not found" and it

    highlighted this command "Me.txtOrderID"

    Let me know if i am doing anything worng.

    Thanks

  4. #4
    Join Date
    Apr 2004
    Posts
    173
    I've also had problems with this and I think it boils down to reacting to the situation more than it does preventing it. Basically you have two forms one inserting information into one table and the subform inserting related information into another table. Insert and update events can occur when a user navigates to the subform from the main form. Of course this can mean there is no record in the subform (table) that matches the record in the main form. So basically there is nothing to check the existance of yet since the user may not have entered any related order lines yet. Typically I've dealt with this by deleting parent records with no children at close of the form. Or you could create your own navigation buttons that trap these problems essentially requiring the user to push a button on the main form that could validate the existance of related lines. Checking the main form for validity doesn't guarantee the existance of related lines. The other avenue possibly would be to store the main forms record id in a variable and when it changes then check for related records; 'listening' for the change in the oncurrent event of the main form. Sorry if this doesn't help. I'll be watching the thread as I'm sure somebody else has something slick to handle this.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This type of question gets asked alot ... Generally a good way to handle the "no record in the subform" problem is to design the form so that they're unbound. Then you write code to write out the order record and the item records ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Apr 2004
    Posts
    173
    You wouldn't know of a good code example for this anywhere would you?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by mmbosman
    You wouldn't know of a good code example for this anywhere would you?
    To what were you referring to exactly?
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Apr 2004
    Posts
    173
    Specifically to setting up an unbound form with an unbound subform imbedded in it. Are we talking about writing a data access layer here or simply using a recordset to populate fields. Then how do you go about linking the unbound subform to the bound main form. I've got a fairly intricate invoices and invoice lines form that has the problem that started this thread. It's also a network problem with an app I wrote with bound forms at certain times. So, I'm looking for a way to swtich some of them over to cut back on the network footprint. Any help appreciated. I did not intend to hijack this thread, but will post separately if I've offended.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by mmbosman
    Specifically to setting up an unbound form with an unbound subform imbedded in it. Are we talking about writing a data access layer here or simply using a recordset to populate fields. Then how do you go about linking the unbound subform to the bound main form. I've got a fairly intricate invoices and invoice lines form that has the problem that started this thread. It's also a network problem with an app I wrote with bound forms at certain times. So, I'm looking for a way to swtich some of them over to cut back on the network footprint. Any help appreciated. I did not intend to hijack this thread, but will post separately if I've offended.
    A point: Unbound forms can't link to anything. So, you do the "linking" for them thru the use of queries, setting RowSources, and ReQuerying ... At it's most basic, you're talking just populating fields (whether on a form or as rows of data in a table) as you said. But, then again, there is always new and inventive ways to do things ... I have a purchase order form that is not bound ,with a subform (for the items detail) that is bound to a local table. When the form is entered, I change the RowSource of the subform to another temp table for actual processing ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Apr 2004
    Posts
    173
    The ap in question is a split mdb file. If I make the main form unbound and change the record source progrmatically for the subform to a SELECT statment that only selects the related records will that have the effect of reducing the data exchanged over the network to only the related records? I was under the impression that in the file server arrangement I brought over the whole table no matter how I changed that record source property. Any information would be appreciated. In lieu of the SELECT statement your solution of the temp table would definitely be only the related records across the network. TIA

  11. #11
    Join Date
    Nov 2003
    Posts
    1,487
    Maybe try this:

    Within the BeforeUpdate event of the Main Form, pole all the Controls withing the SubForm for unfilled fields via:

    Code:
    ' Not Tested....
    Dim Ctrl As Control
    Dim SQLstrg As String
     
    On Error Resume Next
     
    For Each Ctrl In Forms("myMainFormName").Form("mySubFormName").Controls
       If Ctrl.ControlType = acTextBox And IsNull(Ctrl) = True Then
          If MsgBox("You must fill in the remaining Form Fields before this " & _
                        "Record can be saved." & vbCrLf & vbCrLf & "Do you " & _
                        "want to Cancel this Record?", vbExclamation, & _
                        "Not All Fields Supplied") = vbYes then
             SQLstrg = "DELETE FROM yourTableName WHERE yourTableRecordID=" & Me.myMainForRecordIDField
             CurrentDB.Execute SQLstrg
             Exit For
          Else
             Cancel = True
             Exit For
          End If    
       End If
    Next Ctrl
    Or something like that...

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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