Validation (in DAO) doesn´t work!

[Niklas Östergren]

Hi!

I´m quit new to ADO and I try to make a validation (see below) to see if I
find the record selected (with Seek method) but my validation doesn´t work.
I get runtime error 3021 ("Valid record missing", or something like that in
english). Which is correct sinse the record doesn´t yet exist in the
recordset.

IsNull is obvoiusly not a good aproach to validate Seek-method. But what
should I use instead?

TIA!
// Niklas

Her´s the validation I can´t get to work.
=========================================================
If IsNull("rec.Seek " = ", strSearchString") Then
Exit Function
Else
rec.Seek "=", strSearchString

' If value <> 0 send value to calling sub else exit function
If rec("Value") <> 0 Then
SeekStoredCriteria = rec("Value")
Else
Exit Function
End If
End If
=================================================================

Her´s the whole code for this simple function:
=================================================================
Function SeekStoredCriteria(strSQL As String, strSearchString As String) As
Integer

Dim db As Database
Dim rec As Recordset
Dim strMsg As String

'Check validity of passed parameters.
' Exit sub presenting Msg if not valid
If IsNull("strSQL") Or strSQL = "" Or IsNull("strSearchString") Or
strSearchString = "" Then
strMsg = "SQL-string and/or string to search for is missing."
MsgBox strMsg
Exit Function
Else
Set db = Currentdb()
Set rec = db.OpenRecordset(strSQL)

rec.Index = "PrimaryKey"

' If strSearchString doesn´t exist exit function else get value
If IsNull("rec.Seek " = ", strSearchString") Then
Exit Function
Else
rec.Seek "=", strSearchString

' If value <> 0 send value to calling sub else exit function
If rec("Value") <> 0 Then
SeekStoredCriteria = rec("Value")
Else
Exit Function
End If
End If

End If

End Function

 

[Dirk Goldgar]

Hi!

I´m quit new to ADO and I try to make a validation (see below) to see
if I find the record selected (with Seek method) but my validation
doesn´t work. I get runtime error 3021 ("Valid record missing", or
something like that in english). Which is correct sinse the record
doesn´t yet exist in the recordset.

IsNull is obvoiusly not a good aproach to validate Seek-method. But
what should I use instead?

TIA!
// Niklas

Her´s the validation I can´t get to work.
=========================================================
If IsNull("rec.Seek " = ", strSearchString") Then
Exit Function
Else
rec.Seek "=", strSearchString

' If value <> 0 send value to calling sub else exit function
If rec("Value") <> 0 Then
SeekStoredCriteria = rec("Value")
Else
Exit Function
End If
End If
=================================================================

Her´s the whole code for this simple function:
=================================================================
Function SeekStoredCriteria(strSQL As String, strSearchString As
String) As Integer

Dim db As Database
Dim rec As Recordset
Dim strMsg As String

'Check validity of passed parameters.
' Exit sub presenting Msg if not valid
If IsNull("strSQL") Or strSQL = "" Or IsNull("strSearchString") Or
strSearchString = "" Then
strMsg = "SQL-string and/or string to search for is missing."
MsgBox strMsg
Exit Function
Else
Set db = Currentdb()
Set rec = db.OpenRecordset(strSQL)

rec.Index = "PrimaryKey"

' If strSearchString doesn´t exist exit function else get value
If IsNull("rec.Seek " = ", strSearchString") Then
Exit Function
Else
rec.Seek "=", strSearchString

' If value <> 0 send value to calling sub else exit function
If rec("Value") <> 0 Then
SeekStoredCriteria = rec("Value")
Else
Exit Function
End If
End If

End If

End Function

There are lots of things wrong here, I'm afraid. Before getting down to
specifics, please tell us: is the strSQL argument to be passed always
going to be the name of a local table, not a linked table, stored query,
or SQL string? You can only use the Seek method this way in a
table-type recordset, so if strSQL isn't a local table name this is
never going to work.

 

[Niklas Östergren]

Hmm!

Now you make me sade ;-)!

strSQL is, in this case, alwayse a table type recordset and the table is a
local stored table.

// Niklas

 

[Dirk Goldgar]

Hmm!

Now you make me sade ;-)!

strSQL is, in this case, alwayse a table type recordset and the table
is a local stored table.

Okay, in that case, and if there will always be a single-field index
named "PrimaryKey" on the table in question, we can fix up your code to
make it work. You're using IsNull improperly in several places. Try
this:

'----- start of revised code -- still "air code", though -----
Function SeekStoredCriteria( _
strSQL As String, _
strSearchString As String) _
As Integer

Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strMsg As String

'Check validity of passed parameters.
' Exit sub presenting Msg if not valid
If strSQL = "" Or strSearchString = "" Then
'**** Note from DG: These arguments are being passed as strings,
'**** and so cannot be Null.
strMsg = "SQL-string and/or string to search for is missing."
MsgBox strMsg
Else
Set db = Currentdb()
Set rec = db.OpenRecordset(strSQL, dbOpenTable)

With rec
.Index = "PrimaryKey"

' Look for search string
.Seek "=", strSearchString

' If found, get value
If Not .NoMatch Then
' If value <> 0 send value to calling sub else exit
function
If .Fields("Value") <> 0 Then
SeekStoredCriteria = .Fields("Value")
End If
End If

' Close the recordset
.Close
End With

End If

Set rec = Nothing
Set db = Nothing

End Function
'----- end of revised code -----

 

[Microsoft Diskussionsgrupp]

OK! I recognize this code of yours (have read about it sometime ago) but
could´t remember it!

Im not on a computer right now to test it out (don´t have Access 2002). But
I´ll test it as soon as I´ll get home!

Thank´s a lot Dirk!

By the way, yes there will always be a single-field index named "PrimaryKey"
int this table. If I have to create another index I know how to do even with
the code to refere to the right index (I think).

I apreciate you help!

// Niklas

 

[Arvin Meyer]

OK! I recognize this code of yours (have read about it sometime ago) but
could´t remember it!

Im not on a computer right now to test it out (don´t have Access 2002). But
I´ll test it as soon as I´ll get home!

Thank´s a lot Dirk!

By the way, yes there will always be a single-field index named "PrimaryKey"
int this table. If I have to create another index I know how to do even with
the code to refere to the right index (I think).

I apreciate you help!

Hi Niklas,

Dirk IS the man! I just wanted to add that there isn't anything special
about the single-field index named "PrimaryKey". Access uses that name by
default when you create a primary key. You can rename the index "Dirk" (in
his honor, of course <g>) and it will still work fine. To change or name an
index, use the menu item:

View ... Indexes

or click the Indexes button on the toolbar to open the Index dialog and you
can add indexes or change an index name.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Niklas Östergren]

Hi Arvin!

Yes I know Dirk´s the man! Because it work´s like a train!

Thank´s for your information about naming indexes. I allready know that but
that couldn´t you know! Thank´s anyway!

// Niklas