Runtime error 3021

G

Guest

I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String, KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry", dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
 
A

Allen Browne

Did you test NoMatch after your FindFirst to see if the find was successful?

What if the first record matches, and you then MovePrevious? Did you test
for BOF?

And what if the query returns on records?
 
G

Guest

I changed my 'Error Trapping' choice from 'Break on All Errors' to 'Break on
Unhandled Errors' under Tools--> Option---> General.

I don't receive the error anymore.

I have not tried what you've suggested. The funny thing is, this procedure
is Microsoft's procedure....so I'm not sure why it would give me that error...

http://support.microsoft.com/defaul...port/kb/articles/q210/5/04.asp&NoWebContent=1

Should I continue to pursue a solution to this??

Allen Browne said:
Did you test NoMatch after your FindFirst to see if the find was successful?

What if the first record matches, and you then MovePrevious? Did you test
for BOF?

And what if the query returns on records?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Redsoxnation said:
I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String, KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry",
dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
Click to expand...
Click to expand...
 
A

Allen Browne

You do need to test for NoMatch after any find, and for BOF when you
MovePrevious.

Merely papering over the errors is not enough.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Redsoxnation said:
I changed my 'Error Trapping' choice from 'Break on All Errors' to 'Break
on
Unhandled Errors' under Tools--> Option---> General.

I don't receive the error anymore.

I have not tried what you've suggested. The funny thing is, this
procedure
is Microsoft's procedure....so I'm not sure why it would give me that
error...

http://support.microsoft.com/defaul...port/kb/articles/q210/5/04.asp&NoWebContent=1

Should I continue to pursue a solution to this??

Allen Browne said:
Did you test NoMatch after your FindFirst to see if the find was
successful?

What if the first record matches, and you then MovePrevious? Did you test
for BOF?

And what if the query returns on records?

Redsoxnation said:
I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String, KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry",
dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Can you walk me through that?

Allen Browne said:
You do need to test for NoMatch after any find, and for BOF when you
MovePrevious.

Merely papering over the errors is not enough.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Redsoxnation said:
I changed my 'Error Trapping' choice from 'Break on All Errors' to 'Break
on
Unhandled Errors' under Tools--> Option---> General.

I don't receive the error anymore.

I have not tried what you've suggested. The funny thing is, this
procedure
is Microsoft's procedure....so I'm not sure why it would give me that
error...

http://support.microsoft.com/defaul...port/kb/articles/q210/5/04.asp&NoWebContent=1

Should I continue to pursue a solution to this??

Allen Browne said:
Did you test NoMatch after your FindFirst to see if the find was
successful?

What if the first record matches, and you then MovePrevious? Did you test
for BOF?

And what if the query returns on records?

I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String, KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry",
dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

In the code window, place the cursor in FindFirst.
Press F1.
Read the help file.
Particularly, the paragraph that refers to NoMatch.
Then look a the "example" in the help file.

Once you have sorted that out, do the same for MovePrevious and BOF.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Redsoxnation said:
Can you walk me through that?

Allen Browne said:
You do need to test for NoMatch after any find, and for BOF when you
MovePrevious.

Merely papering over the errors is not enough.

Redsoxnation said:
I changed my 'Error Trapping' choice from 'Break on All Errors' to
'Break
on
Unhandled Errors' under Tools--> Option---> General.

I don't receive the error anymore.

I have not tried what you've suggested. The funny thing is, this
procedure
is Microsoft's procedure....so I'm not sure why it would give me that
error...

http://support.microsoft.com/defaul...port/kb/articles/q210/5/04.asp&NoWebContent=1

Should I continue to pursue a solution to this??

:

Did you test NoMatch after your FindFirst to see if the find was
successful?

What if the first record matches, and you then MovePrevious? Did you
test
for BOF?

And what if the query returns on records?

message
I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String,
KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry",
dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I'll try that....so I take it that this Microsoft PrevRecVal function isn't
complete then?

Allen Browne said:
In the code window, place the cursor in FindFirst.
Press F1.
Read the help file.
Particularly, the paragraph that refers to NoMatch.
Then look a the "example" in the help file.

Once you have sorted that out, do the same for MovePrevious and BOF.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Redsoxnation said:
Can you walk me through that?

Allen Browne said:
You do need to test for NoMatch after any find, and for BOF when you
MovePrevious.

Merely papering over the errors is not enough.

