Solved

No Current Record

Posted on 2001-07-03
10
241 Views
Last Modified: 2008-02-01
I have a VB app that I am developing which is essentially a Human interface to an Access database. I end up searching through various tables in the database using Data Object controls. I'll search for something using a command like this:

datExample.Recordset.Seek "=", searchstr$

If there is nothing found in this seek I get a dialog box that I can't control whose title bar reads: "Microsoft Visual Basic" and the message in the dialog box says: "No Current Record". There is a red dot with a white X in it and two buttons, OK and Help.

I know this is the correct response, I looked for something that wasn't there and it is telling me that.

This is my question:
Is there a way to make that dialog box go away, or is there a way to control it like you can other message boxes?  If I create a message box that appears if the recordset.nomatch property is True then I can make it say whatever I want but I still get the "No Current Record" dialog box before it. This makes the message box I can control seem redundant. It also adds clicks for my Operators.  Any thoughts

Thanks in advance for your help
-Gimola
0
Comment
Question by:Gimola
10 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6250469
You need to use On error goto... statement and manage this error there and optionally, show a message box or do other job that you want.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6250755
You seem to query the recordset before checking for NoMatch. Can you show us your code?
0
 
LVL 3

Expert Comment

by:sridhar_PJ
ID: 6251625
Hi Gimola,
If u don't want to handle the error or to avoid the error you can use
On Error Resume Next
datExample.Recordset.Seek "=", searchstr$
or you can handle the error like

datExample.Recordset.Seek "=", searchstr$
If datExample.Recordset.EOF Then
msgbox "No Records Found"
End if

Bye
Sridhar
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6252077
datExample.Recordset.Seek "=", searchstr$
if datExample.Recordset.nomatch then 'not found
   datExample.Recordset.movefirst
   'or movelast or whatever
else 'found
   'do whatever you want
   'probably just show it  
end if

the error you get is not because what you are seeking for is not found
it is because when a seek fails then there is no current record but you (or the bound fields) are trying to display one
 
0
 
LVL 1

Expert Comment

by:frankylew
ID: 6254835
I'll support sridhar PJ 's way to do,
use the .EOF and triggle a msgbox event,
design it in your way.

Cheers.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Gimola
ID: 6256654
I have tried all of your suggestions to no avail. Thank you for trying. Perhaps my description of the problem is lacking. I have a data control object on my form that connects me to an Access database table. The first field of this table is called ?TicketNumber?. When a search is initiated to find a certain Ticket Number that doesn?t exist in the table I get two dialog boxes. The first one is the Microsoft Visual Basic dialog box that says ?No Current Record?, then my dialog box displays because the Recordset.NoMatch property is True. I created a temporary button to recreate the problem easily and to test your solutions. This was the actual code:

Private Sub Command1_Click()

    'get string to be used in the TicketNumber field search
        searchstr$ = txtTicketNumber.Text
   
    'SEARCH LITHOACTUALS for requested Ticket
        datLithoTicketActuals.Refresh
        datLithoTicketActuals.Recordset.Index = "TicketNumber" 'use TicketNumber
        datLithoTicketActuals.Recordset.Seek "=", searchstr$ 'and search
           
        If datLithoTicketActuals.Recordset.NoMatch Then 'if no match
            prompt$ = "There is no Job Data for this Job"
            reply = MsgBox(prompt$, vbOKCancel, "No Job Data Records Found")
            datLithoTicketActuals.Refresh
        End If
End Sub

If I debug the code line by line I get the Microsoft Visual Basic dialog box right after the Seek command. I thought the Error GoTo suggestion was going to work. I tried using the Error GoTo like this:

