Query specific column name from a list box via code

[ma143hal]

Hello all,

This is a new project that I'm working on using Access 2000 version to modify/automate the company's current database. I'm dealing with ugly unnormalized database and they don't want to change anything at this time.
What the users would like to do is produce a report for certain columns, from their selections, do some calculations such as AVG, MIN, MAX, etc then output to Excel.

I have created a form with a list box to allow user(s) to select multiple selections. I have a code to gather their selections then insert into a table, but this is only good if I'm querying for records or rows and not columns. The list box shows the names of all the columns (QTY1 through QTY53).

The backend is SQL Server. Below is the table's layout:

Item Number
Store Number
FiscalYear
QTY1
QTY2
QTY3
QTY4
(through QTY53)

The QTY# columns represents Week# that has totals in their inventory for that week so this is a Number Field. First week of January is QTY49 - and this vary from year to year. Can't figure out how they calculate this whether it must have at least 3-5 days or what?

I'm not sure how to refer to the Column Name(s) from the list box selection(s) for reporting.

Can you help?

Thanks and Have a Great Day!

 

[boblarson]

If using code within the form that the control is on (substituting the actual column number [starting with zero as the first column] for x)

Me.ComboBoxNameHere.Column(x)

 

[ma143hal]

Hello BL,

Thanks for your reply.

I'm using a ListBox not ComboBox so that they can select 1 or multiple at a time. The user even want ALL the list to be visible so that they don't have to scroll down.

 

[Bodisathva]

same thing...Me.lstBoxName.Column(0). use Me.lstBoxName.ItemsSelected(IDX) to pull the selected items. The index is base 1.

 

[ma143hal]

Thanks! Not quite sure yet how to incorporate your advise, but I will give it a try.

 

[boblarson]

If they can select multiple items you will have to use a do loop to run through each selection and do the stuff you want done for each record.

For example I have this code to take a list box and open an item based on each selected item:

    Do Until intCount = Me.lstResults.ItemsSelected.Count
        If Not IsNull(Me.lstResults.Column(1)) Then
            ' sets the stack location
            strFolder = DLookup("FileLocation", "tblFileLocation")
            ' reads the patient name
            strPatientName = Me.lstResults.Column(1, Me.lstResults.ItemsSelected(intCount))
            ' reads the stack number for the document selected
            strStackID = Me.lstResults.Column(9, Me.lstResults.ItemsSelected(intCount))
            ' reads the file name
            strStackFileID = Me.lstResults.Column(6, Me.lstResults.ItemsSelected(intCount))
            ' reads the document type (.doc or .tif)
            strDocType = Me.lstResults.Column(8, Me.lstResults.ItemsSelected(intCount))
            ' reads the form type (Oly Prescription Form, etc.)
            strFormType = Me.lstResults.Column(2, Me.lstResults.ItemsSelected(intCount))
            ' reads the event date for the item selected
            dteEventDate = Me.lstResults.Column(3, Me.lstResults.ItemsSelected(intCount))
            ' reads the add date for the item selected
            dteAddDate = Me.lstResults.Column(4, Me.lstResults.ItemsSelected(intCount))
            ' builds the file path for the file to open
            strDocNamePath = strFolder & "\" & strStackID & "\" & strStackFileID
            ' for multiple items, if the item is a TIFF then user can open multiples.
            ' for Word documents, one at a time (unless users without IDM installed can
            ' open Word documents faster, if so I will recode this.
            If strDocType = ".tif" Then
                OpenFileName strDocNamePath, strDocType
            End If
        End If
        intCount = intCount + 1
    Loop

 

[ma143hal]

Thank you very much, Bob!

Have a wonderful day!

Regards,
Marita