Need some help to figure out what's happening

Jdreyfus

Registered User.
Local time
Yesterday, 17:15
Joined
Jun 19, 2008
Messages
27
I've been fooling around with various VB codes to achieve the desired affect of restricting someone entering duplicate information, as well notify and forward the person to the original record if having done so. I played around with a few different ways to go about it with no real success, I recently discovered someone demonstrating how to do exactly what I want, so I copied the code and replaced the necessary fields to specify my database. Everything runs smoothly without any errors whatsoever, however it also doesn't actually direct me to the duplicated record, it instead shoots me always to the first record within the form. I'm curious if anyone knows why this might happen. I'll post my code and some screen shots to clarify what I mean.

Code:
Private Sub YC_TAG_AfterUpdate()
 
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    SID = Me.[YC TAG].Value
    stLinkCriteria = "[YC TAG] = '" & SID & "'"
    'Check Assets table for duplicate YC_TAG
    If DCount([YC TAG], "Assets", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning YC TAG " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now be taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Student Number
        Me.Form.DataEntry = False
        Set rsc = Me.RecordsetClone
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
    Set rsc = Nothing
 
End Sub

This is what occurs after entering a duplicate yc tag
VBWarningyctagduplicatedata.jpg


After pressing okay it brings you to the first record in the form, not the duplicated record.
vbnextactiondoesnttaketoduplicatedy.jpg


Thank you in advance for any advice or idea you guys can give me, it's been one step forward ten steps back for two weeks now just with this small feature.
 
What is "SID", is that a number? Is it a control name? Is it a field in a table? Why is it different than the control/field named "YC TAG"?

If it is a control name, you need to tell VB a little more about it. Try Me.SID instead.

I also noticed that the format of SID and [YC Tag] are different but you're asking Access to do equality on them. "004053" <> "00-4053"

If these hints don't help, try posting your DB here. How To Upload A Database To The Forum

Not related to why it's not working (unless code referenced here is running instead of code you showed us):
Why are you running this code in AfterUpdate? Is there other code running in BeforeUpdate? How about BeforeInsert?
 
Thanks for your input, I don't know what SID is, it was part of the code that I found so I assumed it was something I shouldn't mess with. If it's not serving a purpose other than to confuse things should I just delete it?

Thanks for that suggestion, I'll upload my db, so maybe someone can tell me why I can't seem to get anything to work :P
 

Attachments

Don't delete it! I wasn't paying attention and asked you a wrong question.

Also, warning to other forum members...this is 2007.
 
So is it very different in 2007? Or would the code act in a similar way? I'm wondering because I'm not sure which version the code was originally written for.
 
It doesn't matter, the file you sent is for 2007 only. I only have one install of 2007 and can only look at it when I have time at that location.

The code looks pretty standard (with the Findfirst & bookmark gambit) but I'm not sure about the whole thing.

Perhaps someone else with 2007 could take a look.
 
Thank you so much for helping me out, I can't make sense of it worth a damn
 
Without spending substantial time looking - I'd say there's potentially a small problem in 2007 with the RecordsetClone on forms with Memos and/or Attachment data types.
I'm unaware of any such known bug (i.e. it's not mentioned on Allen Browne's list) and as I say it's not a big deal really.
Presumably removing the Memo's and Attachment fields aren't what you'd want. (Though a separate table for such things isn't the end of the world - and the naming conventions used are a little near the knuckle as far as being reserved.)