Private Sub Command1_Click()
On Error GoTo ErrorHandler
    'get string to be used in the TicketNumber field search
        searchstr$ = txtTicketNumber.Text
   
    'SEARCH LITHOACTUALS for requested Ticket
        datLithoTicketActuals.Refresh
        datLithoTicketActuals.Recordset.Index = "TicketNumber" 'use TicketNumber
        datLithoTicketActuals.Recordset.Seek "=", searchstr$ 'and search
           
        If datLithoTicketActuals.Recordset.NoMatch Then 'if no match
            prompt$ = "There is no Job Data for this Job"
            reply = MsgBox(prompt$, vbOKCancel, "No Job Data Records Found")
            datLithoTicketActuals.Refresh
        End If
   
        Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & vbCrLf _
    & "Error Description: " & Err.Description
End Sub

Nothing changed, I still get both dialog boxes. Thanks again for your help.
-Gimola
0
 
LVL 6

Accepted Solution

by:
pierrecampe earned 200 total points
ID: 6257055
please read my earlier comment:
the error you get is not because what you are seeking for is not found
it is because when a seek fails then there is no current record but you (or the bound fields) are trying
to display one
when a seek fails *there is no current record*
and when you have controls bound to your datacontrol the datacontrol reases an error
if you do what you are doing and there are *no* bound controls you will not get that error
try the following but do *not* bind any control to the datacontrol
Private Sub Command1_Click()
    Data1.Recordset.Index = "TicketNumber"
    Data1.Recordset.Seek "=", searchstr$
    If Data1.Recordset.NoMatch Then
       MsgBox "not found"
       Data1.Recordset.MoveFirst
    End If
End Sub
and you will *not* get any error
presuming ofcource there are records in your table
the morale: do not use bound controls
also why are you doing all those refreshes

0
 

Author Comment

by:Gimola
ID: 6257145
The evaluation of the problem is correct. I have experimented with a Data Contol Object with and without bound controls, (assuming that an example of a bound control is a textbox with DataSource and DataFields set). If I set the DataField property to "" before I make my search then I don't have the problem. However on a large scale I haven't had success implementing same. I have never heard that you should not use bound controls. I am using them to get data into new records. I'm not sure how else I would go about doing that. I will consider other possibilities.

I have graded a B only because the solution to my problem was a "don't do that" type of an answer without really giving me direction as to what to do.

I do however appreciate the answer.
-Gimola
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6257353
>>without really giving me direction as to what to do<<
Oh i thought the following were very clear directions what to do
Private Sub Command1_Click()
   Data1.Recordset.Index = "TicketNumber"
   Data1.Recordset.Seek "=", searchstr$
   If Data1.Recordset.NoMatch Then
      MsgBox "not found"
      Data1.Recordset.MoveFirst
   End If
End Sub
and if you wanted to know how to work without bound controls all you had to do was ask
and all the dont does, i only included because you totally disregarded my first comment where i told you *exactly* what caused the error and what to do about it
0
 

Author Comment

by:Gimola
ID: 6259618
pierrecampe,
Did not mean to offend. Never disregarded your first comment. Your first comment had two solutions and an explaination. I tried the solutions, they had no effect. At the time, and even now to some degree, I was sketchy as to what a bound control was.
If you're going to be the expert, then be the expert. I did not give you a "bad" grade and you did get the points. Remember, I was the customer. I stand by what I said, you deduced the problem correctly: I have bound controls. If that is the problem then what is the solution? Don't use them? That is not an answer. The "clear" directions you gave me were only to prove that you identified the problem. I did ask for a solution, that's why I posted my question.

I am experimenting with ways around the problem and I'm certain I will get there from here. I don't expect any more help. I suppose I could always post another question, for more points, asking how not to work with bound controls. Until then I will go it alone.

There is no question that you are an expert. I am not. That's why I come here with questions. If you as an expert don't understand how your answer was recieved then how do you become a better expert?

I suppose that this comment could only serve to bother you and to not enlighten you at all. As I said, I really do appreciate the help. You were the only one of five experts that understood and explained my problem. I am better for it and my application will be better for it. That's because of you.

-Gimola

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now