By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

What if DoCmd.FindRecord can't find a record?

KirkT
P: 5
Hello everyone,

I am using the DoCmd.FindRecord to search a table and pull up a record. The way it is working right now, if it doesn't find the record, it still pulls of the form.

What I would like to do is put the DoCmd.FindRecord into an If statement and if it doesn't find a record, display a error message telling them to try again.

I have tried to put the Find Record into an If statement but it just gives me an error.

Here is the code that I am working wth:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOKButtton_Click()
  2. On Error GoTo Err_cmdOKButtton_Click
  3.  
  4.     ' Open the Edit FQT Test Log form
  5.     DoCmd.OpenForm "frm_Edit_FQT_Test_Log"
  6.  
  7.     Forms!frm_FQT_Test_Log!ID.SetFocus
  8.  
  9.     DoCmd.FindRecord Forms!frm_Test_Log_Record_Selection!Record_ID
  10.  
  11.     If DoCmd.FindRecord = True Then
  12.  
  13.     ' Set the focus on the System_ID field
  14.     Forms!frm_FQT_Test_Log!System_ID.SetFocus
  15.     ' Enable the bottom portion of the form
  16.     Forms!frm_FQT_Test_Log!TabCtl132.Enabled = True
  17.  
  18.     Else
  19.     DoCmd.Close
  20.     MsgBox "That record number does not exist, plase try again.", vbOKOnly + vbCritical, "An error has occured"
  21.  
  22. Exit_cmdOKButtton_Click:
  23.     Exit Sub
  24.  
  25. Err_cmdOKButtton_Click:
  26.     MsgBox Err.Description
  27.     Resume Exit_cmdOKButtton_Click
  28.  
  29. End Sub
  30.  
Any help would be appreciated.

Kirk
Jun 23 '17 #1

✓ answered by zmbd

Hello KirkT
You've hit on one of the limitations of the DoCmd.FindRecord (link to Syntax)
This function does NOT return any "user feedback" if no record is found.

One way to do test if the record is present is to open a recordset on your data.
If you are using a form, then a snapshot of the recordset may already available for your use provided the form is bound.

Another method is the Dlookup function (link to syntax) as it will at least return a null() value that you can check for.

-
From your code:

+It is not clear on what form "cmdOKButtton" event is launching.

+ "if it doesn't find the record, it still pulls of the form."
isn't clear; however, line 11 will error, see the link above for the command syntax, the remaining conditional will not execute.
> a debug/compile will catch this type of error.

+ Line 9 isn't clear; however, it appears that you are attempting to search against
"frm_FQT_Test_Log" in field "ID" for the value in the current record in "frm_Test_Log_Record_Selection" field "Record_ID"

line 19 needs to be more explicit in what it is closing.

>> I'm going to PM you a copy of a boilerplate that has some tutorial and other information, please check your
Bytes.com\inbox

So from the above information it may be possible to open a recordset on the underlying datasource for "frm_FQT_Test_Log" then use the findfirst method of the recordset to find the record, if it exists, and then move to that record in the "frm_FQT_Test_Log" form.

+++ a little hard to determine exactly what you are after here, there may be some better methods than what you are currently trying such as a parent/child form or other methods of searching.

Here's a very crude example against my test database using the recordset method.

