Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Working with Recordsets (A2K SR1)

    The below code takes data from a multi select listbox and populates a table. However, I now want to extract data from Table1 where the WBSID in Table 1 equals the WBSID extracted from the listbox. Can you do this while looping through the data in the with clause or is there another way. I would like to save the step of creating a new table and then joining the tables together. Any suggestions?



    *** CODE STARTS HERE ***

    ' INSERT SELECTED DATA FROM LISTBOX INTO TABLE.TABLE - WBS LIST BOX SELECT

    Dim ctl As ListBox
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim VarItem As Variant
    Dim WBSID As String, temp As String
    Dim stDocName As String

    ' Set ctl to the name of the Multi Select Listbox
    Set ctl = Me!SELECTWBS

    'Exit Subroutine if the User does not select anything in the listbox
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more WBSIDs before pressing the Create Report Button"
    Exit Sub
    End If

    ' Start process to populate table with items selected.
    ' Table - WBS LIST BOX SELECT holds data Selected in the Listbox

    ' Delete all existing items in the table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "DELETE-TABLE_WBS_LIST_BOX_SELECT"
    DoCmd.SetWarnings True

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordSet("SELECT [TABLE - WBS LIST BOX SELECT].WBSID FROM [TABLE - WBS LIST BOX SELECT]")

    'Loop through the items selected in the listbox

    For Each VarItem In ctl.ItemsSelected
    With rst
    .AddNew
    !WBSID = ctl.Column(0, VarItem)
    .Update
    End With

    Next VarItem

    rst.Close
    Set ctl = Nothing
    'Set dbs = Nothing

    '' END INSERT LIST BOX DATA INTO TABLE
    Regards,

    Gary
    (It's been a while!)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Working with Recordsets (A2K SR1)

    Hi Gary,
    If I understand you correctly, you should be able to achieve what you're after by creating a string listing all the WBSIDs selected and then using this in an SQL like:
    "SELECT * FROM TABLE1 WHERE WBSID IN (" & strIDList & ")"
    if that makes sense?
    PS Sorry, I haven't had a chance to look at your form printing issue again yet - will hopefully get to do so later.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with Recordsets (A2K SR1)

    Thanks Rory,

    I like this idea <img src=/S/clever.gif border=0 alt=clever width=15 height=15> ... and don't know why I didn't think of it, however, now I am confused <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> (which seems to be happening a lot lately.....)

    Two questions,
    1. After creating the variable strIDList, how would it be created to create the string for the in clause, i.e.,
    - first instance is strIDList = " ' " & [wbsid]& " ' "
    - additional instances strIDList = " , " & strIDList & " ' "&[wbsid]&" ' "

    2. Where would you put this? I do not see how you would put it in the with clause. Perhaps this is where I am stuck in understanding what the recordset is doing. In fact, some other code I have, is doing the same thing (works great) and the reports generated only report on the extracted recordset, however, I have to admit, I can't figure out how the report knows to extract only the recordset data. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    As to the printing issue, thanks for the help when you can look at it.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Recordsets (A2K SR1)

    I just wrote this out for the project I'm working on....

    Public Function SelectedItems(ctl As Control) As String
    'Returns an IN string for selected items in a listbox
    Dim strActivities As String
    Dim varItem As Variant

    strActivities = ""
    If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
    strActivities = strActivities & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    End If
    SelectedItems = Left$(strActivities, Len(strActivities) - 1)
    End Function

    Hope this helps,

    Cecilia :-)

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with Recordsets (A2K SR1)

    Thanks Cecilia.

    This gives me some other ideas.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Working with Recordsets (A2K SR1)

    Hi Gary,
    Hopefully Cecilia has got you going on building the IN clause - you don't need the recordset anymore if you use this method; you just build the string from the selecteditems values.
    Re the screen printing, I'm attaching a text file with a revision of some of your code (it has instructions for where each bit goes) and it seems to work ok for me.
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with Recordsets (A2K SR1)

    Rory,

    Thanks. ... at a quick glance, it is correct to state the the function is getting the open arguments for the form?

    I will put this into the code and see what happens.

    As to the other item, I still need to get a better understanding of the recordsets.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Working with Recordsets (A2K SR1)

    Gary,
    Not entirely, at least not in the sense of OpenArgs. The form has already been opened by the time the function is called. The function merely sets the values of the two comboboxes, refreshes the form and then pauses to allow screen redraw. I simply separated it out into a separate function as your original code was doing the same thing repeatedly and it's easier to maintain if there's only one location you have to change anything!
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with Recordsets (A2K SR1)

    ... Interesting. Then I will have to check to see if the subform on the graphics form refreshes. The problem that I am encountering is with the form MS Graphics's screen shots passed to word. The data on the main form changes with each paste to word, however, the graph in the subform is the same graph over and over. I will try the code you provided and see what happens.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with Recordsets (A2K SR1)

    Rory,

    Works great. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    I can't say I understand exactly why this works and the other code doesn't, however, I will keep pressing on.
    Off to the next request..... <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    Regards,

    Gary
    (It's been a while!)

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with Recordsets (A2K SR1)

    Hi Cecilia,

    .. a dumb question for you. I read your function and understand exactly how it works, but, can't figure out how to launch it as a function. Are you running this off of an on change event of a listbox or perhaps a command button on a form.

    In code, If I want to use this function against a listbox named SelectWBS to create the "In" string, is this how you would do it (I hope not because I keep getting errors....)

    ... Code from a command button, on click event

    Dim ctl As ListBox

    Set ctl = Me!SELECTWBS

    SelectedItems (ctl) '''' I believe this is where I am messing this up.....

    Help <img src=/S/help.gif border=0 alt=help width=23 height=15> - I am having one of these days.....
    Regards,

    Gary
    (It's been a while!)

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Recordsets (A2K SR1)

    This is the part where my brain cells stop working, because I'm a visual type person....

    Are you trying to populate a listbox? Can you run through the steps that you want the user to make?

    For instance, if I want to populate a listbox after the user selects something in a combo, I would (probably) use the afterupdate event of the combo to set the recordsource of the listbox and refresh the form/listbox. The function returns the IN part, so you'd have to modify it to have a whole select statement that would fit into the recordsource. (ie. listbox2.recordsource="Select stuff where criteria in (" & SelectedItems(me.listbox1) & ")"

    Although now I'm getting confused as to why we're bouncing from control to control....wouldn't that make your users a bit crazy?

    Maybe someone else can jump in here?

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with Recordsets (A2K SR1)

    Cecilia,

    I am actually doing just the reverse of what you said.

    I have a multiselect listbox. After the user selects one or many items in the listbox, the user presses a command button that runs a query which generates the data for a report. I need the IN portion of the query to be set equal to that selected in the listbox.

    I have done this before by creating a table that stores the results of the selections made and then joining to the table, however, am trying to come up with a simpler process. (This is where your idea came in....)

    Any ideas, wisdom, humor, or other suggestions on getting this to work.

    Thanks for your help.

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    --- this is where this all started by trying to figure out how to do this with the current recordset....
    Regards,

    Gary
    (It's been a while!)

  14. #14
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with Recordsets (A2K SR1)

    The purpose of the function is to return the list of items selected in a listbox. In order to do something with that string you have to give the function something to return the string to - in this case a string variable.

    strInList = SelectedItems (ctl)

    Now you have the list string and can manipulate it or use it in your SQL select string for the basis of your report.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Working with Recordsets (A2K SR1)

    Are you planning to actually change the SQL property of the query or just apply a filter to it or what? Since the SQL is a string, you can modify it to include a Where condition that uses the IN operator, along with the string of values retrieved from your listbox. *Then* run the query. Of course, this is going to work only in an MDB where the user has permissions to the querydef or you arrange your code to provide them those permissions temporarily.
    Charlotte

Page 1 of 2 12 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
  •