Rowsource from SQL String

[Randomblink]

With lstGenericDELETE
.ColumnCount = 4
.ColumnWidths = "0in;0in;1in;1.3in"
.RowSource = "SELECT DISTINCT [tbl_LINK_ProjectBasics-Employee].[Empl_ID], [tbl_LINK_ProjectBasics-Employee].[Proj_ID], " & DLookup("[Empl_FirstName]", "tbl_Employee", "[Empl_ID] =" & [Empl_ID]) & "AS FirstName, " & DLookup("[Empl_LastName]", "tbl_Employee", "[Empl_ID] =" & [Empl_ID]) & "AS LastName, " & "[FirstName]" & " " & "[LastName]" & " AS Employee, " & DLookup("[Dept_ID]", "tbl_Employee", "[Empl_ID] =" & [Empl_ID]) & "AS DeptLook, " & DLookup("[Dept_Name]", "tbl_Department", "[Dept_ID] =" & [DeptLook]) & "AS Department FROM [tbl_LINK_ProjectBasics-Employee] WHERE ((([tbl_LINK_ProjectBasics-Employee].[Proj_ID])=[Forms]![mstrfrm_ProjectBasics]![Proj_ID]));"
.Tag = "DELETE ProjectBasicEmployee"
End With

If you will check out the attached GIF, it shows the relationships I am using...

The above lines of code are being used to attach a Rowsource to a ListBox.

The idea is:
I have a form called mstrfrm_ProjectBasics.
I have it's RecordSource set to: tbl_ProjectBasics.
As I move from Project to Project, I want to use this list box for various things, but when I click a button, I want the listbox to show all of the employees assigned to this Project...

Now then, when I create the listbox control on the form in Design View, and I manipulate the Rowsource by clicking on the [...] button on the Properties box, I get a Query creator...
I create the Query, it works just like I want it to, so I view the SQL code and cut and paste...

Well, the code at the start is Cut-And-Pasted and it doesn't work...

Can someone help me with why?

It keeps giving me one of the two following run-time errors:

Microsoft Access can't find the field 'Forms' referred to in your expression.

or

Microsoft Access cant find the field '|' referred to in your expression.

What am I doing wrong...????

 

[Jon K]

The problem lies in the fact that DLookup() requires its three arguments to be text strings in quotes.

For example, this Select query runs fine:-

SELECT [Date], [Currency], [Currency] - DLookup("[Currency]", "[myTable]", "[Rank]= " & [Rank]-1) AS Difference FROM myTable;


But if I want to assign the SQL string to the RowSource of a list box, I must double every quote in the DLookup() function:-

lstMyListBox.RowSource = "SELECT [Date], [Currency], [Currency] - DLookup(""[Currency]"", ""[myTable]"", ""[Rank]= "" & [Rank]-1) AS Difference FROM myTable;"


In code, two consecutive quotes will return one quote, so the quotes in the DLookup() function are maintained.

Of course, it will be much simpler if I save the query and assign the query name to the RowSource of the list box.

Hope this helps.

 

[Randomblink]

Rowsource = Query vs. SQL Text String

That sounds great...something I can give a shot at...

BUT as to your comment about saved query, I tried that route too, and it comes back with problems because I refer to my mstrfrm in the query...

When I use a saved query as my rowsource, first off I have to refer to it like:

.RowSource = [Query].qry_mstrfrm_EmployeesOnProject

Secondly, I get this error:

Microsoft Access can't find the field "Forms" referred to in your expression.

Any thoughts?

 

[Jon K]

I don't think the reference to a form is a problem. I have just been able to run this query:

SELECT Date, Currency, Currency - DLookup("Currency", "MyTable", "Rank= " & [Rank]-1 ) AS Difference
FROM MyTable WHERE Date < Forms!MyForm!txtDate;

from the Click event of a command button with this line:

lstMyListbox.RowSource = "queryName"


and also with this:

lstMyListbox.RowSource = "SELECT Date, Currency, Currency - DLookup(""Currency"", ""MyTable"", ""Rank= "" & [Rank]-1 ) AS Difference FROM MyTable WHERE Date < Forms!MyForm!txtDate;"

 

[Randomblink]

Aha

I didn't put the query name in quotes...
I was afraid that referencing it as a string would screw it all up...
Now I know better...

THANKS!