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



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

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.

Microsoft Access MVP


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question