Does not exist query??

[SgtMonty]

I have a table that hold numeric codes. Example:

Code
1000
1002
2000
2394

On a form I request for the code number. I need help to construct a query that if the code does not exist, it will return a message that the code does not exist. For example the code 1111 is not on the list.

I already have a code that if the individual enters a code that has more than 4 characters in length it will message that the code is invalid, the same goes if the code is null or less than 0.

 

[MaxieMax]

if your using an unbound form you could always query the table for the code value entered and then open the recordset and if is null then msgbox with the message saying code doesn't exist.

 

[SgtMonty]

Can you give me an example. How?

 

[SkipVought]

Select Code
From YourTable


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MaxieMax]

where you test for the length of the code... i.e. 4 characters long build an sql string and then open recordset....

you need to create a couple of dimensions if you haven't already

dim db as dao.database
dim rs as dao.recordset
dim strSQL as string

then this code in your test

set db = currentdb

strsql = "select * from tablename_holding_code where code =" & field_name_from_form

set rs = db.openrecordset (strsql,dbopendynaset)

if rs.eof then msgbox "Code Not Found" else msgbox"Code Found"

rs.close
set rs = nothing

db.close
set db = nothing

hope this helps

 

[PHV]

Why not simply use the DLookUp method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SgtMonty]

Thanks for the help. Based on my data can you give me an example.

 

[SgtMonty]

This is what I wrote:

Dim db As dao.Database
Dim rs As dao.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "select * [Time_In] =" & Code
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.EOF Then MsgBox "Code Not Found" Else MsgBox "Code Found"
rs.Close
db.Close
Set rs = Nothing

Set db = Nothing

This is the error that I get:
Syntax error (missing operator) in query expression

RunTime error 3075

 

[BigRed1212]

strSQL = "select * [Time_In] =" & Code

That probably needs to be a well formed SQL query, something more like:

strSQL = "select * from yourtable where [Time_In] =" & Code

 

[dhookom]

On a form I request for the code number

Why not use a combo box to select the code number and limit it to the list? If that doesn't work then you can use PH's suggestion:

If DCount("*","[Time_In],"Code =" & Me.Code) = 0 Then
   MsgBox "The code " & Me.Code & " can't be found."
End If

Duane
Hook'D on Access
MS Access MVP