Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    3 command buttons on a form

    ALL,
    I have 3 buttons on a form, FormFirst. "CmdButtonA", "CmdButtonB", and "CmdButtonC".



    I have 3 queries made. QueryA, QueryB, and QueryC.

    I want to open another form, FormSecond, using all CmdButtons. (I have set this up.)

    The part I am looking to do is:

    If I select CmdButton A, I want FormSecond to use the data from QueryA
    If I select CmdButton B, I want FormSecond to use the data from QueryB
    If I select CmdButton C, I want FormSecond to use the data from QueryC

    FormFirst Code

    Code:
    Option Compare Database
    Dim SelectQryAs String
    
    Private Sub CancelFormFirst_Click()
    DoCmd.Close acForm, "FormFirst"
    End Sub
    
    Private Sub CmdButtonA_Click()
    SelectQry = "QueryA"
    DoCmd.OpenForm "FormSecond"
    End Sub
    
    Private Sub CmdButtonB_Click()
    SelectQry = "QueryB"
    DoCmd.OpenForm "FormSecond"
    End Sub
    
    Private Sub CmdButtonC_click()
    SelectQry = "QueryC"
    DoCmd.OpenForm "FormSecond"
    End Sub
    FormSecondCode

    Code:
    Option Explicit
    Private Sub CancelFormSecond_Click()
        DoCmd.Close acForm, "FormSecond"
    End Sub
    
    Private Sub SelectQueryData_Click()
        Dim As strQuery As String, strQuery2 As String, ctl As Control, varItem As Variant, 
        Set ctl = Me.QueryNum
       
    Docmd.OpenQuery Form_FormFirst.SelectQry ' ets code - doesn't seem to work
       
    If Me.QueryNum.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    
    For Each varItem In ctl.ItemsSelected
       strQuery = strQuery & "'" & ctl.ItemData(varItem) & "',"  'Sets up for data processing
       strQuery2 = strQuery2 & " " & ctl.ItemData(varItem) & ", " ' Sets up for Subject line and file naming convention 
    
    
    Next varItem
       strQuery = Left(strQuery, Len(strQuery) - 1)
       
       strDataQry = strQuery2 ' Use in other modules
       
       DoCmd.OpenReport "rptSelectQuery", acViewReport, , "QueryNumIN(" & strQuery & ")"
       DoCmd.Close acForm, "FromSecond"
    
    
    End Sub
    I also tried to put - =Form_FormFirst.SelectQry in FormSecond Record Source. Didn't work.

    Thanks Wayne

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Private Sub CmdButtonA_Click()
    Forms!FormSecond.RecordSource = "Select ..... SQL for queryA....."
    DoCmd.OpenForm "FormSecond"
    End Sub
    This should work. I'm not sure how to specify queryA directly, so I used its SQL.
    Last edited by davegri; 06-19-2016 at 07:31 AM. Reason: CLARITY

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks davegri,
    I have the buttons worked out in the form. I came up with another issue(s).

    I have an unbound control OOBNum on FormSecond that sets up a report rptSecond to pdf. How would I be able to put the query into the unbound control? Or add 2 controls to the unbound OOB Row Source?

    Current OOBNum Row Source =
    SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber FROM TblChangeRequest WHERE ((([CRNo] + ([SubNo] * 0.01)) Is Not Null) And ((CRNo) <> 0) And ((ActionComplete) = False));

    I would like to add 2 more controls on the fly to slim down the query.
    This changes depending on which command button is selected.

    CmdButtonA = And ((AOVote) = 'Open') And ((O6Vote) Is Null) And ((ActionComplete) = False))

    CmdButtonB = And ((O6Vote) Is Null) AND ((AOVote)<>"Defer" Or (AOVote)<>"Open" Or (AOVote)<>"Hold"))

    CmdButtonC = And ((AOVote) Is Not Null) AND ((O6Vote) Is Not Null And (O6Vote)<>"Defer" Or (O6Vote)<>"Hold"))

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can set the recordsouce for reports the same way as forms.
    Last edited by davegri; 06-19-2016 at 11:44 AM. Reason: clarity

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ok gotcha on the form/reports

    I was trying to place
    Forms!FormFirst!strQuery in the OOBNum query column as criteria.

    In FormFirst VBA for CommandButtonA

    Me.qrySelect = "<> 'Open' Or <> 'Hold' Or <> 'Defer'"
    Me.qrySelect2 = "Is Null"

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    I was trying to place 
    Forms!FormFirst!strQuery in the OOBNum query column as criteria.
    
    In FormFirst VBA for CommandButtonA
    
    Me.qrySelect = "<> 'Open' Or <> 'Hold' Or <> 'Defer'"
    Me.qrySelect2 = "Is Null"
    Ah... I don't see a question there.
    What's not working and what are the symptoms? What are you trying to accomplish in plain English?

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by davegri View Post
    Code:
    I was trying to place 
    Forms!FormFirst!strQuery in the OOBNum query column as criteria.
    
    In FormFirst VBA for CommandButtonA
    
    Me.qrySelect = "<> 'Open' Or <> 'Hold' Or <> 'Defer'"
    Me.qrySelect2 = "Is Null"
    Ah... I don't see a question there.
    What's not working and what are the symptoms? What are you trying to accomplish in plain English?

    Since AOVote and O6Vote are the only 2 fields that change to whittle down the amount of "OOB" I am trying to insert Me.qrySelect into the AOVote and me.qrySelect2 into the O6Vote criteria. This way it doesn't matter what CommandButton I choose on FormFirst, I will get the OOB I need pertaining to the CommandButton.


    Click image for larger version. 

