I Still Need Help - Please!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have made a little progress, but it's still not working for me. I've set the recordsource of my subform to a query. The query has the listbox control as the criteria, but it's still not working. I just want to be able to select items from the listbox and have text boxes show on the subform accordingly. What else do I need to do?

----------------------------
From Previous Post....

Hi Everyone,

My head is clouded and I can't seem to think straight right now. It's been a long day. :)

I'm trying to do something simple. I want to use listbox values to add new records to a table and have only those records as a source for the subform. I have created a recordset and the records are added to the table, but I can't figure out how to set the subform's record source so that records are shown corresponding to the selected listbox values. Am I making sense? Any help would be greatly appreciated. Thanks!

Snippet:

Set DB = CurrentDb
Set RecSet = DB.OpenRecordset("dbo_ProductionData", dbOpenDynaset, dbSeeChanges)

For Each Valu In Me.lbxFunctions.ItemsSelected
With RecSet
.AddNew
!CUser = "testUser"
.Fields("FunctionsID") = Me.lbxFunctions.ItemData(Valu)
.Update
End With
Next Valu

Now how do I get this info to show on my subform?



Expand AllCollapse All
 
Setting the listbox control as the criteria won't work if it is a
multiselect list box because there is no "value" to retrieve. It appears
that in your code you are trying to go through the selected items in the
list box and add them to a temporary table. If that is the case, add this
table to the subform's query and link it to the field that you currently
have the listbox as criteria. This will limit the query to returning items
from the main table that have items selected in the listbox temporary table.
Don't forget to clear the temporary table in between uses.

Another possibility would be to rewrite the SQL of the query. Concatenate
the listbox selections together using commas between them then add them to
the WHERE clause of the query using an IN statement.

For Each Valu In Me.lbxFunctions.ItemsSelected
strCriteria = Valu & ","
Next Valu
strCriteria = Left(strCriteria, Len(strCriteria) -1) 'remove the trailing
comma
CurrentDb.QueryDefs("MyQuery").SQL = "SELECT .... WHERE MyField In (" &
strCriteria & ");"

If the values are text instead of numbers:
strCriteria = """" & Valu & ""","

This will append the quotes into the resulting string.

--
Wayne Morgan
MS Access MVP


ZZK said:
I have made a little progress, but it's still not working for me. I've
Click to expand...
set the recordsource of my subform to a query. The query has the listbox
control as the criteria, but it's still not working. I just want to be able
to select items from the listbox and have text boxes show on the subform
accordingly. What else do I need to do?
----------------------------
From Previous Post....

Hi Everyone,

My head is clouded and I can't seem to think straight right now. It's been a long day. :)

I'm trying to do something simple. I want to use listbox values to add
Click to expand...
new records to a table and have only those records as a source for the
subform. I have created a recordset and the records are added to the table,
but I can't figure out how to set the subform's record source so that
records are shown corresponding to the selected listbox values. Am I making
sense? Any help would be greatly appreciated. Thanks!
 
Back
Top