How to check if Work order exist through a query to the database

[MNJoe]

Not sure if this should go here or in Queries.

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

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. 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 "Work_Order_Exist" query 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 is below. 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

Set rstWork_Order = New ADODB.Recordset

strWO_ID = Trim(Me.Work_Order_ID.Text)

MsgBox strWO_ID

??????????????????????????

blnExist = rstWork_Order.EOF

MsgBox blnExist

rstWork_Order.Close
Set rstWork_Order = Nothing


End Sub

 

[Tom van Stiphout]

On Mon, 26 Jan 2009 14:06:06 -0800, MNJoe

Once you have a linked table, you can query it like any other table.
There is no need to use ADO for this. Unless there are 10,000 +
records in the table I would use DLookup. And rather than LostFocus,
use the <control>.AfterUpdate event. And also add Option Explicit to
each module (you should set that to be the default in Options).
The one-liner is:
blnExist = Not IsNull(DLookup(“Work_Order_ID”, "myTable",
“Work_Order_ID = ” & Me.myTextbox))
(of course you change myObjectNames to yours)
The idea of this code is that we are using DLookup to find the ID
value. It will return null if not found.

-Tom.
Microsoft Access MVP

 

[MNJoe]

Thank you

From your suggestion I was able to get it. what I came up with is below.



Dim blnExist As Boolean


blnExist = Not IsNull(DLookup("[BASE_ID]", "SYSADM_WORK_ORDER", "[BASE_ID] =
' " & Me.txtWork_Order_ID & " ' "))

MsgBox blnExist