Strange demand for variable already provided

[FireyColin]

I am experiencing a very strange phenomena, and as I have absolutely
no idea what is causing it, so I'll try and give the full story.
I have a SQL database with an MSaccess frontend. The principal data
is held in a table called 'policy' and data is input and edit by a
form 'frmpolicy', which has a number of subforms on a tab control.
The form is retrieved by use of a menu with the following options, 1
'policy by number', 2 'policy by name', or 3 'all policies'. Numbers
1&2 use a dialogue box to call the form and 3 calls the form
directly. 2&3 both work perfectly well, but calling the policy by
number asks me to 'Enter Parameter Value Policy.approval_date'.

The table does include a field 'approval date', but I cannot see where
its value is required to retrieve the policy.

It seems that the SQL statement calling the form somehow must be
wrong, but for the life of me I cannot see where. I have been going
over this for days and its driving me nuts PLEASE can anyone help?

The SQL statement is as follows:

Dim dbs As Database
Dim loForm As
Form
'**1.0.16
Dim lrs As DAO.Recordset
Dim lsCriteria As String
Dim lsSQL As String
Dim stDocName As String

On Error GoTo Err_cmdOK_Click

'* Attempt to find the Policy
Set dbs = CurrentDb
lsSQL = "SELECT P.policy_no, " _
& "P.version_no " _
& "FROM Policy P " _
& "WHERE P.policy_no LIKE '*" & txtPolicyNo & "*' " _
& "AND P.version_no IN (SELECT MAX(version_no) " _
& "FROM Policy " _
& "WHERE policy_no = P.policy_no) "
Set lrs = dbs.OpenRecordset(lsSQL, dbOpenDynaset, [dbSeeChanges])
If Not lrs.EOF Then
lsCriteria = "policy_no IN ("
'* Build up filter string
While Not lrs.EOF
lsCriteria = lsCriteria & """" & lrs!policy_no & """"
lrs.MoveNext
If lrs.EOF Then
lsCriteria = lsCriteria & ")"
Else
lsCriteria = lsCriteria & ","
End If
Wend
'* Warn the user they have more than one hit
If lrs.RecordCount > 1 Then
If MsgBox("There are " & lrs.RecordCount & " Policies with
the number you specified." _
& vbNewLine & vbNewLine _
& "Do you want to browse them all?", _
vbYesNo + vbQuestion + vbDefaultButton1, _
"NRI Policies") = vbNo Then
txtPolicyNo.SetFocus
lrs.Close
Set lrs = Nothing
Set dbs = Nothing
Exit Sub
End If
End If

Call DoCmd.Close(acForm, Me.name)
Call DoCmd.OpenForm(stDocName, , ,
lsCriteria) '**1.0.16
Forms!frmPolicy.AllowDeletions = False
Forms!frmPolicy.AllowAdditions = False
If lrs.RecordCount > 1 Then
Forms!frmPolicy.NavigationButtons = True
Else
Forms!frmPolicy.NavigationButtons = False
End If
Forms!frmPolicy.cmdClose.Visible = True
Forms!frmPolicy!
txtPolicyNo.SetFocus '** 1.0.15
Else
Call MsgBox("Policy not found", vbOKOnly + vbExclamation, "NRI
Policies")
txtPolicyNo.SetFocus
End If

Exit_cmdOK_Click:
Call lrs.Close
Set lrs = Nothing
Set dbs = Nothing
Exit Sub

Err_cmdOK_Click:
MsgBox Err.Description
Resume Exit_cmdOK_Click

[Patrick Finucane]

Are you using a query or a table?

Could you provide us an indication of which line you get the error?

[FireyColin]

On Feb 6, 7:47 pm, Patrick Finucane <patrickfinucan...@gmail.com>
wrote:

> Could you provide us an indication of which line you get the error?- Hide quoted text -
>
> - Show quoted text -

Hi Patrick again

The form uses the sql statement to query the backend table. It should
be straightforward and has worked for years so I don't understand why
this is happening.

Regards Colin

[Patrick Finucane]

I did not know if your SQL statement queries an actual table or a
query that could contain a parameter.

I don't either. I don't know where you are erroring out.

You could change the query to
Select Top 1
so you don't get a huge bunch of records while debugging and then put
the word
Stop
in your code to start the debugger. Step thru the code and check
results expected in the immediate window.