ItemsSelected Help 3

[rdgskier8]

I'm looking to pull a value out of a listbox that the user selects (no multi-select). It will then use that value along with another to delete an entry in a table. It seems ItemsSelect and then ItemData would be the way to go about this, but I'm having trouble getting it to work. Here is a sample of my code:

Dim RDG As Variant

RDG = Me!lst2.ItemsSelected.Item

strSELECT = "DELETE Nz([qryFailure].[IDstation]) AS Expr1 "
strFROM = "FROM Intermediate "
strWHERE = "WHERE Station.IDstation=" & frmModify.boxStation & " AND Intermediate.IDfailure=" & lst2.ItemData(RDG) & ";"

strSQL = strSELECT & strFROM & strWHERE

BuildSQLString = True

CurrentDb.QueryDefs("qryModifyDEL").SQL = strSQL

Form_frmModify.Refresh


Any help would be much apprecated!! Thanks in advance.

 

[Eupher]

Since it's not a multi-select listbox, you can refer directly to the value:

strWHERE = "WHERE Station.IDstation=" & frmModify.boxStation & " AND Intermediate.IDfailure=" & [COLOR=blue][b]Me!lst2[/b][/color] & ";"

This assumes the value of lst2 is numeric; if it's a string, include single quotes; if it's a date, the pound sign.

You can, of course, use the ItemData property of the ItemsSelected collection (this works for single- or multi-select listboxes):

Dim varItem As Variant

For Each varItem in Me!lst2.ItemsSelected
    Debug.Print Me!lst2.ItemData(varItem)
Next varItem

HTH,

Ken S.

 

[rdgskier8]

Hmm I went with the first method and it's giving me a runtime error 424, object required error. This is when I click the remove button and it highlights the strWHERE statement. Any suggestions?

Thanks for your help, Ken.

 

[PHV]

What is frmModify ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

frmModify is the form everything resides on (Me).

I think I may have found the problem... my bound lists aren't displaying the correct values when I use the Stop command. They're giving me either Null or the string instead of returning the number like I want them to.

The query has ID as the first column and Name as the second column... what would my ColumnCount, ColumnWidth, and BoundColumn be? I can't seem to get it to work.

Thanks.

 

[earthandfire]

ColumnCount = 2
ColumnWidth = 0;1" or metric equivalent
BoundColumn = 1

Hope this helps

 

[rdgskier8]

Hmm I tried that, earth, but it's coming up with no values in the list... it's like they are there (I can click on them and scroll down - so there's the right number), but it doesn't show any of the names. When I use Stop and check the value, it's the string (column 2). This is really bizarre. Any thoughts?

Thanks for your help.

 

[PHV]

What is your ACTUAL code ?
Is lst2 a MultiSelect ListBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

I just noticed the query has three columns... would this make a difference? I tried ColumnCount=3, Width=0;1;0, and Bound=1, but it still didn't work.

 

[rdgskier8]

lst1 and lst2 are not multi-select.

Here's my code:

Private Sub butInclude_Click()
'This button adds data to the Intermediate table

'Check to make sure data is entered
If lst1.ListIndex = -1 Then
MsgBox "Please select a failure."
Exit Sub
End If
Stop
'Open Hidden Form
DoCmd.OpenForm "frmModifyHIDDEN", , , , acFormAdd, acHidden

'Add data to appropriate fields
Form_frmModifyHIDDEN.txtIDfailure = Me!lst1
Form_frmModifyHIDDEN.txtIDstation = boxStation

'Close and save hidden form
DoCmd.Close acForm, "frmModifyHIDDEN", acSaveYes

'Refreshes the page to show the updated query
Form_frmModify.Refresh

End Sub

 

[earthandfire]

The bound column should be the one with the ID field, since presumably thats the field tied into your underlying table/query.

 

[rdgskier8]

That's why I'm so confused... I have the bound column as 1 (the ID field), and it's still displaying the values for column 2 (the name field). I'll keep tinkering with it.

 

[rdgskier8]

Aha! My RowSource was incorrect. It was a SELECT statment instead of just referring to the query.

Thanks for your help, everyone! Hopefully I can get this to work now. =)

 

[earthandfire]

What do you actually want the combobox to show?

The Value property will always be the bound column,but you can determine what is shown by adjusting the ColumnWidth - setting those that you do not want to show to 0.

Hope this helps.

 

[rdgskier8]

The listbox is showing the right values and giving me the right bound number now - I'm also able to add the values to the Intermediate table, but I'm having trouble deleting entries. I'm getting the right values but I think my SQL is wrong. The code is listed above but I'll list it again:

Private Sub butExclude_Click()
'This button removes data from the Intermediate table

'Ryan Giltner
'May 20, 2005

'Declaration of Variables
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

'Check to make sure data is selected
If lst2.ListIndex = -1 Then
MsgBox "Please select a failure."
Exit Sub
End If
Stop
'Selecting fields for query from tables
strSELECT = "DELETE Nz([qryFailure].[IDstation]) AS Expr1 "
'Relationships and locations
strFROM = "FROM Intermediate "
'Criteria for deleted data
strWHERE = "WHERE Station.IDstation=" & Me!boxStation & " AND Intermediate.IDfailure=" & Me!lst2 & ";"

'Adds up the SQL fields into one long SQL
strSQL = strSELECT & strFROM & strWHERE

BuildSQLString = True

'Impliments the SQL into qryFailure
CurrentDb.QueryDefs("qryModifyDEL").SQL = strSQL

'Refreshes the page to show the updated query
Form_frmModify.Refresh


I'm not getting any errors, but it's just not deleting anything. What do you guys think? I think it's something wrong with my SELECT statement.

Thanks again for everything.

 

[rdgskier8]

I tried...

strSELECT = "DELETE "
strFROM = "FROM Intermediate "
strWHERE = "WHERE Intermediate.IDstation=" & Me!boxStation & " AND Intermediate.IDfailure=" & Me!lst2 & ";"

...but it's still not working. Any thoughts?

 

[rdgskier8]

Ahh I got it... forgot to open (run) the delete query. Thanks all!!