Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Pass Values from a Form to a Query (XP)

    Hi

    A few weeks back, I posted a message regarding creating a Form that contained 2 List Boxes. The List Box on the Left would pull its data from a Query that contained all our Current Mailing List Members. When a User clicks an 'ADD' Command Button on the Form, the selected Mailing List would 'move' from the Available List Box to the Selected List Box. Hans very kindly provided code that would allow this to function, although it used a system whereby check boxes were turned on or off to indicate that a value had been selected. This would be great except that many, many people would be generating mail merges. However, leaving that aside, I purchased 2 books {SAMS Teach Yourself Access VBA (which was useless) and Access VBA Step By Step (which is terrific)} in the hope that I would learn how to pass values from a Form to a Query, but unfortunately neither provided a way to do such. Now I'm wondering is it possible to do this in Access VBA?

    Example:
    A user selects 'All Members', 'Additional Contacts', and 'Leadership Team' from a List Box, and when the user hits an EXPORT Command Button, Code would run that passes the values into the query:

    SELECT company_details.name, company_details.address_1, company_details.town, company_details.postcode, list_details.list_name, personnel_details.name, personnel_details.salutation, personnel_details.title, company_details.active, company_list_details.active, list_details.active, personnel_details.active
    FROM ((company_details INNER JOIN company_list_details ON company_details.company_id = company_list_details.company_id) INNER JOIN list_details ON company_list_details.list_id = list_details.list_id) INNER JOIN personnel_details ON company_list_details.personnel_id = personnel_details.personnel_id
    WHERE (((list_details.list_name)="ALL Members") AND ((company_details.active)="Y") AND ((company_list_details.active)="y") AND ((list_details.active)="y") AND ((personnel_details.active)="Y")) OR (((list_details.list_name)="Additional Contacts") AND ((company_details.active)="Y") AND ((company_list_details.active)="Y") AND ((list_details.active)="Y") AND ((personnel_details.active)="Y"))
    OR (((list_details.list_name)="Leadership Team") AND ((company_details.active)="Y") AND ((company_list_details.active)="Y") AND ((list_details.active)="Y") AND ((personnel_details.active)="Y"))
    ORDER BY personnel_details.name;

    From what I've read, I do think this is possible, so I guess I'm asking if someone knows how to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Values from a Form to a Query (XP)

    The general answer to your question is "yes" and the specific answer is "no, not easily".

    If is possible to refer to the value of a text box, combo box, check box etc. in a query, for instance you can set the condition for a field to

    [Forms]![frmSelect]![txtStartDate]

    But from your description, I guess that you have a multi-select list box. A multi-select list box doesn't have "a" value, so you can't refer to it the same way you can refer to a text box, or a single-select list box for that matter.

    You could write a VBA function to retrieve the selected values, and use that in the query, but it would be very inefficient, since the function would be run for each record in the table.

    You could write code that creates the SQL for the query, then saves it (but that might lead to problems in a multi-user environment, depending on how you set it up), or you can initiate the merge from Access and pass the SQL as data source. Advantage is that the SQL is dynamic, and presents no problems if multiple users are merging at the same time.

    This code is not trivial, but it can be done. Post back if you're interested.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Pass Values from a Form to a Query (XP)

    Thanks Hans. As usual, you're a star.

    Well, the List Box way of doing it is the way we would like it done, but would there be a better way that would make what I'm trying to do easier? I suppose I could add Fields to the 'Lists' Table for each user, e.g., Diana, Melanie, Heather, Hilary, Joanne, etc, and then the checkboxes could be set to True/Yes, whenever a user checked their name and 'Added' a List to the 'Selected' List Box.

    For example

    Heather checks her name and the names of the fields she wants included in the Mail Merge: Additonal Contacts and Members. Accordingly, the HEATHER checkboxes that correspond with Additional Contacts and Members change their values to TRUE. The query can then run by using . . .
    WHERE (((tblLists.Heather)=Yes));

    Would either of these be viable?
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Values from a Form to a Query (XP)

    Unless you really have to, I wouldn't recommend adding fields to the table for each user. It is very inflexible - you would need to change the design of the table each time somebody leaves or joins the group of users.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Pass Values from a Form to a Query (XP)

    I was going to do that to see if it would make it easier b/c you said that what I wanted was very complicated. Do you think there's a better solution that doesn't involve List Boxes?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Values from a Form to a Query (XP)

    List boxes are probably a good way to do this, and whatever method you use, it'll take some work because multiple users must be able to merge different groups of records at the same time.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Pass Values from a Form to a Query (XP)

    <hr> . . . or you can initiate the merge from Access and pass the SQL as data source. Advantage is that the SQL is dynamic, and presents no problems if multiple users are merging at the same time. This code is not trivial, but it can be done. Post back if you're interested.<hr>

    OK. I'm interested!!! <img src=/S/artist.gif border=0 alt=artist width=34 height=29> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Values from a Form to a Query (XP)

    I'll whip up an example for you later today.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Pass Values from a Form to a Query (XP)

    You da man!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Values from a Form to a Query (XP)

    I have attached some sample code. Of course, you must substitute the correct names for the list box, document name etcetera.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Pass Values from a Form to a Query (XP)

    Hans <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

    That code is awesome, but I feel so guilty, b/c we use WordPerfect, so you wrote a lot of code that you didn't need to <GUILTTRIP>Brian</GUILTTRIP>. <img src=/S/sad.gif border=0 alt=sad width=15 height=15> .

    I was going to get the users to click the Export to Excel button, b/c I need to make sure that no duplicate values are included (some people can be on many lists).
    But I so appreciate your taking the time to do that. You're awesome. I'll look at the code tonight and try to get it to work, although I'm sure it will! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Values from a Form to a Query (XP)

    I can't help you then, I have no idea how to automate merging with WP. If it allows merging with a SQL string as data source, you may still be able to use the part of the code that constructs the SQL.

    (Don't worry, I didn't write the code for you, I just adapted something I had lying around.)

  13. #13
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Pass Values from a Form to a Query (XP)

    Don't worry! That's totally cool. I just needed to know how to get the values from the List Boxes to the Query. That's the most important part. Once the query has run, I'll add a button to get the user to send it to Excel where they can play a macro that will remove duplicates.

    Once this is run, they just click a Macro in WordPerfect that merges using the Excel File as the Data Source.

    Thanks again. You have no idea how much I appreciate your help.

  14. #14
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Pass Values from a Form to a Query (XP)

    Well, I'm on the last leg of the DB, and the last Form I need to get working is the one discussed in this thread. Thanks to Hans for the code he provided, but in my stupidity, I've hit a snag in that the code is a function, and (obviously) the OnClick Event is a sub. Could someone please explain how I add the Function to the OnClick Command Button, and is there a way to output the result to Excel (and then run a Macro in Excel that shows unique records).

    The Code that was posted is as follows:
    Private Function cmdMerge_Click()
    Dim strWhere As String
    Dim strSQL As String
    Dim strDoc As String
    Dim varItem As Variant

    If Me.lbxSelected.ItemsSelected.Count = 0 Then
    Exit Function
    End If

    strSQL = "SELECT tbl_list_details.list_name, tbl_personneldetails.name, tbl_personneldetails.title, tbl_personneldetails.salutation, tbl_branch.name, tbl_branch.address_1, tbl_branch.address_2, tbl_branch.town, tbl_branch.postcode, tbl_list_details.active, tbl_personneldetails.active, tbl_personneldetails.Alert
    FROM tbl_list_details INNER JOIN (tbl_branch INNER JOIN (tbl_personneldetails INNER JOIN tbl_company_list_details ON tbl_personneldetails.id_personnel = tbl_company_list_details.personnel_id) ON tbl_branch.branch_id = tbl_personneldetails.branch_id) ON tbl_list_details.list_id = tbl_company_list_details.list_id
    WHERE (((tbl_list_details.active)=True) AND ((tbl_personneldetails.active)=True) AND ((tbl_personneldetails.Alert)=False))
    ORDER BY tbl_list_details.list_name, tbl_personneldetails.name;"

    For Each varItem In Me.lbxSelected.ItemsSelected
    strWhere = strWhere & ", '" & lbxSelected.ItemData(varItem) & "'"
    Next varItem
    ' Get rid of initial ", "
    strWhere = Mid(strWhere, 3)

    strSQL = strSQL & strWhere & ") " & _
    "ORDER BY personnel_details.name"
    End Function

    Here's the Excel code that I want to run . . . can I just paste this into the end of the Function?
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 27/10/2003 by Brian
    '

    '
    Selection.EntireColumn.Delete
    ActiveWindow.Zoom = 50
    Columns("I:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Selection.AutoFilter
    Range("A1:H1159").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Range("A1:H1159").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
    "C:merge.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    End Sub

    Thanks in advance for your help. I couldn't have come this far without you. [img]/forums/images/smilies/smile.gif[/img]

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass Values from a Form to a Query (XP)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Stupid mistake on my side. You can just replace Function by Sub:

    Private Sub cmdMerge_Click()
    ...
    If Me.lbxSelected.ItemsSelected.Count = 0 Then
    Exit Sub
    End If
    ...
    End Sub

    However, the SQL as modified by you won't work. Please go back to my code and see how the SQL is constructed. You can't use a complete SQL statement including a WHERE condition and then append another WHERE condition to it.

    If you want to weed out duplicates, you can do that in the SQL statement, by inserting DISTINCT after SELECT:

    strSQL = "SELECT DISTINCT tbl_list_details.list_name ...

    Easier and faster than using advanced filter in Excel.

    There are several ways to transfer the output of the SQL statement to Excel: create a query with this SQL statement and using DoCmd.TransferSpreadsheet or DoCmd.OutputTo to export it to Excel, or use CopyFromRecordset in a macro in Excel.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •