MS Access - VBA Do while TxtBox is Not Empty

Question

Public Sub Ohno()
Dim stsql As String, results As String
Dim rs As Object, Db As Object, con As Object
Dim num As Integer
Dim start As Object

Set Db = CurrentDb()
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")

num = 1
For num = 1 To 2
  Do While IsEmpty(Forms("setup").Controls("TxtBoxEntry" & num)) = False
    Set start = Forms("setup").Controls("TxtBoxEntry" & num)
    stsql = "SELECT [Crosswalk].[Oracle GL Acct] FROM Crosswalk WHERE [Crosswalk].[Legacy GL Acct]= '" & start & "' "
    rs.Open stsql, con
    results = rs(0).Value
    Forms("setup").Controls("TxtBoxRslt" & num).Value = results
  Loop
Next

Set con = Nothing
Set rs = Nothing

I keep getting: Operation isn't allow while Object is Open - click me the code does work for the first txtbox and stops to give me the above error. Am I setting up myself for failure on this one?

Answer 1

You need to close the recordset after using it. Try adding rs.Close:

Set start = Forms("setup").Controls("TxtBoxEntry" & num)
stsql = "SELECT [Crosswalk].[Oracle GL Acct] 
         FROM Crosswalk WHERE [Crosswalk].[Legacy GL Acct]= '" & start & "' "
rs.Open stsql, con
results = rs(0).Value
Forms("setup").Controls("TxtBoxRslt" & num).Value = results
rs.Close   -- Add this here