Woka your the best ** Resolved **ADO recordset BOF/EOF

**holly** · May 13th, 2003, 03:32 AM

Hi

I have a routine which validates and prints a user input but if
they go back lets say they entered the wrong info I receive and
error when I try to refresh the results.

lets say example
user inputs id - 125336
and a shift as 5

the results printed are like so
5 125336
Harris

but lets say they entered the wrong shift so they press the back button and reenter the shift this is were I am reciving the error
my code will not let me repaint new input...

VB Code:

Sub cmdbutshft_click(index As Integer)
   'Remember that the first CommandButton in the array is
'ALWAYS 0. commandbutton array to determine the packers shift
    Dim intCmd As Integer
       If index = 0 Then
          Strshiftletter = Trim$("1")
        ElseIf index = 1 Then
         Strshiftletter = Trim$("5")
        ElseIf index = 2 Then
         Strshiftletter = Trim$("3")
        ElseIf index = 3 Then
         Strshiftletter = Trim$("4")
    End If
     
     Select Case m_Intwndprogress
        
        Case Is = 10
                txtinformation.Text = Strshiftletter
                progressbar_login
          
            If Not ObjRSuser.EOF Then
                PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter; ObjRSuser.GetString(, , vbCrLf)
                txtinformation.SetFocus
                txtinformation.maxlength = 11
                txtinformation = ""
            End If
            If ObjRSuser.BOF = False Or True Then
            PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter; ObjRSuser.GetString(, , vbCrLf)
                  txtinformation.SetFocus
                txtinformation.maxlength = 11
                txtinformation = ""
        End If
            
   End Select
   For intCmd = 0 To 3 ' Disable caption for each cmd button
            cmdbutshft(intCmd).Caption = ""
             Strshiftletter = vbNullString
   Next intCmd
      Lbltext.Caption = Trim$("Please Enter/code")
       validate_product_code
 End Sub

Thanks

**Wokawidget** · May 13th, 2003, 03:51 AM

Errr...not quite sure what ya codes doing, but I can tell you that:

VB Code:

If ObjRSuser.BOF = False Or True Then

Is definately a bit strange...that is statement will ALWAYS be true!
I think you may want to change this line to:

VB Code:

If NOT objRSUser.BOF And Not objRSUser.EOF Then

Hope that helps,

Woka

**holly** · May 13th, 2003, 04:07 AM

Woka - thanks for your suggestions but unfortunatly I'm still
receiving the same error....either eof or bof is true......

Have you anymore suggestions!!!

**Wokawidget** · May 13th, 2003, 04:29 AM

Ok, how about:

VB Code:

If NOT (objRSUser.BOF Or objRSUser.EOF) Then

If either of them is TRUE then the IF statement will be FALSE and will not fire the code off to print the details to your form...Actually, the line of code I posted prior to this post does exactally the same...

What line of code are you getting the error on???

Woka

**holly** · May 13th, 2003, 04:33 AM

The line of code I geeting the error on is

VB Code:

If Not ObjRSuser.BOF And Not ObjRSuser.EOF Then
                PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter; ObjRSuser
                txtinformation.SetFocus
                txtinformation.maxlength = 11
                txtinformation = ""
        End If

The second if statement this is when I try to repaint the new objrsuser and shift values.

Thanks

**Wokawidget** · May 13th, 2003, 05:42 AM

So the error is on:

VB Code:

ObjRSuser.EOF

???
Woka

**holly** · May 13th, 2003, 05:48 AM

it appears so!!!!

**holly** · May 13th, 2003, 06:01 AM

Woka

It appears to be

VB Code:

ObjRSuser.GetString(, , vbCrLf)
of the second if statement

the error occurs when I try to print out the objrsuser results
for a second time...

Have you any ideas

thanks

**swatty** · May 13th, 2003, 06:50 AM

Have you placed wolka's suggestion instead of the second if statement ?

**Wokawidget** · May 13th, 2003, 06:58 AM

I am confused...

What code are you using NOW for the IF statement...?
Which line EXACTALLY is causing the error?
What is the error number, and description?

