db.Openrecordset prolem 2

[robcarr]

Hi,

I am using the coding below to search a recordset, I have used exactly the same coding on another database and it works, but it will not work now.

Private Sub Command13_Click()
Dim strSql, strsql2 As String
Dim rs, rs2 As DAO.Recordset
Dim db As DAO.Database
If Trim$(Me!txtCriterion & "") = "" Then
    MsgBox "enter a value first..."
Else
    Set db = CurrentDb
    strSql = ("select rstatus from tblLoggedCases " _
    & "where UniqueID = '" & Me!txtCriterion & "'")
    Set rs = db.OpenRecordset(strSql) '****ERROR IS HERE****
    If Not rs.BOF And Not rs.EOF Then
        ' record is found
        If Trim$(rs.Fields("rStatus").Value & "") <> "Closed" Then
            ' field is "blank
            DoCmd.OpenForm "frmCloseACase", , , "UNiqueID = '" & Me!txtCriterion & "'"
            Me.txtCriterion = ""
        Else
            'closead field has a value in.
            strsql2 = ("select Owner from tblLoggedCases " & _
           "where Uniqueid = '" & Me!txtCriterion & "'")
           Set rs2 = db.OpenRecordset(strsql2)
           strsql3 = ("select rStatus from tblLoggedCases " & _
           "where UNiqueId = '" & Me!txtCriterion & "'")
           Set rs3 = db.OpenRecordset(strsql3)
                     
            MsgBox "Record has already been closed by " & vbCrLf _
            & "Username         -   " & rs2.Fields("Owner").Value & vbCrLf _
            & "Date Closed      -   " & rs3.Fields("DateClosed").Value & vbCrLf _
            , vbExclamation
            Me.txtCriterion = ""
        End If
    Else
        MsgBox "Record Doesn't exist..."
        Me.txtCriterion = ""
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End If
End Sub

anyone know why?

Thanks

Hope this is of use, Rob.

 

[PHV]

Replace this:
Dim strSql, strsql2 As String
Dim rs, rs2 As DAO.Recordset
By this:
Dim strSql As String, strsql2 As String
Dim rs As DAO.Recordset, rs2 As DAO.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[robcarr]

still get the same error.

the error is

Data type mismatch in criteria expression

Rob.

Hope this is of use, Rob.

 

[PHV]

Replace also this:
strSql = ("select rstatus from tblLoggedCases " _
& "where UniqueID = '" & Me!txtCriterion & "'")
By this:
strSql = "select rstatus from tblLoggedCases " _
& "where UniqueID = " & Me!txtCriterion

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[robcarr]

Thanks that worked a treat,

when a case is not found it works correctly, when a case is found it errors on

DoCmd.OpenForm "frmCloseACase", , , "UniqueID = '" & Me!txtCriterion & "'"

with a message showing: -

The openform action was cancelled

any ideas,

Hope this is of use, Rob.

 

[RoyVidar]

This still considers the ID to be a text field (needing single quotes as delimiters), remove the single quotes there too:

[tt]DoCmd.OpenForm "frmCloseACase", , , "UniqueID = " & Me!txtCriterion [/tt]

Roy-Vidar

 

[robcarr]

I see, thanks that has corrected it all, I have learnt what little I know from tek-tips and other sites, and wasnt sure on this now I know hopefully my posts will go down in numbers.


Hope this is of use, Rob.