@ ----> Compile Error - Argument No

[sanders720]

@ ----> Compile Error - Argument Not Optional... I am populating combo boxes with the field information in the array below. I.m snagging here "---->".

Thanks in advance for any light you can shed...


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

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

With rs
sql = "SELECT DISTINCT " & A(q) & " FROM qrySoftwareTracking"
Debug.Print sql
.Open sql, CurrentProject.Connection, , adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q2 = 1 To c
---> If .Fields & &quot;('&quot; & A(q) & &quot;')&quot; <> &quot;&quot; Then
' LOOKING FOR IF .FIELDS (&quot;[IT Job No]&quot <> &quot;&quot; Then
Debug.Print .Fields & &quot;('&quot; & A(q) & &quot;')&quot;
Me.cboITJobNo.AddItem .Fields & &quot;('&quot; & A(q) & &quot;')&quot;
End If
.MoveNext
Next q2
.Close
End With
Next q

 

[TommieB]

On what line of code are you getting the error?

 

[sanders720]

---> If .Fields & &quot;('&quot; & A(q) & &quot;')&quot; <> &quot;&quot; Then

 

[NathanStone]

try this instead ...

If .Fields(A(q)) <> &quot;&quot; Then

or this ...

If .Fields(A(q)).Value <> &quot;&quot; Then

 

[sanders720]

Me.cboITJobNo.AddItem .Fields(A(q)).Value

It seems to be having difficulty with .AddItem. Remember, I'm in VBA Access 2000, not VB6.

Data or Member not Found is the error. The Debug.Print statements are also not working. I did change the code to both your syntax options in the previous post.

Than ks for the help!!! Any other ideas?

 

[NathanStone]

you will have problems with both the test ...

If .Fields & &quot;('&quot; & A(q) & &quot;')&quot; <> &quot;&quot; Then

and the action ...

Me.cboITJobNo.AddItem .Fields & &quot;('&quot; & A(q) & &quot;')&quot;


try changing both lines to ...

If .Fields(A(q)) <> &quot;&quot; Then

and

Me.cboITJobNo.AddItem.Fields(A(q))


You've also got a space after 'AddItem' - it should be removed.

 

[sanders720]

The space after AddItem is necessary because I am within a With rs statement. Removing the space also still causes the same error.

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

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

With rs
sql = &quot;SELECT DISTINCT &quot; & A(q) & &quot; FROM qrySoftwareTracking&quot;
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
Next q

Set rs = Nothing

 

[NathanStone]

do you need the square brackets around the field names in the array?

you could try removing square brackets and changing the line ...

sql = &quot;SELECT DISTINCT &quot; & A(q) & &quot; FROM
qrySoftwareTracking&quot;

to

sql = &quot;SELECT DISTINCT [&quot; & A(q) & &quot;] FROM
qrySoftwareTracking&quot;

I think it is looking for a field called [IT JOB NUMBER] rather than just IT JOB NUMBER.

 

[sanders720]

No Go... Same error on AddItem. The sql statement reads correctly on debug.print if I remove the other interpretive statements and just perform the debug.print.

I could type out all 19 statements and am confident it would work fine. The sql's would just be direct statements. I used the array however, to try and save some space.

Any other ideas, and thanks for the help again!