Full Version: Record Found Beforeupdate, Clicking Ok Needs To Open Found Record Help
UtterAccess Forums > Microsoft® Access > Access Forms
SteveHale
Hello again everyone.
Been working on this pickle for awhile and it does the following:
1. When i fill up the "MyRecordNumber", it searches for an existing record. (MyRecordNumber = Primary Key)
2. When i tab over to the next field and a match is found, a message box pops up.
Is there a way / code so that when i click on "OK", the record that it found would come up?
So far all I've come up with is "Run-time error '2501'; The OpenForm action was cancelled".
Any pointers would be a great help as I am stumped right now.
HAs always, thanks.
-Steve
CODE
Private Sub MyRecordNumber_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[MyRecordNumber]", "Requests Processed", "[MyRecordNumber] = '" & Me.MyRecordNumber & "'")
If Not IsNull(Answer) Then
MsgBox "Existing RECORD found" & ": " & MyRecordNumber.Text & vbCrLf & vbCrLf & "Please SEARCH and EDIT on EXISTING Record." & vbCrLf & "Press ESC to close this message.", vbCritical + vbOKOnly + vbDefaultButton1, "EXISTING RECORD FOUND"
DoCmd.OpenForm "Requests Processed", , , Answer
Cancel = True
Me.MyRecordNumber.Undo

Else:
End If
End Sub
theDBguy
Hi Steve,
Are you using separate forms for adding and editing the same record?
SteveHale
No sir.
Just one form.
-Steve
theDBguy
Thanks. Then I don't understand why you're trying to open another form (or the same one). You should be able to just "navigate" to the found record (if the form contains all the records in the table).
Just my 2 cents... 2cents.gif
strBean
Hi Steve - sure, no prob.
!--c1-->
CODE
Private Sub MyRecordNumber_BeforeUpdate(Cancel As Integer)
  Dim rst As Recordset
  Set rst = Me.RecordsetClone
  rst.FindFirst "[MyRecordNumber] = '" & Me.MyRecordNumber & "'"
  If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
  Else
    Me.MyRecordNumber.Undo
  End If
  rst.Close
End Sub

Note: The syntax in your DLookup criteria argument suggests your Primary Key is a text field. Wondering why you don't use AutoNumber. It's really the only bulletproof way to set up a Primary Key. I suggest you use AutoNumber as the data type for all of your Primary Key fields. If you do so, of course you'll have to take the single quotes out of the search criteria.
SteveHale
Agreed.
just wanted the functionality of upon clicking "OK", the record would come up as soon as the message box exits as oppossed to click "OK", "CTRL F", enter number, click "FIND".
The reason for this is because the record it pulls up will definitely be updated, regardless of the reason and confirms that this IS the record that was needed. i'm thinking this saves keypresses and button clicks.
Is this function doable though?
Thanks for the quick response sir.
-Steve
SteveHale
OOOH! i'll try this now!
'll let you guys know what happens.
Thanks!
-Steve
SteveHale
just fired it up and got...
quot;Run-Time error 2115"
The macro or function set to the BeforeUpdate or ValidationRule property for the fields is preventing Microsoft Access from saving the data in the Field.
It highlights this section
Me.Bookmark = rst.Bookmark
strBean
Sorry, didn't think it through thoroughly. Don't use the BeforeUpdate event, and best bet is to use an unbound textbox (blank out the ControlSource property.) Try it in the LostFocus event of the search textbox.
SteveHale
Just tried it and it just jumped back to the first field of the form.
The good news here is that the boss decided to scrap this idea.
So for now, i'm taking it easy on this but the boss sure left a big "Question Mark" in my head if this is feasible.
Thanks for all the help strBean and theDBguy.
-Steve
PS
i'll keep searching on my free time and keep you guys updated with whatever i run into.
theDBguy
Hi Steve,
Sam and I were happy to assist.
What you want should be possible. For example, try something like:
CODE
Private Sub MyRecordNumber_BeforeUpdate(Cancel As Integer)
Dim rs As Object
If DCount("*", "[Requests Processed]", MyRecordNumber='" & Me.MyRecordNumber & "'") > 0 Then
     MsgBox "Existing RECORD found" & ": " & MyRecordNumber & vbCrLf & vbCrLf & "You will be taken to that record now...", vbInformation + vbOKOnly, "EXISTING RECORD FOUND"
     Set rs = Me.RecordsetClone
     With rs
          .FindFirst "MyRecordNumber='" & Me.MyRecordNumber & "'"
          If Not .NoMatch Then
               Cancel = True
               Me.MyRecordNumber.Undo
               Me.Undo
               Me.Bookmark = .Bookmark
          End If
          .Close
     End With
End If
Set rs = Nothing
End Sub

(untested)
Just my 2 cents... 2cents.gif
theDBguy
Come to think of it, we probably don't even need the DCount() part. So, something like:
!--c1-->
CODE
Private Sub MyRecordNumber_BeforeUpdate(Cancel As Integer)
Dim rs As Object
Set rs = Me.RecordsetClone
With rs
     .FindFirst "MyRecordNumber='" & Me.MyRecordNumber & "'"
     If Not .NoMatch Then
           MsgBox "Existing RECORD found" & ": " & MyRecordNumber & vbCrLf & vbCrLf & "You will be taken to that record now...", vbInformation + vbOKOnly, "EXISTING RECORD FOUND"
           Cancel = True
           Me.MyRecordNumber.Undo
           Me.Undo
           Me.Bookmark = .Bookmark
     End If
     .Close
End With
Set rs = Nothing
End Sub

(still untested)
Just my 2 cents... 2cents.gif
SteveHale
Hello theDBguy.
How you guys come up with solutions this quick is beyond me.
YOUR CODE WORKS! FLAWLESSLY EVEN!
No flippin' run-time errors on the first try. AMAZING!
My apologies i haven't tried this out as soon as apossible.
But to confirm it... this code has been tried, tested, and implemented.
Thanks theDBguy!
-Steve
theDBguy
Hi Steve,
Thanks for the update! Glad to hear you got it to work.
Good luck!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.