Rowsource query error

[mafhobb]

Hello, I have the following code in the rowsource for a listbox:

SELECT [tblPrinterSelection].[ID], [tblPrinterSelection].[Doc], [tblPrinterSelection].[Device] FROM tblPrinterSelection WHERE tblPrinterSelection.Computer=Environ("computername");

I'ts telling the "undefined function "Environ" in expression.

I am trying to fill out a listbox with info that is specific to the computer running the database. The computer name is already saved in the table mentioned. So what I need is the WHERE conditionant to figure out what the name of the computer (through Environ?) is and then match it to the table to get the approppriate results.

What am I doing wrong?

mafhobb

 

[stopher]

I would say Environ is a VBA function so you can't use it in a query.

So create a function in VBA that uses Environ to return the result. Then you can use the function instead of Environ.

hth
Chris

 

[mafhobb]

I think that you are right. After messing with it for a while I decided to fill the listbox with the following code, which works fine.

    'Find the computer name
    sHostName = Environ$("computername")
   
    'Find data for listbox limited to the computer's name"
    Me.lstListAllPrinters.RowSource = "SELECT [ID],[Doc], [Device] FROM tblPrinterSelection WHERE [Computer]= '" & sHostName & "';"

Thanks

mafhobb

 

[shoji]

Hello mafhobb,

You can write this code at the form's Load Event.

Me!listbox.Rowsource = "SELECT [ID], [Doc], [Device] " & _
          "FROM tblPrinterSelection " & _
          "WHERE Computer='" & Environ("computername") & "';"

Be careful about the positions of double and single quotation marks.

Shoji

 

[shoji]

Oops. You already got it. Sorry for redundancy.

 

[mafhobb]

I have another one...

        DCount("ID", "tblPrinterSelection", "Computer='" & sHostName & "' And Device Is Null")

I am trying to count how many records exist in the table "tblPrinterselection" with the field "Computer" equal to "sHostName" that have a null value for the field "Device".

The code above right now returns "0" no matter what's in the table.

mafhobb

 

[mafhobb]

Actually, simplifying the code like this worked fine:

DCount("ID", "tblPrinterSelection", "Computer='" & sHostName & "'")

Thanks!

mafhobb