Invalid Procedure or Call error

[Samibouni]

Hello

I've set up an address book in VB. It has a control array of command buttons labeled a-z, that move the recordset to the first surname beginning with the letter on the button. This enables people to move quickly through thousands of addresses.

This was working well, Until I had to add clients with no surnames (businesses, for example). However, when this happens, I get an Invalid procedure or call arguement.

My code is below.

Can anyone suggest a quick fix?

Private Sub Command1_Click(Index As Integer)
 
rec3ClientDetails.MoveFirst
 
Do Until Asc(Left(rec3ClientDetails("Surname"), 1)) = Index + 65
   If Asc(Left(rec3ClientDetails("Surname"), 1)) > Index + 65 Then Stop
     
     rec3ClientDetails.MoveNext
      
Loop

Thanks

Sam

[vbud]

Consider using :

"" & rec3ClientDetails("Surname").Value

instead. Moreover why not not test for blank surname field instead of ASCII values?

[honeybee]

Better still, change your Do loop structure. If a field is null, your loop will most certainly fail. To use the idea suggested earlier means you will have to use it in all the statements that accesses the field. A better idea would be to use a loop that goes on forever, break it manually, and inside the loop, use a string variable to store the value in the field.

'Your Code, with null validation
Private Sub Command1_Click(Index As Integer)
 
rec3ClientDetails.MoveFirst
 
Do Until Asc(Left(rec3ClientDetails("Surname") & "", 1)) = Index + 65
   If Asc(Left(rec3ClientDetails("Surname") & "", 1)) > Index + 65 Then Stop
     
     rec3ClientDetails.MoveNext
      
Loop

It needs to have the & "" part in every statement that accesses the field. Instead you could re-write the code as:

Private Sub Command1_Click(Index As Integer)
Dim tmpStr As String
rec3ClientDetails.MoveFirst
 
Do While True
tmpStr = rec3ClientDetails("Surname") & ""
If Asc(Left(tmpStr, 1)) <> Index + 65 Then
    Exit Do
Else
    If Asc(Left(tmpStr, 1)) > Index + 65 Then
        Exit Do
    End If
End If
rec3ClientDetails.MoveNext
Loop
End Sub

Also I hope you know what the "Stop" statement does in the code. When you are running the code through the VB IDE, the Stop statement will break the code or act as a breakpoint. However in a compiled EXE, it will simply terminate the app.

.

[Samibouni]

Thanks for your comments, they are most insightful.

However, I am still getting the same error. This time, however, VB's debugger highlights the:

If Asc(Left(tmpStr, 1)) <> Index + 65 Then

piece of code. Is there another reason for this besides the blank string or is that it?

[honeybee]

Why not simply test it yourself, by putting a non-empty string in there????



.

[Samibouni]

Thanks for your reply.

Although I was panicking a bit, I decided to look at the problem once more. I was getting the invalid procedure or call error (5) which said that the value of one of my inputs was greater than allowed.

This confused me. It led me to wonder how vb interprets a null string. So, instead of getting it to look at one of those I put a space in the recordset. " " instead of "".

In this way, I took your code version of my code with the null validation:

Private Sub Command1_Click(Index As Integer)
 
rec3ClientDetails.MoveFirst
 
Do Until Asc(Left(rec3ClientDetails("Surname") & "", 1)) = Index + 65
   If Asc(Left(rec3ClientDetails("Surname") & "", 1)) > Index + 65 Then Stop
     
     rec3ClientDetails.MoveNext
      
Loop

and changed it to:

rec3ClientDetails.MoveFirst
 
Do Until Asc(Left(rec3ClientDetails("Surname") & " ", 1)) = Index + 65
   If Asc(Left(rec3ClientDetails("Surname") & " ", 1)) > Index + 65 Then Exit Do
     
     rec3ClientDetails.MoveNext
      
Loop

& whaddya know? By luck, perhaps rather than judgement, it began to work.

Did I do good?

Sam