RowSource in VB(Access) from an array of data! Please Help... 2

[sanders720]

----> X = sql (located about half way down) does not change the property. I am trying to change the value of RowSource from the defined array's. The sql statement works and is counting the data correctly.

Please let me know if you have any ideas, and thank you in advance for the help...


Set rs = New ADODB.Recordset

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

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

For q = 1 To 19
Debug.Print A(q)
Debug.Print B(q)
X = "Me." + B(q) + ".RowSource"
Debug.Print X

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

----> X = sql

.CursorLocation = adUseClient
.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
Next q

Set rs = Nothing



End Sub

 

[FancyPrairie]

I'm not sure I follow. But I think you want X's rowsource to equal the sql statement. If so, X = SQL should look like this:

Me(B(q)).RowSource = sql

 

[sanders720]

The syntax works, but I get thye following error:

Microsoft Access can't find the field 'cboITJobNumber' referred to in your expression - runtime error 2465.

The field is actually the name of a combo box. I am looking to do exactly as you thought in the last email. I concatenated everything to variable X because I thought there were problems with interpreting the B(q) variable.

 

[FancyPrairie]

It appears that the syntax is correct because of the error message. Are you executing this code from a module within a form or just a module? If it's just a module, then you can't use &quot;Me&quot;, you have to use this syntax instead:
Forms!YourFormName(B(q)).Rowsource

 

[sanders720]

It is being executed at form load, not a module. I will try the new syntax tomorrow.

Thanks fot the help...

 

[sanders720]

No go... It is looking for a field instead of an object name! I get the same error using either of the lines of code below.


Microsoft Access can't find the field 'cboITJobNumber' referred to in your expression - runtime error 2465.


Me(B(q)).RowSource = sql

Forms!YourFormName(B(q)).Rowsource

 

[FancyPrairie]

Alright, try this. First put a statement in that works. That is, hardcode the command to set the Rowsource and change your do loop so it only loops 1 time. Once you have it working then place your cursor on the line that works and press F9. This will set a break point on that line (F9 toggles the break point on/off). Now execute your code again. The program will pause at the break point. Select Debug's Immediate Window via the View menu. In the Immediate window check the original syntax by typing ?Me(B(q)).Rowsource to see if it matches the line that works.

Note: Press F8 to step thru your code one line at a time. Press F5 to execute your code until it encounters another break point, a stop statement, or it finishes. If you place the cursor over a variable, a tooltip will display the value of the variable.

 

[GComyn]

instead of Me(B(q)).RowSource = sql try

Me.Controls(B(q)).RowSourse

gcomyn

 

[sanders720]

Yes, in the immediate window I still get the same runtime error.


instead of Me(B(q)).RowSource = sql try

Me.Controls(B(q)).RowSource

This causes my PC to act very strange. The form never loads, and the Windows background blends in with the Access database. Additionally, when I move the form, I get a low panning effect.

 

[FancyPrairie]

Yes you will still get the same runtime error in the Immediate window. What I suggested was, hard code the name of one of the comboboxes within your loop to see if you can make it work. Change your loop so it only loops once. For example,

do q = 1 to 1
...
Me!cboITJobNumber.Rowsource = sql
....
Loop

When you get that to work, then try
Me(&quot;cboITJobNumber&quot.Rowsource = sql. If that works, then
you can examine B(q) to see what it looks like.

Also, note that arrays, by default begin with 0 not 1. So your loop should be q = 0 to 18.

 

[sanders720]

I think it's all figured out. I had a combo box labeled wrong. Anyhow, this is a good post for assigning variables to arrays, because there is little help on this in the manuals and books out there. Thanks for your help with it!

I have posted the final code below. I can use 1 to 19 because I have selected OPTION BASE 1...

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;cboITJobNo&quot;, &quot;cboVendor&quot;, &quot;cboInvoiceNo&quot;, &quot;cboRequestingUser&quot;, &quot;cboAuthorizingUser&quot;, &quot;cboPurchaseDate&quot;, _
&quot;cboWarrantyDate&quot;, &quot;cboManufacturer&quot;, &quot;cboName&quot;, &quot;cboPartNo&quot;, &quot;cboSerialNo&quot;, &quot;cboCDKey&quot;, _
&quot;cboUpgrade&quot;, &quot;cboQuantity&quot;, &quot;cboSeats&quot;, &quot;cboUser&quot;, &quot;cboInstalledPC&quot;, &quot;cboLicense&quot;, _
&quot;cboPurchasedPrice&quot

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

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

' Forms!frmSoftwareTracking(B(q)).RowSource = sql (syntax for modules)
Me(B(q)).RowSource = sql



.CursorLocation = adUseClient
.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
.MoveLast
c = .RecordCount
Debug.Print c
.MoveFirst

.Update
.Close

End With
Next q

Set rs = Nothing



End Sub