当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - MS Access - VBA Do while TxtBox is Not Empty

问题描述:

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?

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: