Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107

    Open a Form in Datasheet View from Navigation Form

    Hello,



    I have a form with four unbound text boxes and a button to run Command1037. Since this form is a sub form of a Navigation form I need to pass the following variables into the form I want to open. Can someone tell me how I add all four variables to this line DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, , "TxtPlanner =" & Me.TxtPlanner


    These are the four variables I need to add to one line
    "TxtPlanner =" & Me.TxtPlanner
    "TxtPlant =" & Me.TxtPlanner
    "TxtRevision =" & Me.TxtPlanner
    "TxtOrder =" & Me.TxtPlanner

    This is what I have so far

    Private Sub Command1037_Click()
    DoCmd.Close acForm, "frmOrderStatusDataSheet", acSaveNo
    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, , "TxtPlanner =" & Me.TxtPlanner
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Just more concatenation. If these fields are text type, need apostrophe delimiters. Why is each field comparing to the same textbox?


    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, , "TxtPlanner ='" & Me.TxtPlanner & "' AND " & _
    "TxtPlanner ='" & Me.TxtPlanner & "' AND " & _
    "TxtPlant ='" & Me.TxtPlanner & "' AND " & _
    "TxtRevision ='" & Me.TxtPlanner & "' AND " & _
    "TxtOrder ='" & Me.TxtPlanner & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Ok. I think my approach is wrong.


    The query for the form I want to open is

    SELECT tblOrderNotifications.CreatedOn, tblObjectPriority.Descripton, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, [tblOrderStatus.AssignUsername] AS [Assigned To], [tblOrderDetails.PlannerGroup] AS Planner, tblObjectStatus.Status, tblOrderStatus.Project, [qrySapMhrs.SumOfPlannedHours] AS [Sap Mnhrs], SumScore.pctComp
    FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (((tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) LEFT JOIN SumScore ON tblOrderDetails.OrderNum = SumScore.OrderNum) LEFT JOIN qrySapMhrs ON tblOrderDetails.OrderNum = qrySapMhrs.OrderNum) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification
    WHERE (((tblOrderDetails.WBS) Like "*" & [Forms]![frmOrderStatus]![TxtPlant] & "*") AND ((tblOrderDetails.Revision) Like "*" & [Forms]![frmOrderStatus]![TxtRevision] & "*") AND ((tblOrderDetails.OrderNum) Like "*" & [Forms]![frmOrderStatus]![TxtOrder] & "*") AND (([tblOrderDetails.PlannerGroup]) Like "*" & [Forms]![frmOrderStatus]![TxtPlanner] & "*") AND ((tblProjectStatus.DateClosed) Is Null))
    ORDER BY tblOrderDetails.Revision;

    Im using the following on this query to filter the list
    Like "*" & [Forms]![frmOrderStatus]![TxtOrder] & "*"
    Like "*" & [Forms]![frmOrderStatus]![TxtRevision] & "*"
    Like "*" & [Forms]![frmOrderStatus]![TxtPlant] & "*"
    Like "*" & [Forms]![frmOrderStatus]![TxtOrder] & "*"

    This is my button code from the first form

    Private Sub Command1037_Click()
    DoCmd.Close acForm, "frmOrderStatusDataSheet", acSaveNo
    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS
    End Sub


    Normally this would work but since the form is on a navigation form then all is lost..... If I use the code you provided the list is empty

    Private Sub Command1037_Click()
    DoCmd.Close acForm, "frmOrderStatusDataSheet", acSaveNo
    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, , "TxtPlanner ='" & Me.TxtPlanner & "' AND " & _
    "TxtPlant ='" & Me.TxtPlant & "' AND " & _
    "TxtRevision ='" & Me.TxtRevision & "' AND " & _
    "TxtOrder ='" & Me.TxtOrder & "'"
    End Sub


    What is the proper way to open a form from another that is located on a nav form?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you are building filter criteria in VBA, why do you have filter criteria in the query? Don't do both.

    Use the LIKE and wildcard structure in the VBA. Or only include criteria where parameters are provided. Review: http://allenbrowne.com/ser-62.html

    I don't use Navigation form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    I already built the filter criteria using his formula. What I would like for the user is to be able to open the same criteria in a data sheet form.

  6. #6
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    So to transfer the filter to the new form I would use Me.Filter = strWhere ?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes. Have you tried it?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Private Sub Command1037_Click()
    DoCmd.Close acForm, "frmOrderStatusDataSheet", acSaveNo
    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, Me.Filter = Me.strWhere
    Me.FilterOn = True

    End Sub

    Me.Filter = Me.strWhere is not working

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are you using Me.strWhere? Me. is to prefix references to controls or fields on form, not a VBA variable.

    Where is the code building string variable?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Is it possible to pull the filter that is already applied on the first form?

    Private Sub Command1037_Click()
    DoCmd.Close acForm, "frmOrderStatusDataSheet", acSaveNo
    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, Me.Filter = First Form Filter
    Me.FilterOn = True

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Exactly what is the 'first form'?

    Why are you closing then opening the same form?

    If you use WHERE CONDITION argument of OpenForm, there is no need to set Filter property.

    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, , strWhere

    If you want to apply filter to already open form, then set the Filter property.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    I want to make sure that form 2 is closed before I open. Is this wrong?

    Compile Error: Variable not defined.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not wrong, I just don't understand the need. As I said, if form is already open, set Filter property.

    So, declare the variable.

    You need to either provide all the relevant code or the database. Otherwise, we will continue going in circles.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Hi June7,

    Here is the filter code on the first form. I would like to open the second form with the results of the first form.

    Private Sub Command1019_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"


    If Not IsNull(Me.TxtRevision) Then
    strWhere = strWhere & "([Revision] Like ""*" & Me.TxtRevision & "*"") AND "
    End If
    If Not IsNull(Me.TxtPlant) Then
    strWhere = strWhere & "([WBS] Like ""*" & Me.TxtPlant & "*"") AND "
    End If

    If Not IsNull(Me.TxtPlanner) Then
    strWhere = strWhere & "([PlannerGroup] Like ""*" & Me.TxtPlanner & "*"") AND "
    End If

    If Not IsNull(Me.TxtOrder) Then
    strWhere = strWhere & "([OrderNum] Like ""*" & Me.TxtOrder & "*"") AND "
    End If


    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
    strWhere = Left$(strWhere, lngLen)
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So you are simply setting filter property of first form. Now if you want to open another form via a different button, then reference the filter property:

    DoCmd.OpenForm "frmOrderStatusDataSheet", acFormDS, , Me.Filter


    Advise you give controls more meaningful names, like: btnFilter, btnOpenDS. Then the procedure declaration would be like: Private Sub btnFilter_Click()




    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. want dialog form to open in datasheet view
    By gunitinug in forum Access
    Replies: 2
    Last Post: 09-04-2017, 08:13 PM
  2. Replies: 5
    Last Post: 05-21-2015, 10:33 AM
  3. Replies: 8
    Last Post: 06-25-2014, 08:03 AM
  4. Replies: 3
    Last Post: 08-18-2013, 09:14 PM
  5. Replies: 3
    Last Post: 02-23-2012, 04:48 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