Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Listbox (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox (A2k)

    With the PNG as below, I have an transparent button by the RHF Wing, when clicked, I show a msgbox for this demonstration:

    Private Sub cmdRHFWing_Click()
    MsgBox "O/S/F Wing"
    'Me.LstGraphics.AddItem "OFW",
    End Sub

    As you see with the commented out line, I am trying to put that value in the list to the right ( Is this possible ?)
    I'm not sure if the .AddItem is the correct thing to use.

    Once the list is populated, from there can I populate a subforms particular column ?
    Attached Images Attached Images

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

    Re: Listbox (A2k)

    The AddItem method for list box and combo box controls was introduced in Access 2002; it doesn't exist in Access 2000. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    If you explain what you want to accomplish, we may be able to find an alternative approach. (I don't understand what you mean by "populate a subforms particular column".)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox (A2k)

    Hans
    I use Office XP but with the databases in A2000 format as default.
    The .AddItem part of the library does exist in my VBA version so I thought this would be the item to select.

    Basically ( I'm jumping thru hoops again I know !! ) I want to overlay transparent command buttons on the car image, and once that part of the car is clicked, lets say on the roof, the code inserts my text ie "Roof" into the list on the right.
    Once I have populated the list with all parts of the car as I need, A command button below the list will transfer all it's contents into a subform, either via an insert into SQL or direct to the subform.
    Attached Images Attached Images

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

    Re: Listbox (A2k)

    If all your users have Office XP, you can use AddItem (even if the database is in Access 2000 format), but if you have users with Office 2000, the code will fail.

    If you want to use AddItem, the Row Source Type property of the list box must be set to Value List; it won't work if it is set to Table/Query. The code you had commented out should work then. You would have to check that the user doesn't accidentally click the roof 37 times.

    The code behind the command button should loop through the items of the list box:

    Private Sub cmdAddToSubform_Click()
    Dim i As Long
    For i = 0 To Me.lstGraphics.ListCount - 1
    ' Code to insert item into subform goes here
    ' Use Me.lstGraphics.ItemData(i) to get i-th item
    Next i
    End Sub

    Alternatively, you might insert a record into the subform directly when the user clicks a transparent button.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox (A2k)

    Hans
    I'm not sure if I understand the following:

    ' Code to insert item into subform goes here
    ' Use Me.lstGraphics.ItemData(i) to get i-th item

    ' Code to insert item into subform goes here
    Would this be with an SQL statement ? (I presume so.)

    ' Use Me.lstGraphics.ItemData(i) to get i-th item
    ?????

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

    Re: Listbox (A2k)

    Dave,

    Executing an SQL statement is probably the way to go. I didn't work out the details since I don't know the name of the table and of the fields to be populated. The general idea is
    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim i As Long
    Dim strSQL As String

    For i = 0 To Me.lstGraphics.ListCount - 1
    strSQL = "INSERT INTO tblSomething ( FieldName ) VALUES (" & _
    Chr(34) & Me.lstGraphics.ItemData(i) & Chr(34) & ")"
    CurrentDb.Execute strSQL
    Next i

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

    This would append records to a table named tblSomething, and only the field named FieldName would be populated; you will probably want to populate more fields. So the above is not meant as complete, finished code, but only to give you an idea of how it could be done.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox (A2k)

    Thanks Hans
    Thats the clarification I was looking for.
    It's enough to get me started.

    Have a good day .

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox (A2k)

    Hans
    If using .AddItem, Can the item be put into a particular column ?

    The help files only seem to refer to the end or start of the list ie:

    Me.LstWhatever.AddItem "Text" 2

    My list has 3 columns so I want to .AddItem to Column(2) ?

    I even tried LstWhatever.Column(2).Additem "text "

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

    Re: Listbox (A2k)

    Dave,

    The Index argument of AddItem specifies the row where the new item must be added (default is at the end.)

    In a multi-column list box, you must either specify a single value, in which case it will be put in the first column, or you must specify values for all columns (empty if necessary). For example:<pre>Me.lstWhatever.AddItem "John;Paul;George"
    Me.lstWhatever.AddItem ";Mick;Keith"
    Me.lstWhatever.AddItem ";Dave;"</pre>

    The items are separated by semi-colons.

    BTW, Why do you want to populate only the second column of the list box? Why have multiple columns if you do that?

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 14-Oct-03 16:00. )</P>Hans
    Very good question:

    BTW, Why do you want to populate only the second column of the list box? Why have multiple columns if you do that?

    I needed to also add the Estimate & Supp to the list, so I could build my SQL to insert all three items.
    It seems I didn't need to do so as you'll probably see with the following code:

    <pre>Dim i As Long
    Dim strSQL As String
    For i = 0 To Me.LstGraphics.ListCount - 1
    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Supp, code) VALUES (" & _
    Forms!frmEstGraphic!txtEstimateNo & ", " & Forms!frmEstGraphic!txtSupp & _
    "," & Chr(34) & Me.LstGraphics.ItemData(i) & Chr(34) & ")"

    CurrentDb.Execute strSQL
    Next i</pre>


    This works fine, thanks again.

    BTW
    I see John,Paul,George from the Beatles
    I see Mick,Keith from the stones !
    Who is Dave ??

    Me.lstWhatever.AddItem "John;Paul;George"
    Me.lstWhatever.AddItem ";Mick;Keith"
    Me.lstWhatever.AddItem ";Dave;"
    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

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

    Re: Listbox (A2k)

    >> Who is Dave?

    What? Don't you remember your own name?

    Actually, I was thinking of Dave Davies of The Kinks. All those groups provided the sound track of my teenage years in the sixties.

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox (A2k)

    Oh
    I thought you might have been refering to "The Thin White Duke" ??

    BTW

    This adds to the list quite nicely to two columns.
    Private Sub cmdRHFWing_Click()
    Me.LstGraphics.AddItem "OFW;O/S/F Wing"
    End Sub

    This:

    <pre>Private Sub CmdTransfer_Click()
    Dim i As Long
    Dim strSQL As String
    For i = 0 To Me.LstGraphics.ListCount - 1
    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Supp, code,item) VALUES (" & _
    Forms!frmEstGraphic!txtEstimateNo & ", " & Forms!frmEstGraphic!txtSupp & _
    "," & Chr(34) & Me.LstGraphics.ItemData(i) & Chr(34) & ")"

    CurrentDb.Execute strSQL
    Next i
    End Sub</pre>


    Doesnt transfer Item ??

    Do I need to reference as with (i) ??

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

    Re: Listbox (A2k)

    What should be transferred to the table in your example? "OFW" or "O/S/F Wing"?

    I thought that mr. Bowie was always referred to as David, I've never heard him called Dave.

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox (A2k)

    Hans
    What started as one item to transfer has now developed to two.
    The reason, the item transfered is only a code which selected from the subforms cboCode after_update, Updates item.
    The code as it stands at the moment only tranfers "code" so the cboCode is not updated unless the user selects from the combo. By transfering item also ie: .Additem"Code;item", the combo doesn't require selection.

    BTW:

    Dave,David,Ziggy,David Robert Jones, I can't wait for November @ Manchester, cos I'll be there !!"

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

    Re: Listbox (A2k)

    David,

    You'll have to specify the two columns separately. To do this, use Column instead of ItemData. Numbering is zero-based, so the first column is #0, the second is #1.<pre>strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Supp, code,item) VALUES (" & _
    Forms!frmEstGraphic!txtEstimateNo & ", " & Forms!frmEstGraphic!txtSupp & _
    "," & Chr(34) & Me.LstGraphics.Column(0, i) & Chr(34) & "," & _
    Chr(34) & Me.LstGraphics.Column(1, i) & Chr(34) & ")"</pre>


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
  •