list boxes (office 2003)

Topic

New Reply

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

Reply | Quote

Replies

post 282035 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 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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.1N2101” 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 (in a different context).

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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 for instructions.

Reply | Quote

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

Reply | Quote

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”)

Reply | Quote

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

Reply | Quote

A good way to get an idea for the SQL you need to create in code is to create a query in design mode, test that it does what you want, then select View | SQL. You cannot copy this straight into the Visual Basic Editor, because the SQL will be spread over several lines, and because you have to replace double quotes around strings with single quotes (or double double quotes), but you’ll get the basic idea.

Reply | Quote