Query - Form Help

[tpearo]

I have a form that is based on a query from a table. The table has a auto number field called RM_ID#. The query asks for the RM_ID# to open the form and display the appropriate record. However if a user types in a number that is not in the table the form opens, yet none of the fields display and the user can't get out. Is there a way that if the record or RM_ID# that the user types in does not exist that the form does not open at all.

 

[nomus1]

Have you tried adding an on open event

execute the query for the form including the filter
count the results the query returns
if results = 0
then show a message box that says invalid number and close the form.
if the query has a result then open the form as normal?

If you need any help with this, I can send you an example

 

[tpearo]

I don't quite understand what you mean. Could you explain a little more?

Thanks.

 

[nomus1]

In the on open event for the form do something like this:

dim sql as string
dim rst as recordset
dim db as database
dim holdcnt as integer

sql = "select * from table name where rm_id# = " & id# user selected

db = currentdb()
rst = db.openrecordset(sql)

holdcnt = rst.recordcount

if holdcnt = 0 then
msgbox("No Records Found for ID# " & id# user selected)
close_form
end if

set rst = nothing
set db = nothing

me.recordsource = sql

 

[tpearo]

I have tryed this code in the open event and I get the following error "User-defined type not defined". This error seems to be related to the line

dim db as database

Any ideas?

 

[nomus1]

Try
set db = currentdb()
set rst = db.openrecordset(sql)