Find record on AfterUpdate Event 1

[GelC]

Hi all,
I would like to modify my code to have a pop-up message saying "No record found" if the text enter unmatch record in database.
Thanks in advance.

Private Sub ReportNum_AfterUpdate()
    DoCmd.OpenForm "frmAllReports", , , "[ReportNum] = '" & Me.ReportNum & "'"
    DoCmd.Close acForm, "frmReportSelector"
End Sub

 

[fneily]

You could add something like:
Dim R As DAO.Recordset
Dim UserSelection
Set R = CurrentDb.OpenRecordset("Select * From [tablename] Where [ReportNum] = " & Me![ReportNum])
If R.RecordCount = 0 Then
UserSelection = MsgBox("No Record Found")

 

[GelC]

I use the code as your suggestion.
However, it can not even open the record that's in the table.
What I did wrong?

 

[gwinn7]

Even faster...

Dim rs as dao.recordset

set rs = currentdb.openrecordset( _
"SELECT myfield FROM mytable WHERE reportnum = " & Me.Reportnum, dbopensnapshot)

If rs.bof = true then
msgbox "No Record Found!", vbokonly
end if

Explanation...

1. Perform your select lookups returning ONE field. If you use the *, then it will return extra data you don't need.
2. Use the 'dbopensnapshot' constant. DAO openrecordset defaults to dbopendynaset which has more overhead. dbopensnapshot is a faster lookup.
3. Avoid using .Recordcount, its not reliable for large sets! All you need is .BOF.
4. Avoid Dlookup for large sets. It is known to be a slower function.

Gary
gwinn7

 

[GelC]

How come it's not working for me.

Private Sub ReportNum_AfterUpdate()

Dim rs As dao.Recordset

  Set rs = CurrentDb.OpenRecordset( _
  "SELECT ReportNum FROM tblAllReports WHERE ReportNum = " & Me.ReportNum, dbOpenSnapshot)

  If rs.BOF = True Then
    MsgBox "No Record Found!", vbOKOnly
  End If
End Sub

 

[PHV]

You may try something like this:

Private Sub ReportNum_AfterUpdate()
If IsNull(DLookUp("ReportNum", "tblAllReports", "ReportNum='" & Me!ReportNum & "'")) Then
    MsgBox "No record found"
Else
    DoCmd.OpenForm "frmAllReports", , , "[ReportNum] = '" & Me.ReportNum & "'"
    DoCmd.Close acForm, "frmReportSelector"
End If
End Sub

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

 

[GelC]

Work like a charm.
Thank you very much, PHV

 

[fneily]

gwinn7 - besides theoretical explanations, can you supply any actual time readings for points 2 and 4?
Also an example of point 3?
That way I can actually show others that it's true.

 

[PHV]

Avoid Dlookup for large sets. It is known to be a slower function
Even when the criteria is applied to an indexed field ?

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

 

[gwinn7]

GelC,

I don't know why its not working for you. If you provided an error message and which line it is having trouble with, I could help you. One shot in the dark possible problem is that your currentdb may be utilizing the ADO library?

fneily,
I don't have time readings to give at the moment. If you are so eager to prove either way, why don't you give it a shot? Its just been my experience that dlookup can be slower than the solution I provided above.

If you want to continue to use dlookup, by all means, do so. It works and doesn't fail. I am just trying to contribute good tips for all programmers here. If you Google Dlookup and other performance keywords, you might find some interesting answers.

Gary
gwinn7