Query with user input displayed in a list box

[metalboy]

Hi All,

I have a query which searches post codes to bring up and account. i want to have the results displayed in a list box on a form. how do i do this when the user has to input infomation into the query?

Regards

ALex

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[SiJP]

Create the list box on the form, set its column count to the number of columns in your query, set the column widths, then set the row source to be the name of your query.
Bobs yer uncle

------------------------
Hit any User to continue

 

[metalboy]

but does that work if the query needs user input?

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[SiJP]

If your query changes, and you need to update the list box with the results, just simply call

YourListBoxName.Requery

------------------------
Hit any User to continue

 

[metalboy]

Hi there,

My sql code is as follows:

SELECT AdamGoughCallLogging.iceid, AdamGoughCallLogging.customerreference, AdamGoughCallLogging.verifierid, AdamGoughCallLogging.salesagentid, AdamGoughCallLogging.date, AdamGoughCallLogging.teammanager, AdamGoughCallLogging.timelogged
FROM AdamGoughCallLogging
WHERE (((AdamGoughCallLogging.verifierid)=fOSUserName()) AND ((AdamGoughCallLogging.date)=[Date To View]));

When i point a list box at this it does nothing as the user needs to input a dat for the query to run.

Thanks again

Alex

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[SiJP]

Ok, lets get this licked...

Assuming you have the following set up:

Form
-----
Objects: List Box, textbox [Date To View], Command Button labelled cmdRunQuery

Stick this code in:

Private Sub cmdcmdRunQuery_Click()

    Dim strsql As String
    Dim strOSUserName As String
    strOSUserName = Environ("UserName")
    
    strsql = "SELECT AdamGoughCallLogging.iceid, AdamGoughCallLogging.customerreference, AdamGoughCallLogging.verifierid, AdamGoughCallLogging.salesagentid, AdamGoughCallLogging.date, AdamGoughCallLogging.teammanager, AdamGoughCallLogging.timelogged " & _
             "FROM AdamGoughCallLogging " & _
             "WHERE AdamGoughCallLogging.verifierid = '" & strOSUserName & "'  AND AdamGoughCallLogging.date = #" & Format(Me.[Date To View], "dd mmm yyyy") & "#;"
    
    'Assuming you now have a list box set up with properties as follows:
    ' 7 columns
    ' 7 column widths (e.g. "1;1;2;3;2;3;1")
    ' RowSourceType as Table/Query
    
    lstResults.RowSource = strsql
    lstResults.Requery
End Sub


So basically you are running the query from the form, taking the 'Date to View' criteria from a text box on your form. The list box will update each time you run the query from the go button.  One more thing is I've used a diffrerent way to get the OS User Name through a built in function called Environ("UserName").

Untested code as I haven't got time to set up your objects.. let me know of any errors and I'll try to resolve.

HTH's.

------------------------
Hit any User to continue