Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    list boxes (office 2003)

    Hi all, I am having problems getting data to show on 2 seperate list boxes on a single form. List1 on the left side of the form lists available records. In the on Click Event, I want to remove that record from List1 and make it appear in List2 on the right. When the record in List1 is clicked, I am setting a check box = to true in the datasource and requering the form to have the List2 on the right look for that check and display the record. The problem is that when List1 is clicked, it is the first record in the datasource that gets the check no matter which record I click on in the list box. Do I need to do a Recordsetclone type of setup and the requery. The bound column in the list box is a unique field which is indexed. Am I on the right track or is there another method which is easier?

    Thanks
    Kevin
    Kevin

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

    Re: list boxes (office 2003)

    <post#=282035>post 282035</post#> has a demo attached with single-select list boxes. Moving items is done by setting/clearing a Yes/No field in the underlying table.

    <post#=284472>post 284472</post#> has a demo attached with multi-select list boxes. Moving items is done by running append and delete queries on the underlying tables (one for each list box)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: list boxes (office 2003)

    Thanks Hans
    I did do the search and found that post earlier. I tried adopting it to fit my situation and am running into a syntax problem. I am trying the following code attached to an add button:

    Dim ctlList As Control
    Dim ctlList2 As Control
    Dim varItem As Variant
    Dim mysql As String

    ' Return Control object variable pointing to list boxes.
    Set ctlList = Me.vAvailPricings
    Set ctlList2 = Me.vPricingsSelected

    ' Enumerate through selected items.
    For Each varItem In ctlList.ItemsSelected
    ' Add to selected list.
    mysql = "INSERT INTO tblPricingsSelected (Pricing_ID) VALUE (" & ctlList.ItemData(varItem) & ");"
    DoCmd.SetWarnings False
    MsgBox mysql
    DoCmd.RunSQL mysql
    DoCmd.SetWarnings True

    Next varItem
    ctlList.Requery
    ctlList2.Requery

    I get a run time 3134 error
    Syntax error in insert into statement.

    I think it is because the value of ctlList.ItemData(varItem) is a text value but am not usre because everything I have tried (double quotes, etc.) has not worked.

    Any Idea?

    Thanks
    Kevin
    Kevin

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

    Re: list boxes (office 2003)

    You must use VALUES instead of VALUE, even if you want to set only one value.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: list boxes (office 2003)

    Thanks Hans, it worked just fine. If you have time for one more, I am having trouble getting an update query to run wher it is taking the criteria from a text box on a form. The text box containns the string...

    "N0117.018.1N2100"Or"N0117.018N2100"Or"N0117.018.1 N2101" quotes included.

    the text box is on [Forms]![Open Pricing by Gov_ID Number]![vStringexp] with vStringexp as the name.

    When I use the syntax [Forms]![Open Pricing by Gov_ID Number]![vStringexp] in the criteria to find the recordds to update it will not execute. However, if I copy and paste that same string directly from the text box to the query criteria, it runs just fine. What am I missing here?

    Thanks
    Kevin
    Kevin

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

    Re: list boxes (office 2003)

    Access interprets the value of [Forms]![Open Pricing by Gov_ID Number]![vStringexp] as a literal string, it doesn't try to evaluate the Or's. If you paste the string into the Criteria line of the query, Access does evaluate the Or's.

    You may be able to use a variation of the technique described in ACC2000: How to Create a Parameter In() Statement.

    If the text box is filled from a list box there may be other methods.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: list boxes (office 2003)

    WOW that was a fast response. Thanks. The string is generated using a list box and actually is related to the questions that started this string. I am trying to establish a second ID for the records in the list box that will relate the list box items. For example, Each item in the list box has its own unique ID, but now I want to relate them using a secondary ID. My intent was to run an update query that assigns that ID. So I was able to identify the records that require the update and put that in the text box on the form. The ID I want to assign works great if the query runs, but obviously I am falling short on passing that parameter to the update query. I will take a look at the sample you pointed out to me and try to incorporate that. In the mean time, what were the other methods you refered to if the text box was filled from a list box. In this case it came from a multi selct list box set to extended.

    Hans Thanks for the help
    Kevin
    Kevin

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

    Re: list boxes (office 2003)

    You can loop through the selected items of a list box, and perform an action for each of them. Or you can assemble an SQL string in code.

    You'll find an example in <post:=402,894>post 402,894</post:> (in a different context).

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: list boxes (office 2003)

    Thanks Hans,
    I can follow the thought process in your example, my only uncertanty is passing the result to the query.

    Dim strWhere As String
    Dim i As Long
    ' Loop through list box items
    For i = 0 To Me.lstMyListBox.ListCount - 1
    If Me.lstMyListBox.Selected(i) = True Then
    strWhere = strWhere & "," & Me.lstMyListBox.ItemData(i)
    End If
    Next i
    If strWhere = "" Then
    ' Nothing selected - get out now!
    Exit Sub
    Else
    ' Remove leading comma
    strWhere = Mid(strWhere, 2)
    ' construct WhereCondition
    strWhere = "FieldName In (" & strWhere & ")"
    ' Open report
    DoCmd.OpenReport "rpt Estimating - PrintOut", acViewPreview, , strWhere
    End If


    I am thinking that the last line would open the query instead of the report listed, but does strWhere pass to the query as an argument of calling the query similar to how the report in the example works?

    Thanks
    Kevin
    Kevin

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

    Re: list boxes (office 2003)

    No, you'd have to create the entire SQL statement in code, then use DoCmd.RunSQL or CurrentDb.Execute to execute the SQL statement.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: list boxes (office 2003)

    Hans, making headway but still falling short. I managed to put the sql statement together as below:

    mySQL = "UPDATE Pricing INNER JOIN [Pricing Detail EB] ON Pricing.Pricing_ID = [Pricing Detail EB].Pricing_ID " & _
    "SET Pricing.Modified_Pricing_ID = 'ModID' & vlastname & vcode , " & _
    "Pricing Detail EB.Modified_Pricing_ID = 'ModID '& vlastname & vcode " & _
    "WHERE Pricing.Pricing_ID=strholder;"

    I am getting a message saying syntax error in update statement.
    I tried removing brackets but cannot tell where I fell off the truck.
    I have looked at many examples but now I'm not sure if the error is truly in the Update statement or if the whole SQL has the syntax error. ANy chance you can take a quick look?

    Thanks
    Kevin


    10th nickel: I have reduced the sql to something very simple see below:

    mySQL = "UPDATE Pricing " & _
    "SET Pricing.Modified_Pricing_ID = 'ModifiedID' " & _
    "WHERE Pricing.Pricing_ID= '" & strHolder & "'"

    it runs and executes but does not update any records. Am I back to the variable strHolder as the culprit?
    Kevin

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

    Re: list boxes (office 2003)

    1) You should update only one table at a time.
    2) Since the table Pricing Detail EB has spaces in its name, you should enclose it in square brackets each time you use it.
    3) You have placed variables within the quoted string, therefore Access will not use the values of the variables but the names.
    4) If strHolder is a series of items from the list box, you cannot use =.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: list boxes (office 2003)

    Hans, I got it to run and update the correct field with:

    mySQL = "UPDATE Pricing " & _
    "SET Pricing.Modified_Pricing_ID = 'ModifiedID' " & _
    "WHERE Pricing.Pricing_ID=" & strHolder

    However, it updated all of the records (6) of them vice the (3) identified in the strHolder collection.
    strHolder is passing

    ..."N0117.018.1N2100" Or "N0117.018N2100" Or "N0117.018.1N2101" to the sql.

    I am close I think, I just am not comprehending what I am doing wrong with ...
    ... "WHERE Pricing.Pricing_ID=" & strHolder


    Thanks
    Kevin
    Kevin

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

    Re: list boxes (office 2003)

    The SQL string should either look like

    ... WHERE Pricing.Pricing_ID="N0117.018.1N2100" Or Pricing.Pricing_ID="N0117.018N2100" Or Pricing.Pricing_ID="N0117.018.1N2101"

    or

    ... WHERE Pricing.Pricing_ID In ("N0117.018.1N2100", "N0117.018N2100", "N0117.018.1N2101")

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: list boxes (office 2003)

    Hans, Thank You, working now !!!!!!!!!! I was not thinking that the SQL would look like that. I was thinking that the strHolder variable should just pass the same thing that you would hand type into the query itself. Once I passed that mental block I was able see more clearly. Thank You for all of your help.

    Kevin
    Kevin

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
  •