I changed my 'Error Trapping' choice from 'Break on All Errors' to
'Break
on
Unhandled Errors' under Tools--> Option---> General.

I don't receive the error anymore.

I have not tried what you've suggested. The funny thing is, this
procedure
is Microsoft's procedure....so I'm not sure why it would give me that
error...

http://support.microsoft.com/defaul...port/kb/articles/q210/5/04.asp&NoWebContent=1

Should I continue to pursue a solution to this??

:

Did you test NoMatch after your FindFirst to see if the find was
successful?

What if the first record matches, and you then MovePrevious? Did you
test
for BOF?

And what if the query returns on records?

message
I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String,
KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry",
dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Yes, whereever it came from, it is an incomplete example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Redsoxnation said:
I'll try that....so I take it that this Microsoft PrevRecVal function
isn't
complete then?

Allen Browne said:
In the code window, place the cursor in FindFirst.
Press F1.
Read the help file.
Particularly, the paragraph that refers to NoMatch.
Then look a the "example" in the help file.

Once you have sorted that out, do the same for MovePrevious and BOF.

Redsoxnation said:
Can you walk me through that?

:

You do need to test for NoMatch after any find, and for BOF when you
MovePrevious.

Merely papering over the errors is not enough.

message
I changed my 'Error Trapping' choice from 'Break on All Errors' to
'Break
on
Unhandled Errors' under Tools--> Option---> General.

I don't receive the error anymore.

I have not tried what you've suggested. The funny thing is, this
procedure
is Microsoft's procedure....so I'm not sure why it would give me
that
error...

http://support.microsoft.com/defaul...port/kb/articles/q210/5/04.asp&NoWebContent=1

Should I continue to pursue a solution to this??

:

Did you test NoMatch after your FindFirst to see if the find was
successful?

What if the first record matches, and you then MovePrevious? Did
you
test
for BOF?

And what if the query returns on records?

message
I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String,
KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")

'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry",
dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Well...I looked in the Help file and scoured the web for help and didn't find
any real literature on the subject so I'm reverting back to the old-fashioned
D-Lookup function instead...

Allen Browne said:
Yes, whereever it came from, it is an incomplete example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Redsoxnation said:
I'll try that....so I take it that this Microsoft PrevRecVal function
isn't
complete then?

Allen Browne said:
In the code window, place the cursor in FindFirst.
Press F1.
Read the help file.
Particularly, the paragraph that refers to NoMatch.
Then look a the "example" in the help file.

Once you have sorted that out, do the same for MovePrevious and BOF.

Can you walk me through that?

:

You do need to test for NoMatch after any find, and for BOF when you
MovePrevious.

Merely papering over the errors is not enough.

message
I changed my 'Error Trapping' choice from 'Break on All Errors' to
'Break
on
Unhandled Errors' under Tools--> Option---> General.

I don't receive the error anymore.

I have not tried what you've suggested. The funny thing is, this
procedure
is Microsoft's procedure....so I'm not sure why it would give me
that
error...

http://support.microsoft.com/defaul...port/kb/articles/q210/5/04.asp&NoWebContent=1

Should I continue to pursue a solution to this??

:

Did you test NoMatch after your FindFirst to see if the find was
successful?

What if the first record matches, and you then MovePrevious? Did
you
test
for BOF?

And what if the query returns on records?

message
I get a runtime error in my query using the code below...

Option Explicit

Function PrevRecVal_NPDES_1812_Eff_A_qry(KeyName As String,
KeyValue,
FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal_NPDES_1812_Eff_A_qry()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")

'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database

On Error GoTo Err_PrevRecVal_NPDES_1812_Eff_A_qry

' The default value is zero.
PrevRecVal_NPDES_1812_Eff_A_qry = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Sys_Comp_NPDES_1812_Eff_A_qry",
dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal_NPDES_1812_Eff_A_qry:
Exit Function
Err_PrevRecVal_NPDES_1812_Eff_A_qry:
Resume Bye_PrevRecVal_NPDES_1812_Eff_A_qry
End Function

When I debug it, it stops on this line...
PrevRecVal_NPDES_1812_Eff_A_qry = RS(FieldNameToGet)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LIne # on continuous subform 0
GetLine Number and .ADP 6
Numbering the Records of a Query 2
Custom attribute ? 6
Object variable or with block not set error 1
bound control to find a record 2
runtime error 3021 0
Help With RunSum Function 3

Top