Page 3 of 3 FirstFirst 123
Results 31 to 34 of 34
  1. #31
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    That is a good question. I believe you are trying to have tis option in your subform so the user can select within the queried results. I believe the only way is to have it bound to the table if it is on the subform because you want it to represent a specific record/row.



    Do you have this check option bound to your table and on your subform?

  2. #32
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    BTW, I have a solution in mind. It is not a simple thing to accomplish but you have much of the structure in place that is needed.

    Please review the comments within the code I provided and give me a status of your progress with your subform recordsource assignment. Let me know if you can successfully bypass pass the query results currently going to .filter. You will need this understanding in the next step. The better you can wrap your head around it the easier you will be able to implement the next step.

  3. #33
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    I've looked at your code behind the comand22 button (which by the way, you should try to follow standard naming control conventions for the ease of reading and debugging code; btnButton, chkBox1, txtBox, fraFrame, pagPage1, etc. rather then the generic names Access provide) and have revised it to the following:

    Private Sub Command22_Click() ' a much more descriptive name would be btnSearch
    ' You don't want to display any of the following is there no matching records

    ' Me.Label28.Visible = True
    ' Me.Box27.Visible = True
    ' Me.Mailing_Options.Visible = True
    ' Me.Mail.Visible = True
    ' Me.Mail_Merge.Visible = True
    ' Me.Export.Visible = True

    Dim strWhere As String
    Dim lngLen As Long

    ' Validate input
    If IsNull(Me.s_PostalCode) Or (Me.s_PostalCode = "") Then ' Do nothing if nothing was entered
    Else
    ' Only execute next line of code is something was entered by user
    'If Not IsNull(Me.s_PostalCode) Then
    strWhere = strWhere & "([PostalCode] = """ & s_PostalCode & """) AND "
    End If

    If IsNull(Me.s_Company) Or (Me.s_Company = "") Then
    Else
    'If Not IsNull(Me.s_Company) Then
    strWhere = strWhere & "([Company] Like ""*" & Me.s_Company & "*"") AND "
    End If

    If IsNull(Me.s_County) Or (Me.s_County = "") Then
    Else
    'If Not IsNull(Me.s_County) Then
    strWhere = strWhere & "([County] Like ""*" & Me.s_County & "*"") AND "
    End If

    If IsNull(Me.CategoryList) Or (Me.CategoryList = "") Then
    Else
    'If Not IsNull(Me.CategoryList) Then
    strWhere = strWhere & "([Category] Like ""*" & Me.CategoryList & "*"") AND "
    End If

    If Me.AGM = -1 Then
    strWhere = strWhere & "([AGM] = true) AND "
    End If

    If Me.s_Newsletter = -1 Then
    strWhere = strWhere & "([Newsletter] = true) AND "
    End If


    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    ' Hide controls if there's no valid criteria
    Me.Label28.Visible = False
    Me.Box27.Visible = False
    Me.Mailing_Options.Visible = False
    Me.Mail.Visible = False
    Me.Mail_Merge.Visible = False
    Me.Export.Visible = False
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
    strWhere = Left$(strWhere, lngLen)
    ' There's a valid criteria, now count number of record matching criteria, if any
    If DCount("*", "Filter_Contacts", strWhere) <= 0 Then
    MsgBox "There are no records matching criteria", vbExclamation, "No Matches"
    Exit Sub
    End If
    ' Display controls, turn on filter
    Me.View_Contacts_subform.Visible = True
    Me.View_Contacts_subform.Form.Filter = strWhere
    Me.View_Contacts_subform.Form.FilterOn = True
    Me.Label28.Visible = True
    Me.Box27.Visible = True
    Me.Mailing_Options.Visible = True
    Me.Mail.Visible = True
    Me.Mail_Merge.Visible = True
    Me.Export.Visible = True
    End If


    End Sub

    Finally, rather than taking the length of the string strWhere, start the string with the following statement
    strWhere = "1=1 "
    follow it with your conditions, strWhere & " AND blah=blah or whatever criteria you're using"
    this way there's no lingering "AND" to worry about.

    You export code should now work.

  4. #34
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I have made some progress toward your desire to have a way for the user to select records that they don't want via a checkbox. I still need to add some code in case the user wants to requery within their original results. It may sound straight forward, but having a checkbox that is bound to the actual record that represents the contact is problematic. If more than one user should query the same contact and wish to export, you will have a conflict.

    I have already incorporated mrojas' code into the control.....


    Anyway, I hope you are able to read the notes within the code I offered. I will be adding onto that code and it will benefit you if you can reverse engineer it.

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

Similar Threads

  1. Exporting filtered results to Excel
    By doublec9 in forum Import/Export Data
    Replies: 2
    Last Post: 06-04-2013, 09:05 PM
  2. Mail merge for filtered results
    By har273 in forum Access
    Replies: 1
    Last Post: 01-03-2012, 12:12 PM
  3. Export Filtered Results to Excel Tabs
    By opod in forum Programming
    Replies: 1
    Last Post: 08-03-2011, 01:33 PM
  4. Save Combobox results to Table
    By Kinder0177 in forum Access
    Replies: 5
    Last Post: 06-29-2011, 08:47 AM
  5. Replies: 1
    Last Post: 10-24-2010, 04:01 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