Testing for existance of a record

[chris]

How can I test for the existance of a record before a "DoCmd.OpenForm
etc" to avoid applying it to non-existant record. I am using an
unbound text box (txtFindRecNum) on a search form to get the number
which is the primary key (ProjectID) of the table (tblProject) on
which the form is based. I'm sure it's very simple but I just can't
see how to do it.
Chris

 

[Allen Browne]

You could perform a DLookup() to see if there is anything at all in the
primary key before you open the table, e.g.:
If Not IsNull(DLookup("ProjectID", "tblProject")) Then

Alternatively, you could cancel the Open event of the form if it has no
records:
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "Nobody home"
End If

 

[Roger Carlson]

There are a number of ways. Probably the simplest is to use the DLookUp
function.

Something like:

If Not IsNull (DLookup("ProjectID", "tblProject", "[ProjectID]= " &
txtFindRecNum)) Then
DoCmd.OpenForm...
End If

If it can't find the value in the table, it will return a null.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[BruceM]

Rather than using a text box you may be able to use a combo box with the row
source listing only valid records. Can you provide more details? Fopr
instance, are you searching by the ProjectID number, or what exactly? What
sort of information would you type into the text box to initiate the search?

If you stay with the text box you could use DCount to check for the
existence of a record (although I suspect there is a better way that
DCount), and open the form only if DCount >= 1. However, limiting the user
to valid choices only is apt to be more effective.

 

[benyod79 via AccessMonster.com]

Chris, there are a couple different ways to do this. I aircode one and see if
it works for you....

'---------------------------------------------------------------------
dim rstProject as Recordset
dim lnRecNum as Long

lnRecNum = Inputbox("Find Record Number....") 'There's some other stuff
you can do here too, but I don't remember

Set rstProject = CurrentDB.OpenRecordset("tblProject", dbopensnapshot)

rstProject.FindFirst "[ProjectID] = " & lnRecNum

If rstProject.NoMatch then
Msgbox "This Record Number doesn't exist"
Else
Msgbox "This Record Number Exists"
End If

rstProject.close
set rstProject = Nothing
'-----------------------------------------------------------------------------

 

[chris]

Thanks everyone - the Dlookup method to be the simplest and does just
what I want.
Chris