I am converting an Access 2003 mdb to operate with a SQL back end and
I am having trouble with the following piece of code which worked
absolutely fine with the original linked tables. The only changes I
have made to the code is to change the table names to add "dbo_" to
the front. I suspect that the problem lies in the syntax of the
'WHERE' clause. At present the code returns, "Policy Not Found". The
following is the code:


Private Sub cmdOK_Click()
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 dbo_Policy P " _
& "WHERE P.policy_no LIKE '*" & txtPolicyNo & "*' " _
& "AND P.version_no IN (SELECT MAX(version_no) " _
& "FROM dbo_Policy P " _
& "WHERE policy_no = P.policy_no) "
Set lrs = dbs.OpenRecordset(lsSQL)
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

stDocName = "frmPolicy"
'* See if form already loaded and turn off data entry
For Each loForm In
Forms '**1.0.16
If loForm.name = stDocName
Then '**1.0.16
loForm.DataEntry =
False '**1.0.16
If loForm.RecordSource <> "qryCurrentPolicies"
Then '**1.0.16
loForm.RecordSource =
"qryCurrentPolicies" '**1.0.16
End
If '**1.0.16
End
If
'**1.0.16

Next
'**1.0.16

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

On Fri, 16 Jan 2009 02:42:37 -0800, colin spalding wrote:

Add a line...

Debug.print

....before you open the Recordset to send the assembled SQL string to the
debug window. You might see the problem just by looking at it there, but
if not, you can paste it into a new query to see if that lets you
determine the problem.

If you were using ADO or a passthrough query your wildcards would need to
be changed from * to %, but with DAO against linked tables there should
be no difference other than possibly performance.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Many Thanks Rick,

Solution 2 worked a treat ,I thought it might be something like that.