There is an Error can you spot it?

There is an error in the code below which returns 'OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET'

The error is due to the statusfield not being set but how do I set it?

Any help would be appreciated

Simon

For Each maxfield In MAXData.Fields
Count = Count + 1

If maxfield.Name = "dload_status" Then
strSQLstatus = "SELECT mmiss.mmiss_code" & " FROM mmiss" & "WHERE mmiss.mmiss_code =" & Chr(39) & maxfield.Value & Chr(59)
frmSearch.mmissdatasource.RecordSource = strSQLstatus
Set StatusCode = frmSearch.mmissdatasource.Recordset
frmView.MAXData(Count) = statusfield.Value
'****This line ERROR's **** ^^^^^^^^^'
Else
frmView.MAXData(Count) = maxfield.Value
End If
Next

I'm guessing that you have declared statusfield as a field for this one..

Try

Code:

---


Dim statusfield AS Field

Set statusfield = NEW Field

---

The statusfield is defined as follows

code:
dim statusfield as ADODB.field

and setting the statusfield as new doesn't help at all.

any other suggestions?

Post the complete code with declarations included, this will make it easier for me to see what is missing... Thanks

Function Search_PN()
Dim strSQL As String
Dim MAXData As ADODB.Recordset
Dim maxfield As ADODB.field

Dim strSQLstatus As String
Dim StatusCode As ADODB.Recordset
Dim statusfield As ADODB.field

'Set statusfield = New field

Dim Count As Integer
Count = -1

strSQL = "SELECT uzdload.dload_item, uzdload.dload_descext, uzdload.dload_drawiss, uzdload.dload_striss, uzdload.dload_status, uzdload.dload_stitem, uzdload.dload_stiss" & " FROM uzdload" & " WHERE uzdload.dload_item = " & Chr(39) & frmSearch.DataComboPN.Text & Chr(39) & Chr(59)

'Identifies which ADO connection is being used for SQL.
frmSearch.maxdatasource.RecordSource = strSQL
Set MAXData = frmSearch.maxdatasource.Recordset





For Each maxfield In MAXData.Fields
Count = Count + 1

If maxfield.Name = "dload_status" Then
strSQLstatus = "SELECT mmiss.mmiss_code" & " FROM mmiss" & " WHERE mmiss.mmiss_code =" & Chr(39) & maxfield.Value & Chr(39) & Chr(59)
frmSearch.mmissdatasource.RecordSource = strSQLstatus
Set StatusCode = frmSearch.mmissdatasource.Recordset
frmView.MAXData(Count) = statusfield.Value
Else
frmView.MAXData(Count) = maxfield.Value
End If
Next

MAXData.Close
Set MAXData = Nothing

End Function

Ok, before you use the recordset objects, "MAXData, and StatusCode", and the Field Objects, "statusfield and max field" you need to set them.

Code:

---


Set MAXData = New ADODB.Recordset
Set StatusCode = New ADODB.Recordset

Set statusfield = New ADODB.Field
Set maxfield = New ADODB.Field

---

Hope this helps...

Thanks for that!

Why do I need to set them as new objects?

When I submit or omit the NEW (word) I get a "METHOD or DATA MEMBER NOT FOUND" error

Simon