Using a lsit box in a query

[Ben92]

Hello everyone,

I am having a problem using a list box in a query. I am trying to get the list box to give me the option of slecting a particular file name based off input and output information. I can get the list box to provide the correct file names, but the file names aren't specific to the input and output info. In other words, when I run the query, I want it to use specific input and output data and only give the file names specific to I/O data in the list box instead of all possible file names for every I/O combination.

 

[mhcruella]

Can you give a sample of the query? If it is relating to a table, can you also give the table structure?

 

[Ben92]

Here is a rough example:

Input/Ouput/file name
1,1,FileA
1,2,FileA
1,2,FileB
1,2,FileC
1,3,FileA
1,3,FileB
2,2,FileA
2,2,FileB
2,3,FileA
3,1,FileC

For a specific I/O I want it to return just the file names that pertain to it.

Example: I/O = 1,1
I want it to return just FileA in the list box. Or:
Example: I/O = 1,3
I want it to return just FileA and FileB in the list box.

Right now the list box is returning all the possible files as shown above.

 

[mhcruella]

set the recordsource for the listbox =

SELECT tblInOutFile.FileName
FROM tblInOutFile
WHERE (((tblInOutFile.Input)=[Forms]![frmInOutFile]![Input]) AND ((tblInOutFile.Output)=[Forms]![frmInOutFile]![Output]));

I made a form called frmInOutFile with two combo boxes(one called Input and the other called Output) and one listbox (FileName). In the afterupdate event of the combo boxes, I tell it to:

FileName.Requery

After the combo boxes are updated, the values in the Listbox will change.

 

[Ben92]

I have another question:

Some of the Input/Outputs do not have file names already associated with them, so I will need to type them in. How would I go about doing this with a list box.

 

[mhcruella]

I do not believe there is a way you can type the filenames into the listbox. I would think that making an invisible textbox and command button on the form and then some code would them would be the best way.

I used the On-Double-Click event of the FileName listbox and put code that would unhide a textbox and a command button. The command button would have the following code behind it:

Dim strSQL As String
    
    strSQL = "INSERT INTO tblInOutFile ( [Input], [Output], FileName ) "
    strSQL = strSQL & "SELECT tblInOutFile.Input, tblInOutFile.Output, [Forms]![frmInOutFile]![txtNewFileName] "
    strSQL = strSQL & "AS NewFileName "
    strSQL = strSQL & "FROM tblInOutFile "
    strSQL = strSQL & "WHERE (((tblInOutFile.Input)= " & [Forms]![frmInOutFile]![Input] & ") AND "
    strSQL = strSQL & "((tblInOutFile.Output)= " & [Forms]![frmInOutFile]![Output] & "));"
    
    DoCmd.RunSQL (strSQL)
    
    txtNewFileName.Visible = False
    lblNewFileName.Visible = False
    FileName.Requery
    FileName.SetFocus

When the FileName listbox gets the focus, I hide the command button (which you can't do when using it). You may need some error trapping to make sure you are not entering duplicate values for each input\output.