DoCmd.FindRecord Error - Perplexing

G

Guest

My code has a function to find a record. The function is called by 2
separate search forms, each of which generates SQL for a query that builds a
table of record keys of all qualifying recs. When there is only 1 qualifying
rec, the function is called to open the primary form (Member Data) to the
qualifying rec.

The function uses the rec in the new results table ( a long int ) as the
argument for the FindRecord.

When called by 1 of the forms, it works properly. When called by the second
form it fails on the DoCms.FindRecord line with Runtime error 2162: "A macro
set to 1 of the current fields properties failed because of an error in a
FindRecord action argument."

I don't understand how one can succeed and the other fail, and I don't
really know how to further investigate the problem. Here's the code in the
function:

Public Function FindSingleMbrSearchResult() As Boolean
Dim MbrIDHold As Variant
Dim Result As Boolean

' Open SearchResultInterim recordset having the MbrID of the 1 rec
meeting search criteria
Dim rstSrch As New ADODB.Recordset
rstSrch.Open "SearchResultInterim", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

' Get MbrID of qualifying rec
rstSrch.MoveFirst
MbrIDHold = rstSrch.Fields("MbrID")
' The following verifies that there is a vailid MbrID in each case
MsgBox "MbrIDHold: " & MbrIDHold
rstSrch.Close
Set rstSrch = Nothing

'Delete rec from SearchResultInterim table
DoCmd.SetWarnings False
DoCmd.OpenQuery "SearchResultInterimDelete"
DoCmd.SetWarnings True

If (VarType(MbrIDHold) > 2) Then
DoCmd.OpenForm "Member Data"
Forms![Member Data]!MbrID.SetFocus
DoCmd.FindRecord MbrIDHold
Result = True
Else
Result = False
End If ' (VarType(MbrIDHold) > 2)
FindSingleMbrSearchResult = Result
End Function

I will greatly appreciate any assistance to understand and correct the problem
 
G

Guest

Are you using any me.<fieldname> variables in the "SearchResultInterim"?

Also, I am not sure if this would help but it looks as if you could use a
DCOUNT function to check for the number of records. You can also add a
NZ(DCOUNT(..),0) to add support for when there are no records and DCOUNT
would return a null.
 
G

Guest

SearchResultInterim is a table with only 1 field in each row: MbrID which is
the member ID that is the primary key in the primary table and form.

Prior to invoking the function that I have shown, I use DCount of
"SearchResultInterim". As a result, 1 of 3 actions occurs depending on
whether the result is 0 (no qualifying recs), 1 rec, or multiple. The
function I have listed is invoked only for a single rec with the desire to
clarify that to the user (i.e. only 1 qualifying rec)and then display that
rec in the primary form. If multiple recs are found, it will lead to a
"summary" display listing the qualifying recs with the opportunity to "click"
on any of the recs to open to it in the primary form for review/updating, and
then return to the "summary" display listing until the user's purposes are
complete.

In both of the uses I cited, there is 1 valid MbrID in the
"SearchResultInterim" table, which is displayed in the MsgBox only for
debugging. The function "works" in 1 of the invoking procedures, and causes
an error in the 2nd, although it is acting on the single MbrID in the table.

Ken said:
Are you using any me.<fieldname> variables in the "SearchResultInterim"?

Also, I am not sure if this would help but it looks as if you could use a
DCOUNT function to check for the number of records. You can also add a
NZ(DCOUNT(..),0) to add support for when there are no records and DCOUNT
would return a null.



Richard S. said:
My code has a function to find a record. The function is called by 2
separate search forms, each of which generates SQL for a query that builds a
table of record keys of all qualifying recs. When there is only 1 qualifying
rec, the function is called to open the primary form (Member Data) to the
qualifying rec.

The function uses the rec in the new results table ( a long int ) as the
argument for the FindRecord.

When called by 1 of the forms, it works properly. When called by the second
form it fails on the DoCms.FindRecord line with Runtime error 2162: "A macro
set to 1 of the current fields properties failed because of an error in a
FindRecord action argument."

I don't understand how one can succeed and the other fail, and I don't
really know how to further investigate the problem. Here's the code in the
function:

Public Function FindSingleMbrSearchResult() As Boolean
Dim MbrIDHold As Variant
Dim Result As Boolean

' Open SearchResultInterim recordset having the MbrID of the 1 rec
meeting search criteria
Dim rstSrch As New ADODB.Recordset
rstSrch.Open "SearchResultInterim", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

' Get MbrID of qualifying rec
rstSrch.MoveFirst
MbrIDHold = rstSrch.Fields("MbrID")
' The following verifies that there is a vailid MbrID in each case
MsgBox "MbrIDHold: " & MbrIDHold
rstSrch.Close
Set rstSrch = Nothing

