468,306 Members | 1,138 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,306 developers. It's quick & easy.

Runtime error 2137: You can't use find or replace now.

I have created a form to allow users to add a new record and/or search for an existing record based on SSN. If the SSN the user enters into SrchID_text is found, the form is populated and a message appears telling the user that the record exists and they can edit existing information or add new information. If the SSN is not found, a new record is supposed to be created and a message appears telling the user that no match was found and they can begin entering data.

My problem occurs when there are no records in the database.

How do I get around this?

Please see code below:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub SrchID_But_Click()
  3.     Dim strStudentRef As String
  4.     Dim strSearch As String
  5.  
  6. 'Check SrchID_text for Null value or Nill Entry first.
  7.  
  8.     If IsNull(Me![SrchID_Text]) Or (Me![SrchID_Text]) = "" Then
  9.         MsgBox "Please enter a SSN to search for!", vbOKOnly, "Invalid Search Criterion!"
  10.          Me!SrchID_Text.SetFocus
  11.     Exit Sub
  12. End If
  13. '---------------------------------------------------------------
  14.  
  15. 'Performs the search using value entered into SrchID_text
  16. 'and evaluates this against values in SSN
  17.  
  18.     DoCmd.ShowAllRecords
  19.     DoCmd.GoToControl ("SSN")
  20.     DoCmd.FindRecord Me![SrchID_Text]
  21.  
  22.     SSN.SetFocus
  23.     strStudentRef = SSN.Text
  24.     SrchID_Text.SetFocus
  25.     strSearch = SrchID_Text.Text
  26.  
  27. 'If matching record found fills in data and
  28. 'sets focus on participant's first name and
  29. 'clears search control
  30.  
  31.     If strStudentRef = strSearch Then
  32.         MsgBox "Match Found For: " & strSearch & vbCrLf & " " & vbCrLf & "Please edit or add data as needed.", , "Existing Record Found"
  33.         F_Name.SetFocus
  34.         SrchID_Text = ""
  35.  
  36.     'If value not found sets SSN equal to searched SSN and
  37.     'informs user to begin entering data and sets focus on
  38.     'F_Name
  39.  
  40.     Else
  41.            MsgBox "Match Not Found For: " & strSearch & vbCrLf & " " & vbCrLf & "Please begin entering data for this new participant.", _
  42.             , "New Participant!"
  43.             DoCmd.GoToRecord , , acNewRec
  44.             SSN.SetFocus
  45.             SSN.Text = strSearch
  46.             LWIA_Combo.SetFocus
  47.  
  48.     End If
  49.  
  50. End Sub
  51.  
  52.  
Nov 19 '07 #1
5 8695
Rabbit
12,512 Expert Mod 8TB
Me.Recordset.RecordCount will let you know if you have at least one record.
Nov 19 '07 #2
Exactly how would I incorporate this into my code so that when a user enters the very first record, it will add a new record using the SSN entered?
Nov 20 '07 #3
Rabbit
12,512 Expert Mod 8TB
You mean like a If Me.Recordset.RecordCount = 0 Then ...?
From your code you seem to know how to use an If/Then statement.
Nov 20 '07 #4
Yes I obviously do... However, I have tried various placements of the If me.recordset.recordcount = 0 then statement and nothing works
Nov 20 '07 #5
Rabbit
12,512 Expert Mod 8TB
Yes I obviously do... However, I have tried various placements of the If me.recordset.recordcount = 0 then statement and nothing works
Show me one of your placements.
Nov 20 '07 #6

Post your reply

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

Similar topics

6 posts views Thread by Peter Frost | last post: by
2 posts views Thread by Steve Pierce | last post: by
3 posts views Thread by Jim Armstrong | last post: by
11 posts views Thread by LayneMitch via WebmasterKB.com | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.