OpenRecordSet error Type Mismatch! How to solve this?

[mutanic]

Hi everybody, I couldn't identify what is the error...here is my code :

Public Sub cmdSelRO_Click()
On Error GoTo Err_cmdSelRO_Click
 
Dim dbs As Database
Dim rst As DAO.Recordset
Dim SQL As String
 
SQL = "SELECT * FROM tblSolicitorMonthlyReport WHERE RO = '" & cmbRO.Value & "' ORDER BY RO"
Set dbs = CurrentDb("tblSolicitorMonthlyReport")
Set rst = dbs.OpenRecordset(SQL, dbOpenTable)
rst.MoveLast
 
Exit_cmdSelRO_Click:
    Exit Sub
 
Err_cmdSelRO_Click:
    MsgBox Err.Description
    Resume Exit_cmdSelRO_Click
 
End Sub

[Mariostg]

If the value of cmbRO.Value is numeric, remove the single quotes surrouding your query statement.

[prasad599]

what is the datatype of RO in the database ?

[mutanic]

Ok, I just change the code into this :

Public Sub cmdSelRO_Click()
On Error GoTo Err_cmdSelRO_Click
 
Dim dbs As Database
Dim rst As DAO.Recordset
Dim SQL As String
 
'If txtTitleRecoveryOfficer.Visible = False Then
'    txtTitleSolicitor.Visible = True
'Else
'    txtTitleRecoveryOfficer.Visible = False
'    txtTitleSolicitor.Visible = True
'End If
 
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT * FROM tblSolicitorMonthlyReport WHERE RO = '" & cmbRO.Value & "' ORDER BY RO", dbOpenDynaset)
rst.MoveLast
 
Exit_cmdSelRO_Click:
    Exit Sub
 
Err_cmdSelRO_Click:
    MsgBox Err.Description
    Resume Exit_cmdSelRO_Click
 
End Sub

but nothing happen...??? I wanted to change the recordset inside my form base on my selection... I select any "Recovery Officer" name from dropdownlist and it suppose to change my current recordset which it only contain data related to selected name. How do I solve this???

[mutanic]

Data type of RO is "Text" only...

I got list of "Recovery Officer" inside table tblRO which is connected to tblSolicitorMonthlyReport as it would appear as dropdownlist when you open in table mode.

Now I got this button in form which also contain list of "Recovery Officer" as dropdownlist, I select the name then I want my form recordset contain related data to the selected name. How do I do this?

[Mariostg]

Not sure I see clearly what you try to achieve. But if you want to change the content of your form when the combo content change, the form has to know the dataset has changed.

So on an On update event of the combo box, you want something like

MyForm.Recordsource=YourSqlQuery
 

[prasad599]

try cmbRO.text instead of cmbRO.Value