In this instance what I have done here is created a very simple form with a text box control and a command button for the search and then I search against the form named "frm_people"

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command2_Click()
  5.   Dim zrs As DAO.Recordset
  6.   Dim zrs_clone As DAO.Recordset
  7.   Dim zsql As String
  8.   '
  9.   On Error GoTo zerrtrap
  10.   '
  11.   'trap for missing or null entries
  12.   If Me.zctrl_SearchTextBox & "" = "" Then Err.Raise Number:=(vbObjectError + 1), Source:="Command2_Click", Description:="An entry is required for this search to work"
  13.   '
  14.   'First make sure our form we're searching against is loaded and open or the code will error
  15.   If Not CurrentProject.AllForms("frm_people").IsLoaded Then DoCmd.OpenForm FormName:="frm_people"
  16.   '
  17.   'set a pointers to the recordset of form to be searched
  18.   'I only want to set the main recordset if it's not already opened... shouldn't be but I've seen strange things happen
  19.   If zrs Is Nothing Then Set zrs = Forms!frm_people.Recordset
  20.   Set zrs_clone = Forms!frm_people.RecordsetClone
  21.   '
  22.   ' create the SQL string used to find the record of interest
  23.   '>> very importain here to note that if we were using a string value in the field of interest
  24.   '>> we would have to have some additional quotes!
  25.   zsql = "data_pk=" & Me.zctrl_SearchTextBox
  26.   '
  27.   'using the cloned record set, zrs_clone, we're going to look up the record and if found then move
  28.   'form's record cursor to that position.
  29.   zrs_clone.FindFirst zsql
  30.   '
  31.   If zrs_clone.NoMatch Then
  32.     'if there's no matching record found then user feedback and close the form
  33.     MsgBox prompt:="There doesn't appear to be a record with that ID in the current records.", title:="No Match"
  34.     DoCmd.Close objecttype:=acForm, objectname:="frm_people", Save:=acSaveNo
  35.   Else
  36.     'if there is a matching record then move the form record cursor to that record and set the focus
  37.     zrs.Bookmark = zrs_clone.Bookmark
  38.     '
  39.     'note here, I rename the form controls, the default control name is the field name; however, this can create issues
  40.     With Forms!frm_people
  41.       .SetFocus
  42.       !zctrl_data_pk.SetFocus
  43.     End With
  44.   End If
  45.   '
  46. zcleanup:
  47. Stop
  48. 'cleanup step, ignore errors so-as to avoid a loop
  49. On Error Resume Next
  50.   'if the form is not loaded then we should close the pointers to the main recordset
  51.   'otherwise if we close the main recordset we will close that recordset on the form too
  52.   If Not CurrentProject.AllForms("frm_people").IsLoaded Then
  53.     If Not zrs Is Nothing Then
  54.       zrs.Close
  55.       Set zrs = Nothing
  56.     End If
  57.   End If
  58.   'it doesn't matter if we close the clone of the record set if the form is open so lets release the memory
  59.   If Not zrs_clone Is Nothing Then
  60.     zrs_clone.Close
  61.     Set zrs_clone = Nothing
  62.   End If
  63. Exit Sub
  64. zerrtrap:
  65.   MsgBox prompt:="Err Number " & Err.Number & vbCrLf & Err.Description, title:=Err.Source
  66.   Resume zcleanup
  67. End Sub
.

Clear as mud (@_@)
Never fear - we can help with that!
-Z

Share this Question
Share on Google+
2 Replies


zmbd
Expert Mod 5K+
P: 5,287
Hello KirkT
You've hit on one of the limitations of the DoCmd.FindRecord (link to Syntax)
This function does NOT return any "user feedback" if no record is found.

One way to do test if the record is present is to open a recordset on your data.
If you are using a form, then a snapshot of the recordset may already available for your use provided the form is bound.

Another method is the Dlookup function (link to syntax) as it will at least return a null() value that you can check for.

-
From your code:

+It is not clear on what form "cmdOKButtton" event is launching.

+ "if it doesn't find the record, it still pulls of the form."
isn't clear; however, line 11 will error, see the link above for the command syntax, the remaining conditional will not execute.
> a debug/compile will catch this type of error.

+ Line 9 isn't clear; however, it appears that you are attempting to search against
"frm_FQT_Test_Log" in field "ID" for the value in the current record in "frm_Test_Log_Record_Selection" field "Record_ID"

line 19 needs to be more explicit in what it is closing.

>> I'm going to PM you a copy of a boilerplate that has some tutorial and other information, please check your
Bytes.com\inbox

So from the above information it may be possible to open a recordset on the underlying datasource for "frm_FQT_Test_Log" then use the findfirst method of the recordset to find the record, if it exists, and then move to that record in the "frm_FQT_Test_Log" form.

