RecordsetClone Problems (1 Viewer)

TimW · Jun 22, 2010

Hi All
This is driving my crazy! I know I could do this another way but I would like to solve this.
The code below works the first time. (It is to see if a duplicate barcode entry is entered for an order, the data is on a sub form) However, If I try to enter a second deplicate entry i get an error.

Object invalid or no longer set. DAO.Recordset

If I close the main form and try again, again the code works but only the once.
PLEASE can anyone give me any sugestions?

Looks like I cannot recreate a record set after I have closed it!?!

Thanks

Tim

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strBarcode      As String
    Dim strCriteria As String
    Dim rsc             As DAO.Recordset
 
    On Error GoTo ErrorHandler
    Set rsc = Me.RecordsetClone
 
 
 
    If Me.Barcode_Number = vbNullString Or IsNull(Me.Barcode_Number) Then
        MsgBox "Must enter Barcode number", vbCritical + vbOKOnly, "Input Error"
        Cancel = True
        Me.Barcode_Number.Enabled = True
        Me.Barcode_Number.SetFocus
    Else
        strBarcode = Me.Barcode_Number
        strCriteria = "[Barcode_Number] Like " & "'" & strBarcode & "'"
  '      Debug.Print strCriteria
        ' see if entered barcode already exists in this order
        With rsc
            Do While Not .EOF
 
                .MoveFirst
                .FindFirst (strCriteria)
                If .NoMatch Then
 
                Else
                    ' **** not allowed two identical barcodes in one order *******
                    MsgBox "This barcode: " & !Barcode_Number & vbCrLf & "Is already used in this order", vbOKOnly + vbExclamation, "Input error - Press ESC to cancel"
                    Cancel = True
                    Undo
                    Exit Do
                End If
                .MoveNext
 
            Loop
        End With
    End If
    ' finished with record set
Exit_Sub:
    rsc.Close
    Set rsc = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Form meter error" & vbCrLf & Err.Description & vbCrLf & Err.Source, vbOKOnly + vbCritical, "Error"
    Cancel = True
    GoTo Exit_Sub
 
End Sub

ajetrumpet · Jun 22, 2010

i wonder if you can only clone a recordset once. ???

have you tried using the actual recordset instead of a clone?

vbaInet · Jun 22, 2010

Try closing the recordset BEFORE Exit_Sub. Or set it to nothing:

Code:

set rsc = Nothing
[B]Exit_Sub[/B]

vbaInet · Jun 22, 2010

Ignore the last reply, you actually do have it before Exit Sub. Might have to rethink.

vbaInet · Jun 22, 2010

Suggestion:

Set the recordset to nothing right after "Cancel = True" in the Is Null check

OR

Use a DCount() function to check if that Barcode_Number returns more than 0 records, if yes, Cancel = True.

However, this criteria:

Code:

        strCriteria = "[Barcode_Number] Like " & "'" & strBarcode & "'"

isn't right. You shouldn't be using LIKE.

TimW · Jun 22, 2010

vbaInet
Thanks for your reply. I may use DCount.
My code is not well documented!!
The IsNull check checks if I have a value in the field, and therefore I have not used the recordsetclone yet <g>
The LIKE in the criteria works (the once anyway and is valid SQL synatax) is this not right?
Anyway, I think I may have to expand the search of invalid Barcode_Numbers to include more that the current order and therefore I may have to create a recordset from SQL.
I just thought I could use recordsetclone :-( but I guess I should try something else.
What bugs is that the sub routine works for correct entries and the first attempt of an incorrect one. Just if i enter more than one incorrect entry it falls over. Annoying,
Still thats life

vbaInet · Jun 22, 2010

Yeap, DCount is the way to go.

TimW said:
The IsNull check checks if I have a value in the field, and therefore I have not used the recordsetclone yet <g>

But you have set the recordset to something prior to this action. So you would still close or set it to nothing.

TimW said:
The LIKE in the criteria works (the once anyway and is valid SQL synatax) is this not right?

You don't need the LIKE operator. Just use "=" instead.

boblarson · Jun 22, 2010

TimW said:
The LIKE in the criteria works (the once anyway and is valid SQL synatax) is this not right?

Just because something works does not make it the right way to do something. You have to understand why it works and the reasons why not to use it. It works because if you are supplying the full barcode, it will only match that barcode. If you supplied a partial, it might be able to find more than one of those. HOWEVER, and that is a big however, you are forcing it to search through the entire table to find any of them that match instead of just pulling back the one you want. If you ONLY want the one then you should look for the specific value so it doesn't have to do a full table scan each time.

TimW · Jun 22, 2010

Thanks Bob
I just googled Equals(=) Vs Like and it is equals what I need

(I still would like to know what made the recordsetclone go legs up though)

Tim

TimW · Jun 22, 2010

Thanks to everyone who had an input.
I gave up on the recordsetclone idea. (as it probably was not comprehensive enough anyway!)

I came up with the code below which

works and
is more comprehensive

This checks a repairs database to see if a meter is already in the repairs department and therefore does not let another meter with the same barcode be entered. It is possible for a meter to be repaired and then returned, but in this case the order would be complete.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strBarcode      As String
    Dim vcount As Variant
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    On Error GoTo ErrorHandler
   Set db = CurrentDb()
 
    ' first check if there is a barcode - if none do not update
    If Me.Barcode_Number = vbNullString Or IsNull(Me.Barcode_Number) Then
        MsgBox "Must enter Barcode number", vbCritical + vbOKOnly, "Input Error"
        Cancel = True
        Me.Barcode_Number.Enabled = True
        Me.Barcode_Number.SetFocus
    Else
    '   ************************************************************* _
        *   see if there is a barcode in an open order that matches * _
        *   the one trying to be entered                            * _
        *************************************************************
        strBarcode = Me.Barcode_Number
        '   ***************************************** _
            *SQL CODE FOR RECORDSET REQUIRED HERE   * _
            *****************************************
        strSQL = "SELECT tblMeter.Barcode_Number, Count(tblMeter.Barcode_Number) AS CountBN " & _
                    "FROM tblRepairOrder INNER JOIN (tblJob INNER JOIN tblMeter ON tblJob.JobID = tblMeter.JobID) ON tblRepairOrder.RepairOrderID = tblJob.RepairOrderID " & _
                    "GROUP BY tblMeter.Barcode_Number, tblRepairOrder.Complete " & _
                    "HAVING (((tblMeter.Barcode_Number)='" & strBarcode & "') AND ((tblRepairOrder.Complete)=False));"
     '   Debug.Print strSQL
        Set rs = db.OpenRecordset(strSQL)
        With rs
            If Not (.BOF And .EOF) Then
                ' must be another live barcode
                MsgBox "Barcode already in a live order in the database"
                Cancel = True
                Undo
            End If
        End With
            ' finished with record set
            rs.Close
    End If
Exit_Sub:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Frm_meter error" & vbCrLf & Err.Description & vbCrLf & Err.Source, vbOKOnly + vbCritical, "Error"
    Cancel = True
    GoTo Exit_Sub
 
End Sub

I think there are still some holes in the code, but I think its getting there

TimW

boblarson · Jun 22, 2010

I'd shorten it up and use a DCount instead:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strBarcode As String
    Dim strSQL As String

    On Error GoTo Error_Handler

    ' first check if there is a barcode - if none do not update
    If Me.Barcode_Number = vbNullString Or IsNull(Me.Barcode_Number) Then
        MsgBox "Must enter Barcode number", vbCritical + vbOKOnly, "Input Error"
        Cancel = True
        Me.Barcode_Number.Enabled = True
        Me.Barcode_Number.SetFocus
    Else
        '   ************************************************************* _
            *   see if there is a barcode in an open order that matches * _
            *   the one trying to be entered                            * _
            *************************************************************
        strBarcode = Me.Barcode_Number

        If DCount("*", "tblRepairOrder", "Barcode_Number=" & Chr(34) & strBarcode & Chr(34)) <> 0 Then
            ' must be another live barcode
            MsgBox "Barcode already in a live order in the database"
            Cancel = True
            Undo
        End If
    End If

Exit_Sub:
    Exit Sub

ErrorHandler:
    MsgBox "Frm_meter error" & vbCrLf & Err.Description & vbCrLf & Err.Source, vbOKOnly + vbCritical, "Error"
    Cancel = True
    [B][COLOR="Red"]Resume [/COLOR][/B]Exit_Sub

End Sub

LPurvis · Jun 23, 2010

Hmm a lot's gone on. Where to begin...

Though it shouldn't cause such failure - you absolutely should not close the RecordsetClone.
You created an object variable and pointed it to the RecordsetClone object, so by all means destroy that (Set rsc = Nothing) if you wish. But the Close method operates upon the object itself (or at least it attempts to) and you don't close persistent objects - and the RecordsetClone is persistent throughout the instance of the form.
(I'll reiterate that - you're not cloning the form's recordset. I can expand upon that if required).

Equally, I wouldn't particularly criticise the choice of comparing with the Like operator.
Obviously if you never want to perform wildcard or any regular expression comparisons then it's a bit redundant, but without any such expression, Like will behave identically to equals (=). (Well - unless you're using Binary field data types, which would be very unlikely - but they're cool ;-)
From an optimisation perspective, performance will be very analogous with an exact match on Like compared to an equality.
However, as I say, if exact is all you want then you might as well be exact in your intent (using Like implies you might want to match expressions - it's less self-documenting).

Are you forbidding matches? Or are you simply wanting to warn the user about the potential match in the table?
If forbidden then this is absolutely worth enforcing at the database level. A unique index on either the field or fields in question (depending upon whether you choose to make it unique per order or for the entire table).
Once implemented then your local check becomes nothing more than a courtesy. A nicer way of informing the user of an impending collision.
The error of a unique index is easily handled in the Error event of a form anyway.

This would have an advantage over the recordsetclone of robustness - as another user could conceivably have entered a conflicting record during the current instance of your form. (However unlikely).
The DCount would also have the potential of handling this requirement.
The problem I have with a DCount (and the recordset method you've chosen) is it's another, essentially redundant, hit on the database.

The matching record either already exists or doesn't.
With the DCount / New Recordset:
If a match does exist, then the check for it matches and you warn the user and do no more.
If it doesn't then you still have to hit the database again to write the record.
With a Unique Index:
If a match does exist, then an error will be raised as you attempt the write. You do nothing further.
If it doesn't exist, the record is written in the same action.
There is no further or pre-emptive hit.

I've seen a database which used to check for a match switch to a unique index and insertion times (over a lot of iterations mind you) decreased by near an order of decimal magnitude.

If you're not concerned about other users - you could still perform the RecordsetClone check as this has the beauty of not hitting the database again. It's making use of a local check. You could still maintain the Unique Index to catch the event of other users having created the record - but you're making the best of both worlds then.

Cheers.

TimW · Jun 23, 2010

Wow thanks Leigh. This shows the value of a forum instead of just googling the problem.

I follow what you are getting at with the recordsetclone. Its just the same object, with a different pointer and therefore when I try and be neat and tidy and destroy it then, bang does not work. Nice of Access to allow me to do that

I follow the Like Vs = argument

I also follow what you are saying about the uniqueness of records. My problem being is that most of the records are imported automatically from other databases. Therefore, I must assume that if they are present they must be valid.
Occasionally records are entered manually in which case: A duplicate is allowed but only if the other entries are not part of an active (or incomplete) order. If this order is being added to manually then it must be active and therefore first place to check is has this meter been added as part of this order? (The input could be via keyboard or scanner so it is possible that a meter may be scanned more than once)
I am not sure how I would validate this across tables as I have an order table and a meter table AND they have a job table between them! (An order may have more than one job. A job will contain a number of meters).
Perhaps I should Google this

Thanks for taking time to reply, I have learned something today.
And thanks to everyone else who has responded

RecordsetClone Problems (1 Viewer)

TimW

Registered User.

ajetrumpet

Banned

vbaInet

AWF VIP

vbaInet

AWF VIP

vbaInet

AWF VIP

TimW

Registered User.

vbaInet

AWF VIP

boblarson

Smeghead

TimW

Registered User.

TimW

Registered User.

boblarson

Smeghead

LPurvis

AWF VIP

TimW

Registered User.

Similar threads

Users who are viewing this thread