MS Access continue execution of loop when no return from SQL

Posted on 2012-09-15
Last Modified: 2012-09-27

I have this bit of code:
Set rst3 = dbs.OpenRecordset("SELECT Customer.[ListID], Customer.[Name], Customer.[FullName] " _
& "FROM Customer " _
& "WHERE (((Customer.[Name])='" & student_name & "'));")
sCustomerListID = rst3!ListID

Sometimes though one some records nothing is returned and the program stops execution. How do I continue execution eventhough nothing is returned from the code above?

Question by:Victor Kimura
    1 Comment
    LVL 61

    Accepted Solution

    Check the recordcount prior to doing anything that needs values from your recordset:

    Set rst3 = dbs.OpenRecordset("SELECT Customer.[ListID], Customer.[Name], Customer.[FullName] " _
    & "FROM Customer " _
    & "WHERE (((Customer.[Name])='" & student_name & "'));")
    if rs.Recordcount = 0 then
         msgbox "No records"
         Exit sub                ' or do whatever else is needed
           sCustomerListID = NZ( rst3!ListID )     '<----- Also change this line as shown to handle nulls
    End if 

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now