Query does not work in code...

[sanders720]

Query works great in Access per this syntax

SELECT qrySoftwareTracking.[IT Job Number]
FROM qrySoftwareTracking;

Cut does not work in code below, returns -1 for a recordcount..

With rs
sql = "SELECT qrySoftwareTracking." & A(q) & " FROM qrySoftwareTracking"
Debug.Print sql

.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
.MoveLast
c = .RecordCount
Debug.Print c
.MoveFirst
For q2 = 1 To c
Debug.Print .Fields(A(q)).Value
If .Fields(A(q)).Value <> &quot;&quot; Then
' Me.cboITJobNo.AddItem .Fields(A(q)).Value

End If
.MoveNext
Next q2
.Update
.Close

End With


Any thoughts??? Thanks in advance for the help...

 

[vbajock]

&quot; & A(q) & &quot;

should be ' &quot; & A(q) &&quot; ' &quot;

 

[cmmrfrds]

In ADO the only cursor that will always provide a record count is a static cursor that is client side. The default cursor is server side and a dynamic cursor usually does not provide a record count since it is difficult to maintain.

'this forces a static cursor.
rs.CursorLocation = adUseClient

 

[sanders720]

' B (q) & .RowSource ' = sql reiterated below seems to want to provide the pop-up menu for rowsource, but does not want to iterpret correctly. The sql statement for A(q) works fine, but how can I set the rowsource for an arrayed variable?

Partial Code Below... Thanks for the help!...


Set rs = New ADODB.Recordset

A = Array(&quot;[IT Job Number]&quot;, &quot;Vendor&quot;, &quot;[Invoice Number]&quot;, &quot;[Requesting User]&quot;, &quot;[Authorizing User]&quot;, &quot;[Purchase Date]&quot;, _
&quot;[Warranty End Date]&quot;, &quot;[Product Manufacturer]&quot;, &quot;[Product Name]&quot;, &quot;[Product Part Number]&quot;, &quot;[Product Serial Number]&quot;, _
&quot;[CD Key]&quot;, &quot;[Upgrade Flag]&quot;, &quot;Quantity&quot;, &quot;[Seats Covered]&quot;, &quot;[User Assignment]&quot;, &quot;[Installed PC Name]&quot;, _
&quot;[Product License Number]&quot;, &quot;[Purchase Price]&quot

B = Array(&quot;cboITJobNumber&quot;, &quot;cboVendor&quot;, &quot;cboInvoiceNumber&quot;, &quot;cboRequestingUser&quot;, &quot;cboAuthorizingUser&quot;, &quot;cboPurchaseDate&quot;, _
&quot;cboWarrantyEndDate]&quot;, &quot;cboProductManufacturer&quot;, &quot;cboProductName&quot;, &quot;cboProductPartNumber&quot;, &quot;cboProductSerialNumber&quot;, _
&quot;cboCDKey&quot;, &quot;cboUpgradeFlag&quot;, &quot;cboQuantity&quot;, &quot;cboSeatsCovered&quot;, &quot;cboUserAssignment&quot;, &quot;cboInstalledPCName&quot;, _
&quot;cboProductLicenseNumber&quot;, &quot;cboPurchasePrice&quot

For q = 1 To 19
Debug.Print A(q)
X = B(q)
Debug.Print X

----> ' B (q) & .RowSource = sql