Woka

**holly** · May 13th, 2003, 07:12 AM

This is my code

VB Code:

Sub cmdbutshft_click(index As Integer)
   'Remember that the first CommandButton in the array is
'ALWAYS 0. commandbutton array to determine the packers shift
    Dim intCmd As Integer
       If index = 0 Then
          Strshiftletter = Trim$("1")
        ElseIf index = 1 Then
         Strshiftletter = Trim$("5")
        ElseIf index = 2 Then
         Strshiftletter = Trim$("3")
        ElseIf index = 3 Then
         Strshiftletter = Trim$("4")
    End If
     
     Select Case m_Intwndprogress
        
        Case Is = 10
                txtinformation.Text = Strshiftletter
                progressbar_login
          
          If Not ObjRSuser.EOF Then
                PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter; ObjRSuser.GetString(, , vbCrLf)
                txtinformation.SetFocus
                txtinformation.maxlength = 11
                txtinformation = ""
         End If
        If (ObjRSuser.BOF = True Or ObjRSuser.EOF = True) Then
             [quote][b]   PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter; ObjRSuser.GetString(, , vbCrLf) [/b][/quote][b][/b]
                txtinformation.SetFocus
                txtinformation.maxlength = 11
                txtinformation = ""
         End If
   
   End Select
   For intCmd = 0 To 3 ' Disable caption for each cmd button
            cmdbutshft(intCmd).Caption = ""
             Strshiftletter = vbNullString
   Next intCmd
      Lbltext.Caption = Trim$("Please Enter code")
       validate_product_code
 End Sub

now the line which is giving me the error is

VB Code:

PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter; ObjRSuser.GetString(, , vbCrLf)

The error is
2031 - either eof or bof is true, current record has been deleted -
well it is something on these lines......

now what I was thinking is the objrsuser has already got the
values I need printing, so I thought the error must be occuring
within the second

if statement

VB Code:

ObjRSuser.GetString(, , vbCrLf)

Thanks
The second IF statement allow me to reprint the new values...after
changing the shift values...

**Wokawidget** · May 13th, 2003, 07:18 AM

Errrrr....?!
You have:

VB Code:

If (ObjRSuser.BOF = True Or ObjRSuser.EOF = True) Then

So of course it's going to give you an error because the recordset is either at the beginning of the file, or at the end of the file...

Like I posted twice before you need either:

VB Code:

If NOT objRSUser.BOF And Not objRSUser.EOF Then

OR

VB Code:

If NOT (objRSUser.BOF Or objRSUser.EOF) Then

Not having a go or anything, but I can't really help you if you type random code into your app instead of the code I suggest...I was well confused because the code I posted should handle this error, but you said you still got it

But you haven't used my code...

Replace you IF statement with either of the 2 I posted. They are exactally the same, just written differently...

Woka

**holly** · May 13th, 2003, 07:28 AM

Woka

I think we are getting our wires crossed....I have tried your code

If NOT objRSUser.BOF And Not objRSUser.EOF Then

and It does not work .......this is why, when I post my code
you're suggestions are not visible.....

ok, the error has been taken care of but I need to execute the
code within the IF STATEMENT inorder to print out the new
shift values....

Thanks for youre help

**Wokawidget** · May 13th, 2003, 07:39 AM

You can't....
If either the recordsets EOF or BOF is TRUE, then it's not pointing at a current record, thus you will get an error...Do you have .MoveNext or .MovePrevious at all in your code?

How do you scroll through the code...?

My code did work, it stopped the error, but it didn't display and text, this is down to another problem further up the line...

Can you post the entire form on here please, and I'll take a little look for you.

Woof

**holly** · May 13th, 2003, 07:46 AM

Woka

Youre very kind....I have attached the form and remove confidential info...

Thanks for taking the time to check out my code...Please feel free
to comment!!!!!

**Wokawidget** · May 13th, 2003, 08:26 AM

Right...where to start...Hmmmmmmm...

1stly, get rid of that END statement....

