ODBC connection not returning records. MNJoe 11/20/2008 8:59:01 PM 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 -- MNJoe

Re: ODBC connection not returning records. "AccessVandal via AccessMonster.com" <u18947[ at ]uwe> 11/21/2008 3:36:28 AM 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? MNJoe wrote: [Quoted Text] >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 > -- Please Rate the posting if helps you Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1