List box problem

[Guest]

Hi All,

I have a list box which is bound to a query containing two two fields. The
first being an ID number (this is viewed by the user), the second being a
boolean value which is defaulted to 0 (this is hidden from the user).
What I want to do is take the selected items put them into a new query that
I can use for a another form.

I have code behind a cotrol button as follows:

Dim varItem As Variant
Dim strList As String

With List01
If .MultiSelect = 0 Then
txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & vbCrLf
Next varItem
MsgBox strList, , "Data From List01"
End If
End With

This is OK as it give me a list but I don't know how to take the selected
items and put them into another / new query.

Any help is greatly appreciated.

 

[Guest]

I do not thing the list box is ideal to solve your problem. I thing that is
better to use subform instead of listbox. In subform in data view you can
create one boolean field can be stored in table. This stored value can be
used in new subform.

When you want to use two listbox it is necessary to make translation
function. This translation function translate lines in your list box to
underlayed records ID. It is only first step. You have to build reverse
translation to change records IDs to relative posssiton on new listbox.
Change logical boolean flag (boolean record field) marking record selected
and unselected before destroying first listbox. Before opening textbox2 you
have to mark select listbox item via hidden flag (recordset field).

Remember there can be delay between displayung textbox1 and textbox2. I case
of multiuser database can be some record addet or deleted.

 

[Guest]

You do not put data in a query. Data is stored only in Tables. A query is
just a representation of the data in table or tables.

Not knowing exactly what the data in your list box represents, it is
impossible to recommend an approach that would work for your. If you can
define the process in more detail, perhaps we can give some guidance.

 

[Guest]

Hi Again,

And apologies for not providing enough information.

Indeed the data does come from a table. The database contains information on
inspection equipment and calibration. The fields that are relevant are the
ID Number and a boolean field called 'Remove' (if the Remove field = 0 then
the equipment is shown in another form, if 'Remove'= -1 then the're not)

If one or more pieces of equipment fail calibration, I want the user to
select the items, click on a command button to open a pop up window in order
to confirm the selection and from there set the 'Remove' value to -1. These
items are then to be stored in a query (Removed Items).

It is important that these items are not deleted as they may, at a later
date, be sent out for repair and re-calibration, and then added back into the
equipment list.

I hope that this makes things a bit clear, and once again thank you for any
help given.

 

[Guest]

Okay, that makes sense. But, again, data is not stored in queries, only in
tables (stored in a query (Removed Items)). Queries are used to either
retrieve or manipulate data in tables. Only tables store data.

A list box would work for this, but I have a suggestion that would be easier
to code and would actually be a better solution because it would give better
visibility to the removed items. It would also be less prone to errros.

My suggestion would be to add a boolean (Yes/No) field to your equipment
table named Removed. Make it True (-1) that the equipment has been removed
and False (0) if it is not removed. Now, rather than a list box, use a
subform based on the table with the ID and the Removed field and filter it
for Not Removed. Make the control for the Removed field a check box. When
the user checks it, you have now updated your Equipment table to show the
item removed. That way, you don't have to have a lot of code or another
table just for removed items. The other form to show only removed items, you
just filter it on Removed = True and only Removed Items will show up.

If you really want to use a list box, let me explain the steps involved
without the detail (because it is a lot) and you will see why I suggest a
subform.

Create a multi select list box (extended) with the two fields.

Write a function that will create a Where clause to select records from the
table based on the selections in the list box.

Create a query that will append the selected items to the removed table.

Create a query that will delete the selected items from the Equipment table.

Put a command button on your form to remove the items and write code in the
Click event that will call the function that creates the where clause and
executes both of the action queries and requerys the list box.

Now, after all that, if you want the detail, I will help with that, but my
guess is you will prefer the sub form option.