Code in a form that finds a Record

[Stafford]

Don't whether to Post in Forms or VBA, here goes.

I have a Text box that is a key field, it requires 6 Alpha Numerics.
What I would like, is for the User to enter the Key Field in Add Record mode, in The AfterUpdate event (Or any such event that will work)
I would like code to search for a duplicate record. If a match is found I want a "YesNoCancel" option to display "This record already exists, do you want to update it?"

If Yes Go to the record and allow the edit.
If No they can re-enter the Key Field to allow for Typos
If Cancel, well it just cancels I guess

I was working on this code with a friend who knows a little VBA and we got the Message box up, but nothing else. Now the message box doesn't even pop up.

Is this all wrong?

Private Sub txtApo_AfterUpdate()

' Find the Record that matches the control.
Dim rs As Object
Dim varBkmrk As Variant

Set rs = Me.RecordsetClone
varBkmrk = rs.Bookmark
rs.FindFirst "[APO/Sabre File Number]" = "" & Me![txtApo] & ""

If Not rs.NoMatch Then
Dim msg, style, varchoice
msg = "This record already exists. Do you want to update it?"
style = vbYesNoCancel
varchoice = MsgBox(msg, style)
If varchoice = vbYes Then
dummy = "Yes"
Else
dummy = "No"
End If
rs.Bookmark = varBkmrk
End If

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Stafford Harriman

 

[Jon K]

The code for FindFirst is:-

rs.FindFirst "[APO/Sabre File Number] = '" & Me![txtApo] & "'"

 

[Stafford]

Thank you for your response Jon K, I have been working on this for a long time.

The Messgage box now works.
Next problem is, when I hit 'Yes' I receive a Run Time Error 2105
When I debug this section is highlighted in Yellow
Me.Bookmark = rs.Bookmark which is near the bottom of the code.

 

[Jon K]

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

is fine. I have no idea why it should cause a run time error. After editing FindFirst and adding Dim dummy As String, I have been able to run the AfterUpdate procedure without error in Access 97.

 

[Stafford]

Jon,

I don't know if this helps but I thought a few screenshots of the error might help shed light into my prob.

 

[Stafford]

Here is the next screenshot.

 

[Jon K]

Attached is a demo DB. The code in it is modified from yours and serves to show the using of bookmarks.

It was written in Access 97 and has been tested in Access 2000.

Open the form. Enter an APO. If it is a new APO, the user can enter a new record. If the APO already exists, the user is given the option to edit the existing record.

The code is contained in the AfterUpdate Event of text box txtAPO:-
-------------------------------------
Private Sub txtAPO_AfterUpdate()
On Error GoTo Err_Handler

' APO must be 6 charaters in length, otherwise exit.
If Len(Me![txtAPO]) > 6 Then
MsgBox "APO must be 6 charaters in length"
Exit Sub
End If

Dim rs As DAO.Recordset
Dim varchoice As Variant

Set rs = Me.RecordsetClone
rs.FindFirst "[APOSabreFileNumber] ='" & Me![txtAPO] & "'"

If Not rs.NoMatch Then
' APO already exists.
varchoice = MsgBox("This record already exists. Do you want to update it?", vbYesNo)

If varchoice = vbYes Then
' If answer is Yes, display existing record for edit.
Me.Bookmark = rs.Bookmark
End If

Else
' If rs.NowMatch, allow user to add a new record.
DoCmd.GoToRecord , , acNewRec
Me.APOSabreFileNumber = Me![txtAPO]
Me.FileName.SetFocus
End If

Set rs = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description

End Sub
-----------------------------------------

It is written in Access 97 so DAO was used. If you write the code in Access 2000, you must make a reference to DAO (when the code window is open, choose menu Tools, References... Check Microsoft DAO 3.6 Object Library.)


Hope you can adapt it to suit your needs.

 

[Stafford]

**Stafford Hugs and kisses Jon K for helping him finally solve a months old problem**

Thanks so much Jon, that piece of code is gonna do wonders for me!

 

[Jon K]

You are welcome.


Just found a typo in my code. The line to test for the length of APO should be:-

If Len(Me![txtAPO]) <> 6 Then

 

[Stafford]

That I assume would check if there are too few characters?

Thanks again for all your effort, I really do appreciate it.

 

[Stafford]

Jon,

Your code is gonna go along way.

I've another form that is utilizing it now.

*******************************************


Private Sub txtAPO_AfterUpdate()
On Error GoTo Err_Handler

' APO must be 6 charaters in length, otherwise display message and exit.
If Len(Me![txtAPO]) <> 13 Then
MsgBox "Debit Memo Number must be 13 charaters in length"
Exit Sub
End If

Dim rs As DAO.Recordset
Dim varchoice As Variant

Set rs = Me.RecordsetClone
rs.FindFirst "[Memo Number] ='" & Me![txtAPO] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

If rs.NoMatch Then
' Found Debit Memo
varchoice = MsgBox("Debit Memo not found. Do you want to enter new Debit Memo?", vbYesNo)

If varchoice = vbYes Then
' If answer is Yes, display existing record for edit.
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Debit Memo Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Me!txtMemoNumber.SetFocus
If varchoice = vbNo Then
'Keep Focus in the Debit Memo Search Box
Me!txtAPO.SetFocus
End If

End If

End If

Set rs = Nothing
Exit Sub
Err_Handler:
MsgBox Err.Description

End Sub
**********************************************

This piece of code is going to forever keep my users outta my tables.

Thanks again.

 

[Bigmo2u]

Once again I'm bringing a thread back from the dead, but it was only one I could find in my search that look like it would match what I needed.

The problem I have is that I can't get the code to work for my needs. I also already have something else in my afterUpdate event, does this mattter?

This what I tried to do.

Private Sub tblClaims_SSN_AfterUpdate()
On Error GoTo Err_Handler - 'unsure on this'

'SSN must be 9 digits in length, otherwise display message and exit'
If Len(Me![tblClaims_SSN]) <> 9 Then
MsgBox "SSN Must be 9 digits in length"
End If

'This is what I Already have in the AfterUpdate Event'
DoCmd.Requery "SDN 1
[SDN 1] = Null
DoCmd.Requery "SDN 2
[SDN 2] = Null
DoCmd.Requery "SDN 3
[SDN 3] = Null
DoCmd.Requery "SDN 4
[SDN 4] = Null
DoCmd.Requery "SDN 5
[SDN 5] = Null

End Sub

This is the Error I get:

Compiler Error: Label Not defined
On Error GoTo Err_Handler

Please be nice I relatively new at this. Is the Err_Handler a Macro?

 

[Bigmo2u]

I figured out the problem this is what I did.

'SSN must be 9 digits in length, otherwise display message and exit.'
If Len(Me![tblClaims.SSN]) <> 9 Then MsgBox "SSN Must be 9 digits in length"
Me.tblClaims_SSN.SetFocus

I put this in the next field of the form in 'On Enter' event.