Results 1 to 8 of 8
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Private Function

    I have a form with lists of records. On double click of any of the fields, I want something to do. So, I made a code:

    Private Function OpenWeaponSubF() As Boolean
    DoCmd.OpenForm "frmWeaponsSubFrm", , , "[EquipID]=" & [EquipID]
    End Function

    It worked, until I add an if statement. I have 2 forms that I want opened up depending on the [EquipTypeID]. Here's the new code:

    Private Function OpenWeaponSubF() As Boolean
    If EquipTypeID = 23 Or 24 Then
    DoCmd.OpenForm "frmWeaponsOtherReturn", , , "[EquipID]=" & [EquipID]
    Else
    DoCmd.OpenForm "frmWeaponsSubFrm", , , "[EquipID]=" & [EquipID]
    End If
    End Function

    However, even if the [EquipTypeID] is something other than 23 or 24, it will only open up the [frmWeaponsOtherReturn] form. What am I doing wrong?

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your syntax is off you need to refer to the field each time and use a sub not a function. A function normally returns a result - you are simply opening a form.

    Code:
     If Me.EquipTypeID = 23 Or  Me.EquipTypeID = 24 Then ....
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Thanks. I changed it to sub. It gives a different error now. It looks like it can't find the [EquipTypeID]. It's there. The information is coming a union query. I took out the if statement to make sure it would work and it is. I made the code:

    Private Sub OpenWeaponSubF()
    If Me.EquipTypeID = 23 Or Me.EquipTypeID = 24 Then
    DoCmd.OpenForm "frmWeaponsOtherReturn", , , "[EquipID]=" & [EquipID]
    Else
    DoCmd.OpenForm "frmWeaponsSubFrm", , , "[EquipID]=" & [EquipID]
    End If

    End Sub

    Now, it gives me this error:
    Click image for larger version. 

Name:	E1.jpg 
Views:	15 
Size:	17.9 KB 
ID:	33849
    Click image for larger version. 

Name:	E2.jpg 
Views:	14 
Size:	66.9 KB 
ID:	33850

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Here's the query. [EquipTypeID] is there.

    Click image for larger version. 

Name:	Q1.jpg 
Views:	13 
Size:	112.3 KB 
ID:	33851

  5. #5
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I removed the Me. and it opened a form without an error. But it will only open [frmWeaponsSubFrm]. It won't open [frmWeaponsOtherReturn]
    Here's the new code:

    Private Sub OpenWeaponSubF()
    If EquipTypeID = 23 Or EquipTypeID = 24 Then
    DoCmd.OpenForm "frmWeaponsOtherReturn", , , "[EquipID]=" & [EquipID]
    Else
    DoCmd.OpenForm "frmWeaponsSubFrm", , , "[EquipID]=" & [EquipID]
    End If

    End Sub

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is that query the record source for the form you are running the code from?
    If not it won't be able to reference it. And what is the control name that the EquipmentTypeId is being displayed in ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Add some basic debugging;

    Code:
    Private Sub OpenWeaponSubF()
    
    Debug.Print "Equip Type: " & nz(EquipTypeID,"Null Value") 
    If EquipTypeID = 23 Or EquipTypeID = 24 Then
         DoCmd.OpenForm "frmWeaponsOtherReturn", , , "[EquipID]=" & [EquipID]
    Else
         DoCmd.OpenForm "frmWeaponsSubFrm", , , "[EquipID]=" & [EquipID]
    End If
    
    End Sub
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Yes. it is the source for the form. You gave me an idea though. I had forgotten to add the field to the form. It's working great. Thanks.

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

Similar Threads

  1. Private Sub Form_Current use
    By Lou_Reed in forum Access
    Replies: 5
    Last Post: 01-20-2017, 09:32 AM
  2. Replies: 2
    Last Post: 02-10-2014, 07:41 AM
  3. Turning Private Function Into Public
    By g4tv4life in forum Programming
    Replies: 1
    Last Post: 02-04-2014, 05:31 PM
  4. Calling a Private Function from Another Form
    By MintChipMadness in forum Programming
    Replies: 1
    Last Post: 01-07-2013, 12:08 PM
  5. Replies: 9
    Last Post: 12-20-2010, 08:05 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