ODBC connection not returning records.

[MNJoe]

Office 2003 on windows XP pro. A form with a text box where the user enters
in the Work Order ID’s.

I have a SQLBASE server. I can connect to it using an ODBC connection. In
Access I use the “Get External Dataâ€, click on the “Link Tables†and on the
very bottom select from the drop down list “Files of Type†I select “ODBC
connections†and then select my machine data source.

I then can connect and select the tables and bring them into Access. No
problem there. I have created in the “Queries†a very simple query called
“Work_Order_Exist†I have 1 table with the “Work_Order_ID†column selected.

What I would like to do is using the ODBC connection check if the work
order exist for every work order id entered into the text box. What I have so
far for the General declarations and the txtWork_Order_ID_LostFocus sub. I am
not completely sure how to use the ODBC connection to do this and have not
found anything any where that discusses this very well. Could someone point
me in the right direction.

Option Compare Database
Dim rstWork_Order As ADODB.Recordset


Private Sub Work_Order_ID_LostFocus()

Dim blnExist As Boolean
Dim strWO_ID As String
Dim strSql_Statement As String

Set rstWork_Order = New ADODB.Recordset

strWO_ID = Trim(Me.Work_Order_ID.Text)
'MsgBox strWO_ID
strSql_Statement = "Select * from SYSADM_WORK_ORDER where WORK_ORDER_ID = "
& strWO_ID

' had the DoCmd.RunSql and it always came back with true for EOF ??
' even if I hard coded into the Sql Statement a valid Work Order ID

blnExist = rstWork_Order.EOF

MsgBox blnExist

rstWork_Order.Close
Set rstWork_Order = Nothing


End Sub

 

[AccessVandal via AccessMonster.com]

Docmd.RunSql does not return any recordset. Therefore "rstWork_Order.EOF" is
always "True".

You should open the recordset than check EOF. Like...watch for word wrap.

rstWork_Order.Open "Select * from SYSADM_WORK_ORDER where WORK_ORDER_ID = "
& strWO_ID, CurrentProject.Connection,adOpenDynamic,adLockOptimistic

blnExist = rstWork_Order.EOF 'check True or False
MsgBox blnExist 'False if exist, True if None.

How did you get it to work without openning the record? I mean "rstWork_Order.
EOF", is there any code in the form with this recordset object that is still
not close?