Name:	QrySelect.png 
Views:	45 
Size:	8.7 KB 
ID:	24944

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    For the shown query (named OOBNum?), go to SQL view and look at what you have and post the SQL here.
    Is qrySelect a combo box? If so, post its rowsource here also.

    The below code will allow you to create a named query from an SQL string. The named query could be a report recordsource, for example.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : fcnCustomizeSQL
    ' DateTime  : 9/26/2006 20:57
    ' Author      : David
    ' Purpose    : Attach new SQL property to an existing querydef. If the Query doesn't exist,
    '                : create it with the passed SQL.
    '                : qName is the query name to create, strPassedSQL is the SQL statement
    '---------------------------------------------------------------------------------------
    '
    Function fcnCustomizeSQL(qName As String, strPassedSQL As String) As Boolean
        Dim qthisQuery As DAO.QueryDef
        On Error GoTo fcnCustomizeSQL_Error
        'if the query has been deleted, create it
        If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
            Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
            Set qthisQuery = Nothing
            Exit Function
        End If
        'else modify it
        Set qthisQuery = CurrentDb.QueryDefs(qName)
        qthisQuery.SQL = strPassedSQL
    fcnCustomizeSQL_Exit:
        Set qthisQuery = Nothing
        Exit Function
    fcnCustomizeSQL_Error:
        msgbox Err.Number  & ", " & Err.Description & " in fcnCustomizeSQL"
        Resume fcnCustomizeSQL_Exit
    End Function
    Last edited by davegri; 06-19-2016 at 02:08 PM. Reason: syntax

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    The unbound control "OOBNumber" Row Source code:

    Code:
    SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber
    FROM tblChangeRequest
    WHERE (((tblChangeRequest.ActionComplete)=False))
    GROUP BY [CRNo]+([SubNo]*0.01)
    HAVING ((([CRNo]+([SubNo]*0.01)) Is Not Null));
    me.qrySelect and me.qrySelect2 = are unbound text boxes on FormFirst.

    Code
    Code:
    Form_frmOOBChangeSelect.RecordSource = ("SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber, ChangeRequested, Rationale, NOTES, ActionItems, AOVote, Priority, Hr, [Level], NIE, ChangeType, DateID, Format([DateID],'dddd'', ''mmm d yyyy') AS Dates, Format(Now()+([Hr]/24),'hhnn dddd'', ''mmm d yyyy') AS [Time], Format(Now()+([Hr]/24),'hhnn dddd'', ''mmm d yyyy') AS DTG, [Unit] & Chr(13) & Chr(10) & [Section] AS Units, [HBVersion] & Chr(13) & Chr(10) & [ApproxPage] AS HBVers, [MTOEPara] & Chr(13) & Chr(10) & [BumperNum] AS MTOEParas, [Requestor] & vbrclf & [Sponsor] AS People, DateDiff('d',[DateID],[DateClosed]) AS DaysOpen FROM TblChangeRequest WHERE (((ActionComplete)=False) AND ((AOVote)='Open') AND (CRNo)<> 0)")
    
    Me.qrySelect = "<> 'Open' Or <> 'Hold' Or <> 'Defer'"
    Me.qrySelect2 = "Is Null"
    
    
    DoCmd.OpenForm "FormSecond"
    FormSecond Code
    Code:
    Option Explicit
    Private Sub CancelChanges_Click()
        DoCmd.Close acForm, "frmOOBChangeSelect"
    End Sub
    Private Sub SelectOOBChanges_Click()
        Dim strWhere As String, ctl As Control, varItem As Variant, strWhere2 As String
    
    
        Set ctl = Me.OOBNumber
    
    
    If Me.OOBNumber.ItemsSelected.Count = 0 Then
         MsgBox "Nothing was selected"
            Exit Sub
        End If
    
    
    For Each varItem In ctl.ItemsSelected
       strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
       strWhere2 = strWhere2 & " " & ctl.ItemData(varItem) & ", "
    
    
    Next varItem
       strWhere = Left(strWhere, Len(strWhere) - 1)
       
       Me.OOBChanges = strWhere2
       
     MsgBox OOBChanges
         
     DoCmd.OpenReport "rptOOB", acViewReport, , "OOBNumber IN(" & strWhere & ")"
    
    
    End Sub

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    qrySelect provides incorrect syntax to the SQL expression.
    The correct syntax would be
    AOVote <> 'Open' OR AOVote <> 'Hold' OR AOVote <>'Defer'
    The AOVote needs to be repeated for each comparison.

  11. #11
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by davegri View Post
    qrySelect provides incorrect syntax to the SQL expression.
    The correct syntax would be
    AOVote <> 'Open' OR AOVote <> 'Hold' OR AOVote <>'Defer'
    The AOVote needs to be repeated for each comparison.

    Davgri,
    Fixed on that part for formatting. I still get a null or empty when I place Forms!OOBSetup!qrySelect in the criteria of AOVote column in the unbound control on the form. There is a query in it to filter out the undesired OOBNumbers.

    OOBNumber SQL:
    Code:
    SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber
    FROM tblChangeRequest
    GROUP BY [CRNo]+([SubNo]*0.01), tblChangeRequest.ActionComplete
    HAVING ((([CRNo]+([SubNo]*0.01)) Is Not Null) AND ((tblChangeRequest.ActionComplete)=False));

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You need to see what Forms!FormFirst!qrySelect presents to the SQL. Do something like this:

    Code:
    Sub subTest()
    Dim sSQL As String
    sSQL = "Select AOVote from tblChangeRequest WHERE " & Forms!FormFirst!qrySelect
    Debug.Print sSQL
    End Sub
    end sub
    Put this little sub in a module. Make sure FormFirst is loaded and qrySelect has a value in it. In the immediate window type 'call subTest' ENTER
    Examine the print line produced by the debug.print. Adjust qrySelect until the query syntax is correct.

    Now, add the code I provided before in #8 to the same module.
    Modify subTest to this:

    Code:
    Sub subTest()
    Dim sSQL As String
    sSQL = "Select AOVote from tblChangeRequest WHERE " & Forms!FormFirst!qrySelect
    Debug.Print sSQL
    Call fcnCustomizeSQL("I_STILL_DONT_KNOW_THE_NAME",sSQL)
    End Sub
    This will create a named query 'I_Still_dont....) that will have the correct criteria.

  13. #13
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Function Modules are not well known to me. I'll play with it at work. BUT. It seems an awful lot to do when all you are is putting a string into the criteria of query. One would think that you set up an unbound control as a string in the CommandButton_Click() on the forms' VBA:

    Code:
    Private Sub CommandButtonA_Click()    
        Me.AOSelect = "Open"
        Me.O6Select = "Is Null"
    DoCmd.OpenForm "FormSecond"
    End Sub
    Then when you open FormSecond, you should be able to reference it in the unbound control, such as:
    Code:
    SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber
    FROM tblChangeRequest
    WHERE (((tblChangeRequest.ActionComplete)=False) AND ((tblChangeRequest.AOVote)=[forms]![frmOOBSetup]![AOSelect]))
    GROUP BY [CRNo]+([SubNo]*0.01)
    HAVING ((([CRNo]+([SubNo]*0.01)) Is Not Null));
    This will work if I have AOVote =[forms]![frmOOBSetup]![AOSelect] where AOSelect = "Open" or a single word. It will run all the way through to opening the report with the correct data. If I add in "and Defer" it won't work. If I add in the code for O6Vote it won't work. It comes up with nothing.

    Additionally, I made up a single query where all the Open OOBNumber data resides. There is no criteria on the AOVote/O6Vote. This the need to filter out all but the exact criteria I need. Would this be easier if I used some sort of filter on FormSecond?

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Work with just the little subTest in reply #12. It will help you get your syntax correct with what is in Me.AOSelect.

  15. #15
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by davegri View Post
    Work with just the little subTest in reply #12. It will help you get your syntax correct with what is in Me.AOSelect.
    I think I have a way but I keep coming up with a null. This is for FormSecond with the unbound control named "OOBNumber."

    Forms!frmOOBSetup!AOSelect = " = Open" and Forms!frmOOBSetup!O6Select = " Is Null" for FormFirst CommandButtonA.

    Code:
    Private Sub Form_Load()
    Dim AOSelected As String, O6Selected As String, strTesT As String
    
    Private Sub Form_Load()
    AOSelected = Forms!frmOOBSetup!AOSelects
    O6Selected = Forms!frmOOBSetup!O6Selects
    
    MsgBox "= " & AOSelected & vbCrLf & "  " & O6Selected
    
    strTesT = "(" & "'SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber From qryOOB Where (((AOVote)" & AOSelected & ") And ((O6Vote)" & O6Selected & ")) HAVING ((([CRNo]+([SubNo]*0.01)) Is Not Null) AND ((ActionComplete)=False))'" & ")"
    MsgBox strTesT
    
    Me.OOBNumber.RowSource = strTesT
    
     MsgBox "= " & Me.OOBNumber
    
    End Sub
    MsgBox "= " & AOSelected & vbCrLf & " " & O6Selected is:

    = Open
    Is Null



    MsgBox strTesT is:
    ('SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber From qryOOB Where (((AOVote) = Open) And ((O6Vote) Not Is Null)) HAVING ((([CRNo]+([SubNo]*0.01)) Is Not Null) AND ((ActionComplete)=False))')



    MsgBox "= " & Me.OOBNumber is:
    =
    I don't error out either.

    How would I get [strTest] into the RowSource for OOBNumber? I have tried = [strTest] in OOBNumber Row Source

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 09-08-2015, 04:43 PM
  2. Command buttons & images displayed on a form
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 10-15-2013, 09:34 AM
  3. Command Buttons Stopped Working on Form
    By genest11 in forum Reports
    Replies: 12
    Last Post: 02-06-2013, 06:59 AM
  4. Form Command buttons
    By chazcoral2 in forum Forms
    Replies: 6
    Last Post: 05-01-2012, 08:10 AM
  5. Command Buttons on form
    By bespra in forum Forms
    Replies: 10
    Last Post: 11-12-2011, 01:50 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