ADO VB6 Error 3021; Even with 1 record in recordset?

can you show more details like the table design you query and the test code to reproduce the problem=

dbWhereCondition = "SELECT * FROM order_line WHERE order_num = '" & ordnum & "' AND line_num =" & ctrvar
 
   
  Dim oldb As ADODB.Connection

  Set oldbRecordset = New ADODB.Recordset
  Set oldb = New Connection

  oldb.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=N:\databases\gwi.mdb;Jet OLEDB:Database Password=xxxxx;"
   
  oldbRecordset.Open dbWhereCondition, oldb, _
            adOpenDynamic, adLockOptimistic, adCmdTableDirect


  'reccountvar = 0

  'Do Until oldbRecordset.EOF
  '  reccountvar = reccountvar + 1
  'Loop
 
  'If reccountvar = 0 Then
  '     MsgBox "FATAL ERROR: CANNOT FIND THE LINE ITEM IN THE ORDER LINE TABLE"
  '     Exit Sub
  'End If

IF I REFERENCE oldbRecordset AT THIS POINT I GET THE 3021 ERROR.

remove the parameter adCmdTableDirect. as you run a query, that one is wrong. you might use adCmdText instead, which is the default.

now, what exactly do you reference?

dbWhereCondition = "SELECT * FROM order_line WHERE order_num = '" & ordnum & "' AND line_num =" & ctrvar
 
   
  Dim oldb As ADODB.Connection
 
  Set oldbRecordset = New ADODB.Recordset
  Set oldb = New Connection
 
  oldb.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=N:\databases\gwi.mdb;Jet OLEDB:Database Password=xxxxx;"
   
  oldbRecordset.Open dbWhereCondition, oldb, _
            adOpenDynamic, adLockOptimistic
 
  if oldbRecordset.eof and oldbRecordset.bof then
    'recordset is empty
  else
    while not oldbRecordset.eof
      'you should be able to access the oldbRecordset.fields(...) collection here without error
 
 
      oldbRecordset.movenext
    wend
  end if
  oldbRecordset.close

Select allOpen in new window

I modified the code per the posting but I'm still getting the same error. BOF and EOF are TRUE. BUT if I insert a msgbox, and then continue with the process I can access the 1 record in the oldbRecordset.

dbWhereCondition = "SELECT * FROM order_line WHERE order_num = '" & ordnum & "' AND line_num =" & ctrvar
 
   
  Dim oldb As ADODB.Connection

  Set oldbRecordset = New ADODB.Recordset
  Set oldb = New Connection

  oldb.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=N:\databases\gwi.mdb;Jet OLEDB:Database Password=xxxxx;"
   
  oldbRecordset.Open dbWhereCondition, oldb, _
            adOpenDynamic, adLockOptimistic
           
 
  If oldbRecordset.EOF And oldbRecordset.BOF Then
    'recordset is empty
  Else
    While Not oldbRecordset.EOF
      'you should be able to access the oldbRecordset.fields(...) collection here without error
 
      oldbRecordset.MoveNext
    Wend
  End If
     


  'reccountvar = 0

  'Do Until oldbRecordset.EOF
  '  reccountvar = reccountvar + 1
  'Loop
 
  'If reccountvar = 0 Then
  '     MsgBox "FATAL ERROR: CANNOT FIND THE LINE ITEM IN THE ORDER LINE TABLE"
  '     Exit Sub
  'End If
   
 
  dataline1 = ""
  dataline2 = ""
  ditemdisc = ""
  qandp = ""
  ditemqty = ""
  ditemprice = ""
 
ditemcat = oldbRecordset!item_category

***** ERROR 3021 when I try to reference oldbRecordset *****

can you upload the ms access db?

The recordset is appearing empty (e.g. BOF and EOF are the same) BUT, if I simply insert a msgbox to display ANYTHING, the recordset works. Remember there is only 1 record that will be retreived. Once I find the record and process the fields I will not need to do .MoveNext or any other navigation.

database file sent
gwi.mdb

coool.... without the password will be difficult :)

that's why i put the password in the description line of gwi.mdb :)   $kbios2

silly me :)

ok, I tried this:
  dbWhereCondition = "SELECT * FROM order_line WHERE order_num = 'ST01010000225' AND line_num = 1 "

and the code worked immediately (eof and bof returned false).
please tell me with what values you have a problem.

When I run the program eof and bof return true (they shouldn't). But if I simply use the select statement to create the recordset I get the 3021 error when I attempt to access the recordset.  When I used your logic:  While Not oldbRecordset.EOF    the recordset comes back with an EOF and BOF as true. I can't get to the single record.

well, as posted above, I tried with a specific set of variables, and it worked perfectly.
so, again, what are the values for order_num and line_num that you are using? ...

order_num and line_num are generated during the program. any order_num value that you found in the database was an order_num that was used and generated the error. although there are multiple lines per order the error occurs on the first line. so I too would have used ST01010000225 as an order_num and 1 as the line_num.

perhaps there is a reference or component that I'm missing that explains the reason yours works and mine doesn't?

one my end the code works when i add a msgbox (?) so the order_num and line_num data elements can be found in the database. it's just that when the code is ran without the msgbox the error occurs. what could the msgbox be doing that would allow it to work? a delay perhaps ? I'm going to add some delay process to see if this works. i'll be back.

i fixed it!!! i added a 500millisecond delay and the code works great! I started with a delay of 2000milliseconds the program ran fine, i reduced to 1000 everything is fine, i reduced to 500 still ok, i changed to 100milliseconds and the 3021 error occured. on my end i have to wait before processing.
i set back to 500 and all is well. can you shed any light as to WHY this may be happening? is there another (better) way to accomplish what i'm trying to do without having to 'sleep' for 500milliseconds?

not immediately before the select but pretty close. i create a tmp_line table to contain the line item data. once the order is complete i go through each line of the tmp_line and create the order_line table. the creation of the order_line table is done just prior to the actual receipt printing (the above code snipets is part of the receipt printing). so the creation of the row in the table just may need a little more time before I reference it.

I'm closing this question. Thanks for your help and expertise.

Thanks again. I enjoyed the exchange of ideas back and forth. I appreciate your help.

Use the DoEvents statement ALWAYS AFTER the ADODB objects.  

oldb.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=N:\databases\gwi.mdb;Jet OLEDB:Database Password=xxxxx;"
 DoEvents
   
  oldbRecordset.Open dbWhereCondition, oldb, _
            adOpenDynamic, adLockOptimistic, adCmdTableDirect
  DoEvents