'Delete rec from SearchResultInterim table
DoCmd.SetWarnings False
DoCmd.OpenQuery "SearchResultInterimDelete"
DoCmd.SetWarnings True

If (VarType(MbrIDHold) > 2) Then
DoCmd.OpenForm "Member Data"
Forms![Member Data]!MbrID.SetFocus
DoCmd.FindRecord MbrIDHold
Result = True
Else
Result = False
End If ' (VarType(MbrIDHold) > 2)
FindSingleMbrSearchResult = Result
End Function

I will greatly appreciate any assistance to understand and correct the problem
Click to expand...
Click to expand...
 
G

Guest

Ken,
Here is the code that invokes the function shown previously:

Else ' Find by Last Name
Dim recCount As Integer ' Count of qualifying records found
recCount = 0 ' Initialize

Dim strSQL As String
Dim strBase As String
strBase = " INSERT INTO SearchResultInterim(MbrID) "
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strOrder As String
Dim strName As String


strSelect = " SELECT Membership.MbrID "
strFrom = " FROM Membership"
strOrder = " ORDER BY Membership.MbrID "

strName = Me!LNCr

If matchStartName Then ' If match is for start of name (not
whole name)
strName = strName & "*"
End If

strWhere = " WHERE (Membership.LN = " & "'" & strName & "'" & ")"

strSQL = strBase & strSelect & strFrom & strWhere & strOrder

'********************************************************
' Execute query with sql newly generated for search criteria
'********************************************************
recCount = DoQuerySearch("SearchByMbrTblFlds", strSQL)

//////////////////////////////////////////

ElseIf recCount = 1 Then
' FindSingleMbrSearchResult is the function being invoked
If FindSingleMbrSearchResult Then
DoCmd.Close acForm, "FindMbrEntry"


Thanks for looking into this for me!




Richard S. said:
SearchResultInterim is a table with only 1 field in each row: MbrID which is
the member ID that is the primary key in the primary table and form.

Prior to invoking the function that I have shown, I use DCount of
"SearchResultInterim". As a result, 1 of 3 actions occurs depending on
whether the result is 0 (no qualifying recs), 1 rec, or multiple. The
function I have listed is invoked only for a single rec with the desire to
clarify that to the user (i.e. only 1 qualifying rec)and then display that
rec in the primary form. If multiple recs are found, it will lead to a
"summary" display listing the qualifying recs with the opportunity to "click"
on any of the recs to open to it in the primary form for review/updating, and
then return to the "summary" display listing until the user's purposes are
complete.

In both of the uses I cited, there is 1 valid MbrID in the
"SearchResultInterim" table, which is displayed in the MsgBox only for
debugging. The function "works" in 1 of the invoking procedures, and causes
an error in the 2nd, although it is acting on the single MbrID in the table.

Ken said:
Are you using any me.<fieldname> variables in the "SearchResultInterim"?

Also, I am not sure if this would help but it looks as if you could use a
DCOUNT function to check for the number of records. You can also add a
NZ(DCOUNT(..),0) to add support for when there are no records and DCOUNT
would return a null.



Richard S. said:
My code has a function to find a record. The function is called by 2
separate search forms, each of which generates SQL for a query that builds a
table of record keys of all qualifying recs. When there is only 1 qualifying
rec, the function is called to open the primary form (Member Data) to the
qualifying rec.

The function uses the rec in the new results table ( a long int ) as the
argument for the FindRecord.

When called by 1 of the forms, it works properly. When called by the second
form it fails on the DoCms.FindRecord line with Runtime error 2162: "A macro
set to 1 of the current fields properties failed because of an error in a
FindRecord action argument."

I don't understand how one can succeed and the other fail, and I don't
really know how to further investigate the problem. Here's the code in the
function:

Public Function FindSingleMbrSearchResult() As Boolean
Dim MbrIDHold As Variant
Dim Result As Boolean

' Open SearchResultInterim recordset having the MbrID of the 1 rec
meeting search criteria
Dim rstSrch As New ADODB.Recordset
rstSrch.Open "SearchResultInterim", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

' Get MbrID of qualifying rec
rstSrch.MoveFirst
MbrIDHold = rstSrch.Fields("MbrID")
' The following verifies that there is a vailid MbrID in each case
MsgBox "MbrIDHold: " & MbrIDHold
rstSrch.Close
Set rstSrch = Nothing

'Delete rec from SearchResultInterim table
DoCmd.SetWarnings False
DoCmd.OpenQuery "SearchResultInterimDelete"
DoCmd.SetWarnings True

If (VarType(MbrIDHold) > 2) Then
DoCmd.OpenForm "Member Data"
Forms![Member Data]!MbrID.SetFocus
DoCmd.FindRecord MbrIDHold
Result = True
Else
Result = False
End If ' (VarType(MbrIDHold) > 2)
FindSingleMbrSearchResult = Result
End Function