Bad, bad, bad command
Your UnLoad event destroys all your objects and other forms, so there is no need for the END command...

Also...change all you buttons for the numbers to an control array:

VB Code:

Private Sub cmdNumber_Click(Index As Integer)
    With txtInformation
        .Text = Trim$(.Text) & CStr(Index)
        .SelStart = Len(.Text)
        .SetFocus
    End With
End Sub

Does that make sense?

change your .Open statement to:

VB Code:

.Open strsql, m_objoCn,adOpenForwardOnly,adLockReadOnly

This is makes the code faster...and does not lock the database...

I can't see how that Sub is fired off if you click the back button

I think it might be because when you click the back button is resets the txtInformation to vbNullstring ("")

Woka

**holly** · May 13th, 2003, 08:53 AM

Woka - I know youre probably fed up of me by now but
Thanks - I have made the changes you've recommended....Just as
a matter of interests....what do you think of the overall code...
but, bear in mind, I'm new to VB.....

Going back to the original problem...if I place this in my second
if statement then the code executes...(is this bad programming)

VB Code:

If ObjRSuser.BOF = False And ObjRSuser.EOF = True Then
               PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter
                txtinformation.SetFocus
                txtinformation.maxlength = 11
                txtinformation = ""
         End If

It will reprint the shift letter but I also need the objrsuser object..
to print....why does it seem as though the objrsuser values are
lost

Once again thanks for all your help!!

**holly** · May 13th, 2003, 10:00 AM

Please Help!!!!!!!

Going back to the original problem...if I place this in my second
if statement then the code executes...(is this bad programming)

visual basic code:--------------------------------------------------------------------------------If ObjRSuser.BOF = False And ObjRSuser.EOF = True Then
PicResultsOperator.Print "Packers_ID"; vbCrLf; StrshiftletterObjRSuser.GetString(, , vbCrLf)
txtinformation.SetFocus
txtinformation.maxlength = 11
txtinformation = ""
End If
--------------------------------------------------------------------------------

It will reprint the shift letter but I also need the objrsuser object..
to print....why will the objrsuser values not print!!!

Thanks

**Wokawidget** · May 13th, 2003, 10:07 AM

Just before that IF statment add:

VB Code:

MsgBox objRSUser.RecordCount

What number is displayed in the MSgBox???

Woka

**holly** · May 13th, 2003, 10:25 AM

Woka

the number returned is -1

why???

**Wokawidget** · May 13th, 2003, 10:52 AM

Because I want to see what the record count is for that recordset. I have no idea if you have records in there...it could be empty for all I know

Try changing you .OPEN statement back to what you had it as then run again and tell me what the recordcount is...

Woka

**demotivater** · May 13th, 2003, 10:59 AM

Open using adopenkeyset, that should give a record count.

**holly** · May 13th, 2003, 10:59 AM

The value has changed to 1

**Wokawidget** · May 13th, 2003, 11:15 AM

OK, that's good...
Change your IF statement code to

VB Code:

If ObjRSuser.RecordCount = 1  Then
                objRSUser.MoveFirst
                PicResultsOperator.Print "Packers_ID"; vbCrLf; Strshiftletter; ObjRSuser.GetString(, , vbCrLf)
                txtinformation.SetFocus
                txtinformation.maxlength = 11
                txtinformation = ""
         End If

That should do the trick...

Woka

**holly** · May 13th, 2003, 11:43 AM

Woka

I will have to test your solution in the morning - I will let you know
how I get on (fingers are crossed)

in the meantime I would
like to thankyou for youe patience and especially your help!

Thanks again

PS See ya tomorrow - I know what youre think....not if I see you
first (ha, ha)

**holly** · May 14th, 2003, 01:43 AM

Woka - what can one say except a big thankyou...

Maybe my
code is alot to desire but you managed to fix and

thanks a
million for having the patience.......

Thanks again for all your help!!!!

**Wokawidget** · May 14th, 2003, 03:07 AM

No problems...

Did you get my PM?

Woka

Thread: Woka your the best Resolved ADO recordset BOF/EOF

Thread Tools

Display