Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Changing subforms via VBA problem

    Using a group box (gruppevalg) I'm giving the user the ability to choose between different contents in a subform.

    When changing to either value 1 and 2, I don't get any error.

    Changing to 3 doesn't either, but going from value 3 to 1 I get a prompt for a parameter, and then exits with errorcode 2101 + "..... isn't valid for this property".

    I have similar code on a few other forms, and they work flawlessly.

    Any ideas?

    Cheers, Trin

    Code:
        If gruppevalg.Value = 1 Then
            With Forms!arbejdsgiver!underform_arbejdsgiver
                .SourceObject = "Stilling_liste"
                .LinkChildFields = "Stilling_arbejdsgiverid"
                .LinkMasterFields = "Arbejdsgiver_id"
            End With
        ElseIf gruppevalg.Value = 2 Then
            With Forms!arbejdsgiver!underform_arbejdsgiver
                .SourceObject = "Arbejdsgiver_forloebshistorik"
                .LinkChildFields = "Arbejdsgiver_id"
                .LinkMasterFields = "Arbejdsgiver_id"
            End With
        ElseIf gruppevalg.Value = 3 Then
            With Forms!arbejdsgiver!underform_arbejdsgiver
                .SourceObject = "Arbejdsgiver_kontaktpersoner"
                .LinkChildFields = "Arbejdsgiverkontakt_arbejdsgiver"
                .LinkMasterFields = "Arbejdsgiver_id"
            End With
        ElseIf gruppevalg.Value = 4 Then
            With Forms!arbejdsgiver!underform_arbejdsgiver
                .SourceObject = "Arbejdsgiver_kontakthistorik"
                .LinkChildFields = "Arbejdsgiverkontakt_arbejdsgiverid"
                .LinkMasterFields = "Arbejdsgiver_id"
            End With
        Else
                MsgBox "dyt else", vbOKOnly
        End If
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Nov 2012
    Posts
    32
    Hi Trin,

    Can you post what have you tried?

    Does it occur ONLY when going from 3 to 1 and not from 2 to 1, for example?
    Have you tried debug stepping through the code to check what line causes the error?

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    The follow "state"-changes work

    1 -> 2
    2 -> 3
    2 -> 4
    4 -> 2

    The follow do NOT work but trigger the error

    1 -> 3
    1 -> 4
    3 -> 1
    3 -> 4
    4 -> 1

    The error is trigger when altering linkchildfields. It is as if the childfields "Arbejdsgiverkontakt_arbejdsgiver" and "Arbejdsgiverkontakt_arbejdsgiverid" do not exist in the respective subforms. But I've checked the forms and their recordsource, and they are present in both form and the tables they use for recordsource.

    Cheers, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Nov 2012
    Posts
    32
    Hi Trin,

    Access can behave rather strangely sometimes. Can you try putting .requery between assigning recordsource and link properties? Or maybe, just for debugging purposes, divide the process into two parts: first button will assign recordset, the second will set the link properties? Or before assigning the new recordsource remove the link properties first..

    Marek

  5. #5
    Join Date
    Oct 2005
    Posts
    183
    requerying after sourceobject change, and changing linking fields afterwards doesn't resolve the problem.

    it has however made something apparent. The error seems to be provoked "per line" in the subform. If I have 5 related recordsi in the subform, I have to click through it, a new line is added, new error.

    Rather interesting really. There has to be something wrong with the subforms, especially since subforms 3 and 4 are the ones that prove the most troublesome.

    Going from 1 to 3 provokes it, but going from 2 to 3 doesn't. Quirky, n'est-ce pas?
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  6. #6
    Join Date
    Nov 2012
    Posts
    32
    It is interesting. If your database is not too large or with corporate data, post it here.

    I really can't think of anything else right now, I would just try to debug by reducing complexity. Switching of subforms works if no link fields are set? What if something is wrong with recordsources/recordsets of the troublesome subforms? Try to assign each form the same recordset and test...

  7. #7
    Join Date
    Oct 2005
    Posts
    183
    The DB contains a lot of sensitive data, so uploading isn't an option unless I feel like polishing license plates and sewing jeans for 4-6 years.

    I have however just discovered, that the problem disappears, if I change the subforms underlying recordsource from a query to a table. Although it will require a lot more changes than I really wan to do, at least I can see a way out.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  8. #8
    Join Date
    Nov 2012
    Posts
    32
    Ha, I know how that feels :-)

    Hmm, it seems there is something wrong with the fields in those queries, which is probably why it works in tables with fixed structure. I would guess that by the time the form is trying to link the master+child fields the query didn't even finish refreshing the field structure (does it accept any external parameters by any chance?) Access is really weird sometimes, this is another case.

  9. #9
    Join Date
    Oct 2005
    Posts
    183
    Access IS weird. If it wasn't because this is a protoptype / proof-of-concept design, I wouldn't be touching it. Problem is that these prototypes tend to slide into production-use before anyone gets a say.

    Ah well, in a week its no longer my problem, I'll be back on sql-server and .net duty. Other benefits, other concerns.

    Thanks for the sparring btw.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  10. #10
    Join Date
    Nov 2012
    Posts
    32
    No problem, glad to have helped...sort of. I myself am working on a project that has to use Access only, no SQL server solutions... a nightmare. I envy you that you know .NET, I am also trying to learn some C# but the company I work for believes VBA is good enough for everything :P

  11. #11
    Join Date
    Oct 2005
    Posts
    183
    VBA is certainly a good prototyping tool or SOHO solution, but I've worked with Access / VBA on/off for 15 years now, and know all too well, that it quickly turns into a nightmare unless its KISS.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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