+++ a little hard to determine exactly what you are after here, there may be some better methods than what you are currently trying such as a parent/child form or other methods of searching.

Here's a very crude example against my test database using the recordset method.

In this instance what I have done here is created a very simple form with a text box control and a command button for the search and then I search against the form named "frm_people"

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command2_Click()
  5.   Dim zrs As DAO.Recordset
  6.   Dim zrs_clone As DAO.Recordset
  7.   Dim zsql As String
  8.   '
  9.   On Error GoTo zerrtrap
  10.   '
  11.   'trap for missing or null entries
  12.   If Me.zctrl_SearchTextBox & "" = "" Then Err.Raise Number:=(vbObjectError + 1), Source:="Command2_Click", Description:="An entry is required for this search to work"
  13.   '
  14.   'First make sure our form we're searching against is loaded and open or the code will error
  15.   If Not CurrentProject.AllForms("frm_people").IsLoaded Then DoCmd.OpenForm FormName:="frm_people"
  16.   '
  17.   'set a pointers to the recordset of form to be searched
  18.   'I only want to set the main recordset if it's not already opened... shouldn't be but I've seen strange things happen
  19.   If zrs Is Nothing Then Set zrs = Forms!frm_people.Recordset
  20.   Set zrs_clone = Forms!frm_people.RecordsetClone
  21.   '
  22.   ' create the SQL string used to find the record of interest
  23.   '>> very importain here to note that if we were using a string value in the field of interest
  24.   '>> we would have to have some additional quotes!
  25.   zsql = "data_pk=" & Me.zctrl_SearchTextBox
  26.   '
  27.   'using the cloned record set, zrs_clone, we're going to look up the record and if found then move
  28.   'form's record cursor to that position.
  29.   zrs_clone.FindFirst zsql
  30.   '
  31.   If zrs_clone.NoMatch Then
  32.     'if there's no matching record found then user feedback and close the form
  33.     MsgBox prompt:="There doesn't appear to be a record with that ID in the current records.", title:="No Match"
  34.     DoCmd.Close objecttype:=acForm, objectname:="frm_people", Save:=acSaveNo
  35.   Else
  36.     'if there is a matching record then move the form record cursor to that record and set the focus
  37.     zrs.Bookmark = zrs_clone.Bookmark
  38.     '
  39.     'note here, I rename the form controls, the default control name is the field name; however, this can create issues
  40.     With Forms!frm_people
  41.       .SetFocus
  42.       !zctrl_data_pk.SetFocus
  43.     End With
  44.   End If
  45.   '
  46. zcleanup:
  47. Stop
  48. 'cleanup step, ignore errors so-as to avoid a loop
  49. On Error Resume Next
  50.   'if the form is not loaded then we should close the pointers to the main recordset
  51.   'otherwise if we close the main recordset we will close that recordset on the form too
  52.   If Not CurrentProject.AllForms("frm_people").IsLoaded Then
  53.     If Not zrs Is Nothing Then
  54.       zrs.Close
  55.       Set zrs = Nothing
  56.     End If
  57.   End If
  58.   'it doesn't matter if we close the clone of the record set if the form is open so lets release the memory
  59.   If Not zrs_clone Is Nothing Then
  60.     zrs_clone.Close
  61.     Set zrs_clone = Nothing
  62.   End If
  63. Exit Sub
  64. zerrtrap:
  65.   MsgBox prompt:="Err Number " & Err.Number & vbCrLf & Err.Description, title:=Err.Source
  66.   Resume zcleanup
  67. End Sub
.

Clear as mud (@_@)
Never fear - we can help with that!
-Z
Jun 24 '17 #2

NeoPa
Expert Mod 15k+
P: 31,186
Hi Kirk.

Just a thought, but many people think first of searching through records, when a filter is more database-centric and more efficient. If you're interested in that alternative approach you'll find some pointers at Example Filtering on a Form.

Otherwise, you can do a lot worse than working with Zmbd.
Jun 24 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.