Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Unanswered: Problems with Forms/SubForms

    I'm hoping a picture will speak a thousand words .. as I've been unable to explain the problem and find a solution yet.

    http://mich.f2o.org/cons.gif

    That shows the main form for one of my groups... in this case Consultants. There are five other groups.

    The first five tabs are the same for each of the groups. All of the tabs
    contain subforms. The Address tab is actually a continuous subform, with
    view/edit and delete buttons on it for the corresponding records. Education also represents a 1 to many relationship but is displayed as a datasheet view. Personal Info, Specific Info, and Resume all come from the same table, Contacts.

    The problems I have are ...

    Throughout my code are references to the [Consultants] form .. some
    examples:

    [Forms]![Consultants]![Contacts.updatedby] = CurrentUser()
    [Forms]![Consultants]![Contacts.DateUpdated] = Date

    [Forms]![Consultants].Requery


    [Address]![AddressID]=[Forms]![Consultants]![FormsAddressDetailsSubTemplate].[Form]![AddressID]

    All works fine within the Consultants form .. however .. the problem arises when I use the same subforms on the other groups .. obviously the mainform isn't then called Consultants, so there are errors throughout.

    I've tried to use Me. and Me.Parent, but it's still not working. Sometimes
    it'll work for the first time, but then the second time a change occurs,
    it'll throw and error that it can't find the reference. After EVERY change
    these two lines are triggered in an after update ..

    [Forms]![Consultants]![Contacts.updatedby] = CurrentUser()
    [Forms]![Consultants]![Contacts.DateUpdated] = Date

    So if I make changes on Tab_PersonalInfo then move to Tab_SpecificInfo .. the two fields indicated above should be changed. Like I said the first time they are changed it works, but when I change something on the second tab, I get an error.

    So, I'm not sure that Me. or Me.Parent is going to work.

    What I was hoping to do .. and I haven't been able to figure it out is ..
    upon opening the Consultants Form declare a variable such as Consultants = MyMainForm. Then use MyMainForm throughout my code instead of Consultants. I can then declare MyMainForm upon the opening of each of the 5 other groups. I can't get this to work, I don't know where to declare it so that the sub-forms (tabs) can have access to it.

    As a last resort, I could copy each of the subforms five times for use on
    each of the MainForms .. but this *really* is iccky and defeats the purpose of creating the subform once and using it many times.

    I would be very appreciative of any assistance anyone could provide.
    Unfortunately this is holding up the entire project and I'm getting
    extremely frustrated with what I think should be so simple.

    TIA,

    Mich

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    I think you could use a link field instead. On each tab place a link field in the tab portion - not in the subform.

    Update the link in line with your selected record, to show custID or some other common link field.

    Then to make life real easy use the LinkChild/ LinkParent property to create a link between your subforms clientID and any field you can possibly link to (It's not important right now). When this is done change the child portion by typing directly into the property window the name of the link filed on your tab (which is unbound).

    I think this will get around hte problem you are describing.

  3. #3
    Join Date
    Feb 2003
    Posts
    2
    Originally posted by dynamictiger
    I think you could use a link field instead. On each tab place a link field in the tab portion - not in the subform.

    Update the link in line with your selected record, to show custID or some other common link field.

    Then to make life real easy use the LinkChild/ LinkParent property to create a link between your subforms clientID and any field you can possibly link to (It's not important right now). When this is done change the child portion by typing directly into the property window the name of the link filed on your tab (which is unbound).

    I think this will get around hte problem you are describing.
    Excuse my ignorance .. but what do you mean by a Link Field ? and is there a reason I need another one that exists on top of the Tab when it already exists behind the tab?

    LinkChild/LinkParent properties are already set correctly on those tables that have 1-many relationships. Three of the tabs in the pic come from the main table (Contacts) as is used to generate the MainForm itself. Two of the tabs are 1-many relationships, one I'll explain at length in a minute the other is displayed in datasheet view and isn't really causing a major problem at this point. The last tab is completely independent and is only used on this MainForm.

    The Addresses tab. On there is a Continuous Sub-Form that displays the various addresses associated with that contact. There is a 1 to many relationship between the Contact and the Address table, based on the ContactID PK and the AddressContactID FK. Only a couple of the address fields are actually displayed on the Continuous Sub-Form along with [View/Edit] and [Delete] buttons. When on clicks on the [View/Edit] button it runs a Macro that opens another Sub-Form and in the Where statement is this ...

    [Address]![AddressID]=[Forms]![Consultants]![FormsAddressDetailsSubTemplate].[Form]![AddressID]

    While I am on the Consultants main form this works fine, no problem. The problem arises when the main form is something else, let's say Marketing.

    What I'm trying to do .. is replace the [Consultants] in the above line to
    something dynamic or to use Me.Parent or whatever, but no matter what I try I keep getting a popup asking me for the AddressID. Yes, on the Continuous Sub-Form are two hidden fields AddressContactID and AddressID.

    Hopefully this explains things better. I really appreciate the assistance.

    Michele

  4. #4
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150

    Wink

    I am still a bit confused on what you want to achieve. But if you want to dynamically change the main form and sub form then use this code.

    Dim Mainform as String
    Dim SubFrm as String

    Mainform = "FormName"
    SubFrm = "SubFormName"

    Forms(Mainform)(SubFrm).Controls("ControlName") = Value

    I hope this helps.

  5. #5
    Join Date
    Apr 2003
    Posts
    36
    Originally posted by machado
    I am still a bit confused on what you want to achieve. But if you want to dynamically change the main form and sub form then use this code.

    Dim Mainform as String
    Dim SubFrm as String

    Mainform = "FormName"
    SubFrm = "SubFormName"

    Forms(Mainform)(SubFrm).Controls("ControlName") = Value

    I hope this helps.
    Does this work for subreports too? What is the 'Value' part of the last statement? Is is the name of the subform/subreport which I'm going to show on the main form/report?

  6. #6
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Yes it does

    use the reports object instead of Forms

    Reports(Mainreport)(Subreport).Controls(Controlnam e) = Value

    the value is the data you want to put in the control
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  7. #7
    Join Date
    Apr 2003
    Posts
    36
    Originally posted by machado
    Yes it does

    use the reports object instead of Forms

    Reports(Mainreport)(Subreport).Controls(Controlnam e) = Value

    the value is the data you want to put in the control
    What would the code be to link the child report and the master report? I know that this can be done in the properties window of the subreport, but it doesn't seem to be working and I thought I'd read somewhere that there's a bug in this code. Here's the situation:

    I've got a graph of student genders by program. I've put that graph in a subreport that takes PROGRAM as an input for the query. The master report also takes PROGRAM as in input for its query. When I run the subreport and just put in one PROGRAM, I get the data I want. When I run the master report, I get data for all the programs. So I thought I could hard code the link between the master and child via some code. Thanks in advance.

  8. #8
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    In the main report add the following code

    Me.SUBREPORT.LinkMasterFields = "Field1;Field2;Field3"
    Me.SUBREPORT.LinkChildFields = "Field1;Field2;Field3"

    Enter only the fields to link seperated by ; as above

    Graphs are a pain in the ass on reports sometimes it won't update for some reason and it has been identified as a bug.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  9. #9
    Join Date
    Apr 2003
    Posts
    36
    Originally posted by machado
    In the main report add the following code

    Me.SUBREPORT.LinkMasterFields = "Field1;Field2;Field3"
    Me.SUBREPORT.LinkChildFields = "Field1;Field2;Field3"

    Enter only the fields to link seperated by ; as above

    Graphs are a pain in the ass on reports sometimes it won't update for some reason and it has been identified as a bug.
    Thanks again. One last thing. Does this have to be in the OPEN event or can it be in the FORMAT event of the Header Group where the subreport is located?

  10. #10
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    It doesn't matter where but I do suggest that you call

    me.requery after you link the fields
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  11. #11
    Join Date
    Apr 2003
    Posts
    36
    So first I added it to the Format event - but got an error saying something like I couldn't do this after opening. So, then I put it in the OPEN event, and now Access crashes everytime I open the form. Any other places I can try to put the code?

    Thanks for your patience.

  12. #12
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    When you say Access crashes to you mean it hangs and you can't do anything.

    This will probably have something to do with the fields you are linking in the parent and child report
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  13. #13
    Join Date
    Apr 2003
    Posts
    36
    Sorry, it wasn't hanging, it was crashing. So I took out the me.requery and that stopped it from crashing, but I still can't get the link working between the master report and the chart in the sub report. I think I'm going to take the subreport out and just put the chart on the main report. Thanks for you help.

  14. #14
    Join Date
    Apr 2003
    Posts
    36
    Originally posted by machado
    When you say Access crashes to you mean it hangs and you can't do anything.

    This will probably have something to do with the fields you are linking in the parent and child report
    OK I give up. Now just trying to put a chart object on the report causes Access to Crash after I complete the Chart Wizard steps. I'm totally burnt out on this graph stuff. Any words of wisdom? I'm about to give up and maybe take the Access data into Excel to chart it.

  15. #15
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    Have you tried to compact and repair the database.

    If that doesn't work:

    Create a new database and import all th objects ie(tables, queries, forms, reports, macros, modules)

    Sometimes access 2000 files get really screwed up that is one of the main reasons I use MSDE or SQL Server with VB6/.net
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

Posting Permissions

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