Results 1 to 7 of 7
  1. #1
    veejay is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Location
    Montreal, Canada
    Posts
    46

    VBA code and link between form and subform

    So here's the thing. I have a main form and a subform. I want the subform to "gather" the APPLICATION # from the main form, out of consistency so I created a master link. When I don't add any VBA code I get an error saying i'm trying to reference some VBA code. After poking around I found some VBA code that would be able to help me but it's not working.

    When trying to use the following code I get the error "Method or data member not found"

    Code:
    Private Sub APPLICATION_AfterUpdate()
    Forms![Quality Form]![Score Subform].Form!APPLICATION = Me!APPLICATION.Value
    If DCount("*", "QUALITY_Table", "APPLICATION='" & Me.APPLICATION & "'") > 0 Then
        Me.APPLICATION.ForeColor = vbRed
        Cancel = True
    Else
        Me.APPLICATION.ForeColor = vbBlack
    End If
    End Sub
    When I edit the same code to comment out some part like this:

    Code:
    Private Sub APPLICATION_AfterUpdate()
    Forms![Quality Form]![Score Subform].Form!APPLICATION = Me!APPLICATION.Value
    If DCount("*", "QUALITY_Table", "APPLICATION='" & Me.APPLICATION & "'") > 0 Then
        'Me.APPLICATION.ForeColor = vbRed
        'Cancel = True
    Else
        'Me.APPLICATION.ForeColor = vbBlack
    End If
    End Sub
    I get a 438 error, but the field from the subform is updated to match the APPLICATION field I want as master link.
    I don't really understand why it's not working and without any VBA code it's just not working.

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    application is a reserved word so you may want to start by replacing that.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    And it is better if you do not use spaces in object names.
    Code:
    Forms![Quality Form]![Score Subform].Form!APPLICATION = Me!APPLICATION.Value
    Use
    - "QualityForm" or "Quality_Form"
    - "ScoreSubform" or "Score_Subform"



    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not begin an object name with a number.
    Do not use
    spaces, punctuation or special characters in object names.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @veejay; while the first answer may at first seem insignificant, it and what you posted just might be one of the best examples I've seen for not using reserved words. Me cannot have an APPLICATION object, which would certainly fit the error message you posted because the Application object does not have a forecolor property. In addition to Steve's righteous assertion I offer

    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

    While you might get away with APPLICATION as a field name if it had brackets [ ] around it, let's not try to make a silk purse out of this sow's ear (or should I have said let's not put lipstick on this pig?).
    Last edited by Micron; 12-14-2018 at 08:06 PM. Reason: fixed hyperlink
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Have you tried conditional formatting?

    Also note that the afterupdate event of a control does not have a cancel argument.

    these 2 lines confuse me.
    out of consistency so I created a master link.
    Code:
    Forms![Quality Form]![Score Subform].Form!APPLICATION = Me!APPLICATION.Value
    Is that the master link your refering to? Or are you setting the Master/Child link in the subform control?

  6. #6
    veejay is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Location
    Montreal, Canada
    Posts
    46
    I had totally forgot about the reserved words this is a real noob mistake. I've changed the name of the field, and renamed my tabls/forms that had a space and now it's working well.

    Thanks to all of you.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If your question is solved, you could mark it so.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. subform will not link to main form
    By wolfm in forum Forms
    Replies: 2
    Last Post: 09-28-2017, 09:49 AM
  2. Link main form to subform
    By OllieCat in forum Forms
    Replies: 3
    Last Post: 05-12-2017, 06:01 AM
  3. Replies: 3
    Last Post: 01-05-2012, 07:15 PM
  4. Link main form with subform
    By lizzywu in forum Forms
    Replies: 1
    Last Post: 11-18-2011, 03:22 PM
  5. Replies: 3
    Last Post: 11-16-2011, 01:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums