Trouble with .AddItem

[sanders720]

This program works great, until I try to add the SHAPE field.
====> Me.cboShape.AddItem .Fields("SHAPE&quot

All fields in the database are text. I have checked the field names and they all confirm correct. I cannot understand why the logic works for the first three and not for the fourth thru seventh.

Any thoughts, and thanks in advance for the help.


Private Sub Form_Load()
Dim rs As Recordset
Dim sql As String
Dim selsql As String
Dim q As Integer
Dim c As Integer

Set rs = New ADODB.Recordset

With rs

.CursorLocation = adUseClient
.Open "SELECT DISTINCT ITEM from tblStockList", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False", adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;ITEM&quot <> &quot;&quot; Then
Me.cboItem.AddItem .Fields(&quot;ITEM&quot
.MoveNext
End If
Next q
.Close

.Open &quot;SELECT DISTINCT CODE from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;CODE&quot <> &quot;&quot; Then
Me.cboCode.AddItem .Fields(&quot;CODE&quot
.MoveNext
End If
Next q
.Close

.Open &quot;SELECT DISTINCT DESCRIPTION from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;DESCRIPTION&quot <> &quot;&quot; Then
Me.cboDescription.AddItem .Fields(&quot;DESCRIPTION&quot
.MoveNext
End If
Next q
.Close

.Open &quot;SELECT DISTINCT SHAPE from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;SHAPE&quot <> &quot;&quot; Then
====> MsgBox .Fields(&quot;SHAPE&quot
====> Me.cboShape.AddItem .Fields(&quot;SHAPE&quot
.MoveNext
End If
Next q
.Close

.Open &quot;SELECT DISTINCT WEIGHT from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;WEIGHT&quot <> &quot;&quot; Then
Me.cboWeight.AddItem .Fields(&quot;WEIGHT&quot
.MoveNext
End If
Next q
.Close

.Open &quot;SELECT DISTINCT MATERIAL from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;MATERIAL&quot <> &quot;&quot; Then
Me.cboMaterial.AddItem .Fields(&quot;MATERIAL&quot
.MoveNext
End If
Next q
.Close

.Open &quot;SELECT DISTINCT GRADE from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;GRADE&quot <> &quot;&quot; Then
Me.cboGrade.AddItem .Fields(&quot;GRADE&quot
.MoveNext
End If
Next q
.Close

End With

End Sub

 

[BlackburnKL]

Try putting the .Fields(...) command on the next line, separate from the .AddItem. BlackburnKL

 

[sanders720]

Produces an &quot;Argument Not Optional&quot; Error on the .AddItem portion. Have I interpreted you correctly?


.Open &quot;SELECT DISTINCT SHAPE from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c
If .Fields(&quot;SHAPE&quot <> &quot;&quot; Then
MsgBox .Fields(&quot;SHAPE&quot
Me.cboShape.AddItem
.Fields (&quot;SHAPE&quot
.MoveNext
End If
Next q
.Close

 

[HardcoreTechnoHead]

What was the origial error?

 

[BlackburnKL]

Sorry about that. I am used to using this in a &quot;With&quot; context (which you are not doing). Put it back on the same line.

Next, I would try being more specific. Try useing rs.Fields(&quot;SHAPE&quot instead. BlackburnKL

 

[sanders720]

This syntax is used on seven fields, to populate seven combo boxes. The first three work, and the fourth snags, and this the fifth thru seventh as well. Actually, the fifth also snags when I rem out the fourth. The complete code is in the first segment of the thread.

I am confused because I haven't changed anything in the syntax. The field names are correct, and so are the names of the combo boxes.

 

[sanders720]

I removed With rs and End With. I placed rs everywhere. Still had the same problem. Gow should the syntax look?

rs.Open &quot;SELECT DISTINCT SHAPE from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
rs.MoveLast
c = rs.RecordCount
rs.MoveFirst
For q = 1 To c
If rs.Fields(&quot;SHAPE&quot <> &quot;&quot; Then
MsgBox rs.Fields(&quot;SHAPE&quot
Me.cboShape.AddItem
rs.Fields (&quot;SHAPE&quot
rs.MoveNext
End If
Next q
rs.Close

 

[BlackburnKL]

Try this. I changed a couple of things. First, I used a while not end of file loop. Second, I just noticed that your .MoveNext command was inside the If statement. It should be outside so that you move to the next record regardless of whether or not the SHAPE field is empty. That may also avoid problems you may encounter with the .RecordCount property.

rs.Open &quot;SELECT DISTINCT SHAPE from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
rs.MoveFirst
While Not rs.Eof()
If rs.Fields(&quot;SHAPE&quot <> &quot;&quot; Then
MsgBox rs.Fields(&quot;SHAPE&quot
Me.cboShape.AddItem rs.Fields(&quot;SHAPE&quot
End If
rs.MoveNext
End While
rs.Close

BlackburnKL

 

[HardcoreTechnoHead]

Two questions

1) What is the error that is produced?
2) Which version of ADO does you project reference?

 

[sanders720]

1. No errors, it just doesn't populate the combo box. The first three are populated correctly, and the fourth is not.

2. Microsoft ActiveX Data Objects 2.5 Library

 

[HardcoreTechnoHead]

Check the values in you database table.
One of them is an empty string.

.Open &quot;SELECT DISTINCT SHAPE from tblStockList&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\deepblue\EngineeringBOM\Data\EngineeringBOM_data.mdb;Persist Security Info=False&quot;, adOpenStatic, adLockOptimistic
.MoveLast
c = .RecordCount
.MoveFirst
For q = 1 To c


When code gets here with the recordset, the first entry in the recordset is an empty string
If .Fields(&quot;SHAPE&quot <> &quot;&quot; Then
====> MsgBox .Fields(&quot;SHAPE&quot
====> Me.cboShape.AddItem .Fields(&quot;SHAPE&quot

The movenext is inside the IF block so never gets evaluated if there is an empty value in the database
.MoveNext
End If
Next q
.Close

 

[sanders720]

Brilliant... I knew it had to be something simple...