Recordsets... argh I hate them!

[SiJP]

Ok, I posted hear earlier and ended up giving a star for some useful tips.. I have another chunk of code playing me up.

The senario is as follows. I have:

forms: frmOutOfStocks
tables: tblOutofStocks

The idea is that I can record when a product is out of stock in my business place. I've included some functionality that creates a "Matchkey" everytime a record is created. this matchkey is made up of three unique factors: Location ID, Date and Product Code.

If someone duplicates entry of an out of stock record, this can be identified from the matchkey. The code below bascially (at run time) will check to see if the current entry already exists.

Function fDuplicateCheck()
'============================================
'once matchkey has been created, see if any
'other match keys of the same sort exists
'============================================

'declare variables
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblOutOfStocks", dbOpenDynaset)

Dim Msg, Style, Title, Response
Msg = "This Out of Stock has previously been recorded.  Click OK to delete the existing entry and continue or Cancel to adjust."
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Out of Stock Duplicate Entry"

'find records that match the current matchkey
rst.MoveFirst
rst.FindLast "MatchKey = '" & MatchKey & "'"

'actions to take if matchkey already exists / does not exist.
If rst.NoMatch = True Then

    'no action necessary - duplicate not found

Else
    'duplicate found
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then
    'get rid of existing duplicate record
    rst.Delete
    Else
    'back to the record without deleting
    'no code necessary
    End If

End If

rst.Close

End Function

I have two problems.

1) No matter if i enter a duplicate record or not, the rst.NoMatch always will set to True, indicating a duplicate. I think I have some of the rst.FindLast code wrong.. any help would be appreciated.

2) If I stick some error handling in and put in the Err.Number and Err.Description, it will come up with error 28, Out of Stack space.. why is this and how can I avoid it?

TIA.

 

[AccessAce]

Here's a thought. Instead of using record sets to do your checking, try the DLookup() function. Since you are looking for a specific string in a specific field of a specific table. The DCount() function may also serve equally well. These functions, supposedly, have been optomized to speed the search. Then your test is easy:

if DCount("[fieldname]", "tablename", _
"[fieldname] = '" & MatchKey & "'&quot >0 then
'There is a match. show error msgbox
else
'there is no problem
End If

 

[wkendrvr]

The problem you are having is this

'find records that match the current matchkey
rst.MoveFirst
rst.FindLast "MatchKey = '" & MatchKey & "'"


I see the code where you are getting the last key, but you are not incrementing through the records to check the matchkey against existing records. If you wanted to continue using that method you would have to iterate through the records.

 

[AccessAce]

Note to wkendrvr:
The FindLast method DOES loop through all records. Perhaps you are confusing it with the MoveLast method.

 

[wkendrvr]

My bad you are correct, too many egg nogs

 

[SgtJarrow]

A quick look at you code and I have the following thoughts....

You mention that matchkey is a generate collection of strings to create a unique id...fine. I am also going to have to assume that this is either a public variable or one that this function has access to. Now...with the above info...looking at the line:

rst.FindLast "MatchKey = '" & MatchKey & "'"

I read this as following...start from the last record, and search towards the first, looking for....what???? Alhtough is may not be the case, I would first verify that MatchKey is the actual field name in the tblOutOfStocks. And second, in the line above, enclose the Matchkey with []....or in other words....

rst.FindLast "[MatchKey] = '" & MatchKey & "'"

Lastly, make sure that the matchkey is in fact a text string...can it be numeric??? If so, removing the single quotes will solve your problem.

Other than that, your code looks pretty clean....there really is no need for the rst.MoveFirst line though.....the rst.FindLast will automatically move the focus to the last record and start working backward every time....Also, you may want to try the .FindFirst method if you can....I tend to find better results with it than .FindLast.....don't know why..... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[danvlas]

Another way to skin this cat...

Set rst = CurrentDb.OpenRecordset("Select Count(MatchKey) An NoOfMatches From tblOutOfStocks Where MatchKey = " & MatchKey, dbOpenDynaset)

rst.Fields("NoOfMatches&quot will return the number of matches and you can do whatever you want, without moving around through an entire table.

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[SiJP]

Thanks all for your input. Daniel, not sure where the NoOfMatches field comes from there.. thanks for the ideas though (didn't know you could use a SELECT statement in the open recordset part!)

Robert, tried adjusting code as above - the MatchKey is a field on the tblOutOfStocks and is a Text data type. A typical matchkey will be: "12320/12/2002067895". I set it as text due to the '/'s contained within the string.

I'm still having the original problem that rst.NoMatch returns a False value (indicating there are duplicates) on all records, regardless if they have been duplciated.

The reason I wanted to use recordsets is so that I can use the rs.Delete function get rid of the 'found' duplicate. I adjusted some of the code so that the user is not asked if they want to delete or not (thinking about that one I come to the conclusion that they'd need to delete regardless so I took the option away). This now reads:

'actions to take if matchkey already exists / does not exist.
If rst.NoMatch = True Then
     Debug.Print "NO DUPLICATE FOUND"
Else
     'duplicate found automatically delete duplicate through rst.Delete
     Debug.Print rst.Fields("Matchkey") & " - " & rst.Fields("OOSid") & " will be deleted"
End If

As I said previously, rst.NoMatch returns a False value, meaning the Debug.Print returns my matchkey and also (now I've added it) the ID number of the record which its found as a duplicate.

When I entered a new record that definately was NOT a duplicate, the print out returned:

"101/01/200268134 - 3700 will be deleted"

Since 3700 was the record I'd just entered, I came to the conclusion that when I was searching through the table, the duplicate it was finding was itself.. as the record was already written to the table it seems logical to access that the record was in fact a duplicate! So no syntax errors to the code there..

I think my resolution would be to *not* write the entry to the table until until validation has been done.. joy, a few more hours changing code...

 

[wkendrvr]

One way to do that would be to have an entry table (front end side) and compare it to the backend if you have a match you don't append.

 

[AccessAce]

Maybe you should have the database delete the second found instance of the key. That way, only the earliest one will get svaed in the database.

 

[SiJP]

Thanks both for the suggestions.. the adding / deleting part is sorted.. just had trouble with the finding! If anyone is intersted in the final code, please let me know and I'll paste here for reference.