You have several options though.
First of all - it seems to me that there's no point whatsoever in assigning the RecordsetClone object before you perform the DCount and offer the message box (and pivotally the Undo method!).
i.e.
Code:
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
 
    SID = Me.[YC TAG].Value
    stLinkCriteria = "[YC TAG] = '" & SID & "'"
    'Check Assets table for duplicate YC_TAG
    If DCount("*", "Assets", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning YC TAG " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now be taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Student Number
        Set rsc = Me.RecordsetClone
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
    Set rsc = Nothing

Alternatively you can just simplify the whole shebang, you know the record exists - just use the form recordset directly.

Code:
    Dim stLinkCriteria As String
 
    stLinkCriteria = "[YC TAG] = '" & Me.[YC TAG].Value & "'"
    'Check Assets table for duplicate YC_TAG
    If DCount([YC TAG], "Assets", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning YC TAG " _
             & Me.[YC TAG].Value & " has already been entered." _
             & vbCr & vbCr & "You will now be taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Student Number
        Me.Recordset.FindFirst stLinkCriteria
    End If

Either way - the small bug isn't then a factor.
 
I should probably point out at this stage that I'd be tempted to not persue that exact method anyway.
Though it's possible that another user has entered the data since the current user opened the Assets form - if you use the Recordset Clone only (without any DCount against the table - which is a relatively expensive hit against the database considering that we already have that data loaded locally) then you can check as:

Code:
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
 
    Set rsc = Me.RecordsetClone
    stLinkCriteria = "[YC TAG] = '" & Me.[YC TAG].Value & "'"
    'Check Assets table for duplicate YC_TAG
    rsc.FindFirst stLinkCriteria
    If Not rsc.NoMatch Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning YC TAG " _
             & Me.[YC TAG].Value & " has already been entered." _
             & vbCr & vbCr & "You will now be taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Student Number
        Me.Bookmark = rsc.Bookmark
    End If
    Set rsc = Nothing

I believe you have the field in question indexed - so it's impossible a legitimate duplicate will be entered anyway. You could just error handle that eventuality that the record has been inserted by another user...
 
Also, I found the code that you got this from. How come you deviated from the posted code (for instance, you set rsc twice and set DataEntry to false)?

Anyhow, I fixed that in your code when I got home and ran into an error. Since I don't have an older version of Access here I can try it on, I can't tell if it's a problem running this specific code in 2007 or if it's something else (like the code never worked).

At any rate, you should major listen to Leigh's advice. The correct way to prevent duplicates is to put a UK (unique index in Access speak) on the field(s) you don't want duplicated.
 
I would say that the occurances of two assignments of the recordsetclone - and indeed the DataEntry mode look like semi-desperate attempts to be rid of an error that was occuring otherwise?
As I say though - IMO the DCount is, not only seemingly problematic - but wasteful resourcewise. Not if you had a form which was bound to a single row instead of the whole table (always my preference for data forms as it in itself is non-wasteful) then you would have had call to make the DCount check.

And, as I recall, there was already a unique index for the field in question, but I felt that, beyond the pre-emptive check already in place - some error handling for hitting that index might be a good idea.
Indeed - it could be argued that all of this should be done through that process, as using the index to catch the duplication is the most efficient means. However you wouldn't have a data error raised until you attempted to. None the less - you could maintain the functionality anyway... (it assumes that there's only one field which could raise the error though...)

Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    Dim rsc As DAO.Recordset
    Dim blnFound As Boolean
    Dim strVal As String
    
    Const cField = "YC TAG"
    strVal = Me(cField)
    
    If DataErr = 3022 Then
        MsgBox "Warning " & cField & " " _
             & Me(cField) & " has already been entered." _
             & vbCr & vbCr & "You will now be taken to the record.", _
               vbInformation, "Duplicate Information"
        Me.Undo
        Set rsc = Me.RecordsetClone
        rsc.FindFirst "[" & cField & "] = '" & strVal & "'"
        If Not rsc.NoMatch Then
            blnFound = True
        Else
            Me.Requery
            Set rsc = Me.RecordsetClone
            rsc.FindFirst "[" & cField & "] = '" & strVal & "'"
            If Not rsc.NoMatch Then
                blnFound = True
            End If
        End If
        If blnFound Then
            Me.Bookmark = rsc.Bookmark
            Response = acDataErrContinue
        End If
    End If
    
    Set rsc = Nothing
    
End Sub
 
Thank you guys so much, I tried this:

Code:
Private Sub YC_TAG_BeforeUpdate(Cancel As Integer)
   Dim stLinkCriteria As String
 
    stLinkCriteria = "[YC TAG] = '" & Me.[YC TAG].Value & "'"
    'Check Assets table for duplicate YC_TAG
    If DCount([YC TAG], "Assets", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning YC TAG " _
             & Me.[YC TAG].Value & " has already been entered." _
             & vbCr & vbCr & "You will now be taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Student Number
        Me.Recordset.FindFirst stLinkCriteria
    End If
    
End Sub


And it works great, thank you, I was driving myself nuts trying to figure it out.
 

Users who are viewing this thread

Back
Top Bottom