I will greatly appreciate any assistance to understand and correct the problem
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Richard,

Why not use DCount from the tables that populate the SearchResultInterim
table. This couldn't this save you a step?

Ken


Richard S. said:
Ken,
Here is the code that invokes the function shown previously:

Else ' Find by Last Name
Dim recCount As Integer ' Count of qualifying records found
recCount = 0 ' Initialize

Dim strSQL As String
Dim strBase As String
strBase = " INSERT INTO SearchResultInterim(MbrID) "
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strOrder As String
Dim strName As String


strSelect = " SELECT Membership.MbrID "
strFrom = " FROM Membership"
strOrder = " ORDER BY Membership.MbrID "

strName = Me!LNCr

If matchStartName Then ' If match is for start of name (not
whole name)
strName = strName & "*"
End If

strWhere = " WHERE (Membership.LN = " & "'" & strName & "'" & ")"

strSQL = strBase & strSelect & strFrom & strWhere & strOrder

'********************************************************
' Execute query with sql newly generated for search criteria
'********************************************************
recCount = DoQuerySearch("SearchByMbrTblFlds", strSQL)

//////////////////////////////////////////

ElseIf recCount = 1 Then
' FindSingleMbrSearchResult is the function being invoked
If FindSingleMbrSearchResult Then
DoCmd.Close acForm, "FindMbrEntry"


Thanks for looking into this for me!




Richard S. said:
SearchResultInterim is a table with only 1 field in each row: MbrID which is
the member ID that is the primary key in the primary table and form.

Prior to invoking the function that I have shown, I use DCount of
"SearchResultInterim". As a result, 1 of 3 actions occurs depending on
whether the result is 0 (no qualifying recs), 1 rec, or multiple. The
function I have listed is invoked only for a single rec with the desire to
clarify that to the user (i.e. only 1 qualifying rec)and then display that
rec in the primary form. If multiple recs are found, it will lead to a
"summary" display listing the qualifying recs with the opportunity to "click"
on any of the recs to open to it in the primary form for review/updating, and
then return to the "summary" display listing until the user's purposes are
complete.

In both of the uses I cited, there is 1 valid MbrID in the
"SearchResultInterim" table, which is displayed in the MsgBox only for
debugging. The function "works" in 1 of the invoking procedures, and causes
an error in the 2nd, although it is acting on the single MbrID in the table.

Ken said:
Are you using any me.<fieldname> variables in the "SearchResultInterim"?

Also, I am not sure if this would help but it looks as if you could use a
DCOUNT function to check for the number of records. You can also add a
NZ(DCOUNT(..),0) to add support for when there are no records and DCOUNT
would return a null.



:

My code has a function to find a record. The function is called by 2
separate search forms, each of which generates SQL for a query that builds a
table of record keys of all qualifying recs. When there is only 1 qualifying
rec, the function is called to open the primary form (Member Data) to the
qualifying rec.

The function uses the rec in the new results table ( a long int ) as the
argument for the FindRecord.

When called by 1 of the forms, it works properly. When called by the second
form it fails on the DoCms.FindRecord line with Runtime error 2162: "A macro
set to 1 of the current fields properties failed because of an error in a
FindRecord action argument."

I don't understand how one can succeed and the other fail, and I don't
really know how to further investigate the problem. Here's the code in the
function:

Public Function FindSingleMbrSearchResult() As Boolean
Dim MbrIDHold As Variant
Dim Result As Boolean

' Open SearchResultInterim recordset having the MbrID of the 1 rec
meeting search criteria
Dim rstSrch As New ADODB.Recordset
rstSrch.Open "SearchResultInterim", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

' Get MbrID of qualifying rec
rstSrch.MoveFirst
MbrIDHold = rstSrch.Fields("MbrID")
' The following verifies that there is a vailid MbrID in each case
MsgBox "MbrIDHold: " & MbrIDHold
rstSrch.Close
Set rstSrch = Nothing

'Delete rec from SearchResultInterim table
DoCmd.SetWarnings False
DoCmd.OpenQuery "SearchResultInterimDelete"
DoCmd.SetWarnings True

If (VarType(MbrIDHold) > 2) Then
DoCmd.OpenForm "Member Data"
Forms![Member Data]!MbrID.SetFocus
DoCmd.FindRecord MbrIDHold
Result = True
Else
Result = False
End If ' (VarType(MbrIDHold) > 2)
FindSingleMbrSearchResult = Result
End Function

I will greatly appreciate any assistance to understand and correct the problem
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I've found my problem. The error does not occur if I close and then reopen
the primary form before the ".SetFocus" statement. The .SetFocus statement
was OK, but the following .FindRecord statement resulted in the error. I
don't understand why this resolves the problem, but it now works.
 
Top