Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MultiSelect - Listbox (Access 2000 & 97)

    I need some help with the Listbox MultiSelect feature. I'm creating a database with Access 2000 for our company marketing mailshot information. (Although using Access 2000 at home, I have Access 97 at work so have to convert the database each time!) I need to show a list of postcodes, per letter, per branch office. The postcodes, letter information and general branch information are all stored in separate tables. In my Letters form, I have a listbox from which I would like to be able to multiselect postcodes, then store this information in [Postcodes Mailed] in the Branches Table. I have a form for Branches, containing a subform showing each letter sent out for that branch office with the postcodes used. I want the postcodes to be displayed with a comma separating each one, BR2, BR3, etc. I have found various bits of code in your column that would do the job but I don't know where to "store" them or run them. Would it be done as an event procedure, from On Click from the listbox control properties sheet, or what?? Please could you tell me, in layman's terms, what I need to do!

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

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hello Sue,

    You will need to place a command button on the form. next to the list box, to fill the text box bound to the PostCodes Mailed field. Let's say that you name this command button cmdSet, and that your list box is named lstPostCodes.

    The code to fill the text box will be in the On Click event procedure of the command button.

    <UL><LI>Open the form in design view.
    <LI>Put a command button on the form.
    <LI>Set its Caption property to "Set PostCodes" or whatever you prefer.
    <LI>Set its Name property to "cmdSet".
    <LI>In the Events tab, click in the On Click event.
    <LI>Select "Event Procedure" from the dropdown list.
    <LI>Click the builder button (the three dots ... to the right.)
    <LI>Enter or copy the following code, substituting the names used on your form:

    Private Sub cmdSet_Click()
    Dim strPC As String
    Dim i As Integer
    ' Loop through list items
    For i = 0 To Me.lstPostCodes.ListCount - 1
    ' Check if item is selected
    If Me.lstPostCodes.Selected(i) = True Then
    ' Append item to string
    strPC = strPC & ", " & Me.lstPostCodes.ItemData(i)
    End If
    Next i
    ' Was anything selected?
    If strPC <> "" Then
    ' Get rid of first ", "
    strPC = Mid(strPC, 3)
    ' Set PostCodes text box
    Me.[PostCodes Mailed] = strPC
    Else
    ' Optional: clear text box if nothing is selected
    Me.[PostCodes Mailed] = ""
    End If
    End Sub

    <LI>(If you're in Access 2000, switch back to Access) Save the form.[/list]I repeat, you must substitute the name of your list box and text box.

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Excellent, thank you very much. This is just what I needed! I will try immediately.

    Regards,

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

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hi Hans
    I believe that your statement:
    strPC = strPC & ", " & Me.lstPostCodes.ItemData(i)
    could be changed to:
    strPC = (strPC + ", ") & Me.lstPostCodes.ItemData(i)

    However you must define strPC as a variant. And you don't need any cleanup code to strip away the comma.
    This is not my idea, i gleaned this fom another site.
    Neat eh.

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

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hi Pat,

    Yes, that should work too. I use that technique often in calculated fields in queries, since concatenating with + instead of & avoids the use of IIf and IsNull. For some reason, I tend to avoid it in VBA, but that is probably an irrational preference. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Anyway, thanks. It gives the poster another option.

  6. #6
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hi Guys

    Update on this - have tried your solution on Access 2000 at home (works fine), then converted to Access 97 and tried at work. On hitting the command button to Set Postcodes, I get the following error message:

    "Compile error
    Can't find project or library"

    It then highlights the word "mid" in the following line

    strPC = Mid(strPC, 3)

    I think this is something to do with missing references/libraries (??) but don't really know what to look for when the references list comes up!

    Any clues would be greatly appreciated.

    Many thanks and regards,

    Sue

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

    Re: MultiSelect - Listbox (Access 2000 & 97)

    In the Visual Basic Editor, select Tools | References...
    Look for items in the list that start with MISSING. Note which ones they are, then clear the corresponding check marks. Click OK.
    If all goes well now, you're in luck. If not, you may have to find and check a reference that in the list that is equivalent to one you unchecked.

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

    Re: MultiSelect - Listbox (Access 2000 & 97)

    If you don't have Office 2000 or VB6 installed at work, you may not have the ADO libraries on the machine. If your 2000 database had an ADO reference set and the library isn't there on the office machine, that would cause a references problem.
    Charlotte

  9. #9
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hi Hans

    Many thanks to you and Charlotte. I tried your suggestion at work on Access 97 and all is now sorted!

    Thanks again.

    Sue

  10. #10
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hi All

    Back on the Listbox problem again!

    1. You gave me the solution to concatenate the items multi-selected from my listbox with a comma inbetween each selection, e.g. the string (stored in [Postcodes Mailed] on the form is "BR1, BR2" and this works very well.
    2. However, it appears I now need to also save the multiple items selected to a separate table, e.g. Letter 31 (sent to postcode) BR1 is one record, Letter 31 (sent to postcode) BR2 is another record of my Postcodes Per Letter table. I've been trying to use the Items Selected feature, without success.

    Is it possible to do both items 1. and 2. above at the same time from the same listbox multiple selections? If so, please would you give me the correct code to use and whether it should be an on exit, on click, etc., procedure. The name of the listbox is "Listbox73", field names of the Postcodes Per Letter table will be something like [Letter Number] and [Postcodes Marketed].

    Any help will be greatly appreciated, (again!!!!)
    Best Regards,
    Sue

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

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hi Sue,

    The code below uses DAO; in Access 97, this is the default data model, but in Access 2000, you'll probably need to set a reference to it:

    Activate the Visual Basic Editor (Alt+F11)
    Select Tools | References...
    Locate the Microsoft DAO 3.6 Object Library and check the corresponding box.
    Click OK.

    I have used the same procedure as in a previous reply. You will need to substitute the appropriate names (such as Listbox73 - what a horrible non-descriptive name!)

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub cmdSet_Click()
    Dim strPC As String
    Dim i As Integer
    ' New declarations
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    ' Set references to database and to table
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("[Postcodes Per Letter]", dbOpenDynamic)

    ' Loop through list items
    For i = 0 To Me.lstPostCodes.ListCount - 1
    ' Check if item is selected
    If Me.lstPostCodes.Selected(i) = True Then
    ' Append item to string
    strPC = strPC & ", " & Me.lstPostCodes.ItemData(i)

    ' Append record to table and set values
    rst.AddNew
    rst![Letter Number] = Me.[Letter Number]
    rst![PostCodes Marketed] = Me.lstPostCodes.ItemData(i)
    rst.Update
    End If
    Next i

    ' Was anything selected?
    If strPC <> "" Then
    ' Get rid of first ", "
    strPC = Mid(strPC, 3)
    ' Set PostCodes text box
    Me.[PostCodes Mailed] = strPC
    Else
    ' Optional: clear text box if nothing is selected
    Me.[PostCodes Mailed] = ""
    End If

    ExitHandler:
    ' Cleaning up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user of error
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  12. #12
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    Hi Hans

    Many thanks for your very prompt reply. I'll take this into work today and try out.

    Best regards,
    Sue

  13. #13
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    HiHans

    Well, I've tried the code on my text db here at home and the Access 97 version at work and on both dbs, I get the message "Invalid argument" as soon as I exit the listbox. The only amendments I made were to change the name of the listbox to my version, List73, and the name of the [Letter Number] field to that shown on the form but as I deleted the whole line referring to the [Letter Number] to see if it would cure the problem (No!), that can't be the problem.

    Any clues, please?

    Regards,
    Sue

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MultiSelect - Listbox (Access 2000 & 97)

    What code have you got in the AfterUpdate event of the ListBox?

    What have you now called List73?

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

    Re: MultiSelect - Listbox (Access 2000 & 97)

    When the error occurs, you see a message box describing the error and three buttons. If you click the "Debug" button, you'll be taken to the code with the line causing the problem highlighted in yellow. What is the code on the highlighted line?

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
  •