Results 1 to 8 of 8
  1. #1
    KNap is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    28

    Confused!!! Need macro help

    Hi



    If two field names on the same form say "yes" I want to run a macro which opens a form on the second yes..... so far I have:

    ([FRM - Housing History]![Is Client a Care Leaver?]="Yes" And [FRM - Housing History]![If 'Yes' Is Client under 21 yrs of Age?]="yes") then

    but this isn't working..... Please help, I'm really stuck.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Code needs to be in each combo box...
    Code:
    sub cmoBox1_afterupdate()
      CheckYes
    end sub
    
    sub cmoBox2_afterupdate()
      CheckYes
    end sub
    
    sub CheckYes()
    if cboBox1='Yes' and cboBox2= 'Yes' then docmd.openForm "fMyForm"
    end sub

  3. #3
    KNap is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    28
    I'm so sorry - I'm self taught and a bit overwhelmed at the minute.

    Is the above the only statement that I have to put in each of the boxes? Also there doesn't seem to be a command to open the form with the warning message on it?

    I'm so sorry to be so dumb!

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Sorry , wrote openquery, change it to openform.
    when the user changes cbobox1, vb must check both.
    when the user changes cbobox2, vb must check both.

    This is done via CheckYes routine.

  5. #5
    KNap is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    28
    This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.

    Put your suggestion into both fields and the above comes up.... I know that this is me being completely dumb and I thank you for you patience.

  6. #6
    KNap is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2013
    Posts
    28
    Private Sub If__Yes__Is_Client_under_21_yrs_of_Age__Click()
    Sub cmoBox1_afterupdate()
    CheckYes
    End Sub


    Sub cmoBox2_afterupdate()
    CheckYes
    End Sub


    Sub CheckYes()
    If cboBox1 = "Yes" And cboBox2 = "Yes" Then DoCmd.OpenForm "POP UP Social Services Duty of Care"
    End Sub
    End Sub


    Private Sub Is_Client_a_Care_Leaver__AfterUpdate()
    Sub cmoBox1_afterupdate()
    CheckYes
    End Sub


    Sub cmoBox2_afterupdate()
    CheckYes
    End Sub


    Sub CheckYes()
    If cboBox1 = "Yes" And cboBox2 = "Yes" Then DoCmd.OpenForm "POP UP Social Services Duty of Care"
    End Sub
    End Sub

    This is what the code looks like on my screen

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You have way too many END SUBs.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by KNap View Post
    If two field names on the same form say "yes"
    Just so you understand the terms, Tables have FIELDS, Forms have CONTROLS.

    Quote Originally Posted by KNap View Post
    ([FRM - Housing History]![Is Client a Care Leaver?]="Yes" And [FRM - Housing History]![If 'Yes' Is Client under 21 yrs of Age?]="yes") then
    You should Google "Naming Conventions".
    Object names should only be letters, numbers or the underscore.
    NO spaces, punctuation or special characters.
    Object names should be short but descriptive.

    These are poor form names:
    "FRM - Housing History".
    "POP UP Social Services Duty of Care".

    Better form names would be:
    "frmHousingHistory" or "frmHousing_History"
    "POPUP_SocialServicesDuty_of_Care" or "POPUP_SocialServicesDutyOfCare"


    These are poor field names:
    [Is Client a Care Leaver?]
    [If 'Yes' Is Client under 21 yrs of Age?]
    They have spaces, single quotes and the question mark.

    Better names would be:
    [ClientIsCareLeaver] or [Client_Is_Care_Leaver]
    [ClientIsUnder21] or [Client_Is_Under_21]



    This is NOT a macro. This is code....
    Code:
    Private Sub If__Yes__Is_Client_under_21_yrs_of_Age__Click()  '<< Can't have nested Subs
    Sub cmoBox1_afterupdate()
      CheckYes
    End Sub
    
    
    Sub cmoBox2_afterupdate()
      CheckYes
    End Sub
    
    
    Sub CheckYes()
    If cboBox1 = "Yes" And cboBox2 = "Yes" Then DoCmd.OpenForm "POP UP Social Services Duty of Care"
    End Sub
    End Sub   '<<< too many "End Sub"s
    
    
    Private Sub Is_Client_a_Care_Leaver__AfterUpdate()  '<< Can't have nested Subs
    Sub cmoBox1_afterupdate()
      CheckYes
    End Sub
    
    
    Sub cmoBox2_afterupdate()
      CheckYes
    End Sub
    
    
    Sub CheckYes()
    If cboBox1 = "Yes" And cboBox2 = "Yes" Then DoCmd.OpenForm "POP UP Social Services Duty of Care"
    End Sub
    End Sub   '<<< too many "End Sub"s

    It sounds like you have two text boxes on the form, so the code might look like:
    Code:
    Private Sub If__Yes__Is_Client_under_21_yrs_of_Age__AfterUpdate()  '<< Can't have nested Subs
       CheckYes
    End Sub
    
    Private Sub Is_Client_a_Care_Leaver__AfterUpdate()  '<< Can't have nested Subs
       CheckYes
    End Sub
    
    
    Sub CheckYes()
      If If__Yes__Is_Client_under_21_yrs_of_Age = "Yes" And Is_Client_a_Care_Leaver = "Yes" Then
      DoCmd.OpenForm "POP UP Social Services Duty of Care"
      End IF
    End Sub

    If the two controls are check boxes, try:
    Code:
    Private Sub If__Yes__Is_Client_under_21_yrs_of_Age__Click()  '<< Can't have nested Subs
       CheckYes
    End Sub
    
    Private Sub Is_Client_a_Care_Leaver__Click()  '<< Can't have nested Subs
       CheckYes
    End Sub
    
    
    Sub CheckYes()
      If If__Yes__Is_Client_under_21_yrs_of_Age = TRUE And Is_Client_a_Care_Leaver = TRUE Then
      DoCmd.OpenForm "POP UP Social Services Duty of Care"
      End IF
    End Sub

    "Yes" is a text string. True is a Boolean value. Depending on the field type the control is bound to determines what you compare the control value to.
    If the control value is a Boolean, use TRUE. If the control value is a Text String, use "Yes".

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

Similar Threads

  1. SQL for Confused Join
    By gazzieh in forum Programming
    Replies: 6
    Last Post: 05-01-2014, 04:42 AM
  2. I am confused please help me
    By pedjvak in forum Forms
    Replies: 5
    Last Post: 04-20-2013, 02:13 AM
  3. I am a bit confused by this one
    By wubbit in forum Access
    Replies: 7
    Last Post: 05-15-2012, 03:18 PM
  4. Confused!!!
    By mkc80 in forum Access
    Replies: 1
    Last Post: 05-11-2012, 04:39 PM
  5. Just Confused
    By BigCat in forum Access
    Replies: 1
    Last Post: 05-09-2011, 12:57 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