Databases
StackOverflow2013
Postsdbo
POVBA string length problem
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA string length problem
    primarykey
    data
    text
    <p>I have an Access application where everytime a user enters the application, it makes a temp table for that user called 'their windows login name'_Temp. In one of my reports I need to query using that table, and I can't just make a query and set it as the recourdsource of the report, since the name of the table is always different. </p> <p>What I tried then was to programatically set the recordset of the report by running the query and setting the form's recordset as the query's recordset. When I tried this, it kept giving me an error about the query. </p> <p>I tried to debug, and I found that the string variable isn't able to contain the whole query at once. When I ran it with break points and added a watch for the string variable, it shows me that it cuts off the query somewhere in the middle.</p> <p>I've experienced this problem before, but that was with an UPDATE query. Then, I just split it into two queries and ran both of them separately. This one is a SELECT query, and there's no way I can split it. Please help!</p> <p>Thank you</p> <p>Heres what I've tried doing:</p> <p>ReturnUserName is a function in a module that returns just the login id of the user</p> <pre><code>Private Sub Report_Open(Cancel As Integer) Dim strQuery As String Dim user As String user = ReturnUserName strQuery = "SELECT " &amp; user &amp; "_Temp.EmpNumber, [FName] &amp; ' ' &amp; [LName] AS [Employee Name], " &amp; _ "CourseName, DateCompleted, tblEmp_SuperAdmin.[Cost Centre] " &amp; _ "FROM (tblCourse INNER JOIN (" &amp; user &amp; "_Temp INNER JOIN tblEmpCourses ON " &amp; _ user &amp; "_Temp.EmpNumber = EmpNo) ON tblCourse.CourseID = tblEmpCourses.CourseID) " &amp; _ "INNER JOIN tblEmp_SuperAdmin ON " &amp; user &amp; "_Temp.EmpNumber = tblEmp_SuperAdmin.EmpNumber" &amp; _ "WHERE (((" &amp; user &amp; "_Temp.EmpNumber) = [Forms]![Reports]![txtEmpID].[Text])) " &amp; _ "ORDER BY CourseName;" Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset Dim rsCmd As ADODB.Command Set rsCmd = New ADODB.Command rsCmd.ActiveConnection = CurrentProject.Connection rsCmd.CommandText = strQuery rs.Open rsCmd Me.Recordset = rs rs.Close End Sub </code></pre> <p>This what strQuery contains when I add a breakpoint on <code>rsCmd.CommandText = strQuery</code>:</p> <blockquote> <p>SELECT myusername_Temp.EmpNumber, [FName] &amp; ' ' &amp; [LName] AS [Employee Name], CourseName, DateCompleted,</p> <p>tblEmp_SuperAdmin.[Cost Centre] FROM</p> <p>(tblCourse INNER JOIN (myusername_Temp INNER JOIN tblEmpCourses ON</p> <p>myusername_Temp.EmpNumber = EmpNo) ON tblCourse.CourseID=</p> </blockquote> <p>(It's all one line, but I've written it like this because the underscores italicize the text)</p> <p>And the error I get says Run Time Error: Join not Supported. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USzohair
    1. USzohair
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COthe temp table, is its sole purpose to hold the username? Or does it have other data associated to the user
      singulars
      1. POVBA string length problem
      1. USCaRDiaK
    2. COPlease post some code, I suspect this may be easy enough to fix.
      singulars
      1. POVBA string length problem
      1. USFionnuala
    3. COthe temp table does have other data aswell
      singulars
      1. POVBA string length problem
      1. USzohair
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload