Cant run rst.nomatch

[winxmun]

Hi all, pardon me as I am not good in VBA. I am converting my current Access 2003 files to 2010. 1st error hit is cant run "rst.nomatch". Below is my code. Would appreciate any help. Thank you!


Private Sub Testing_Click()
On Error GoTo Err_Testing_Click

Dim rst As Recordset
Dim wsp As Workspace
Dim dbs As Database

Set wsp = DBEngine.Workspaces(0)
Set dbs = wsp.OpenDatabase("D:\File A.accdb")
Set rst = dbs.OpenRecordset("Table1", dbOpenTable)

With rst
.Index = "PrimaryKey"
.Seek "=", Me!Serial_No
If .NoMatch Then
Call AddRecord
End If
End With
rst.Close
Set wsp = Nothing

Exit_Testing_Click:
Exit Sub

Err_Testing_Click:
MsgBox Err.Description
Resume Exit_Testing_Click
End Sub

[HiTechCoach]

Step 1 it update the DIM statements. This may fix ne all you need to change,.

Change the following


Dim rst As Recordset
Dim wsp As Workspace
Dim dbs As Database

to this:


Dim rst As DAO.Recordset
Dim wsp As DAO.Workspace
Dim dbs As DAO.Database

[HiTechCoach]

I would avoid using Seek and use SQL.

I would use this code


Private Sub Testing_Click()
On Error GoTo Err_Testing_Click

Dim rst As DAO.Recordset
Dim wsp As DAO.Workspace
Dim dbs As DAO.Database

Dim strSQL as String

Set wsp = DBEngine.Workspaces(0)
Set dbs = wsp.OpenDatabase("D:\File A.accdb")

strSQL = "SELECT Table1.Serial_No FROM Table1 WHERE Table1.Serial_No " & = Me!Serial_No

' if able1.Serial_No is a text data type this use this
' SQL = "SELECT Table1.Serial_No FROM Table1 WHERE Table1.Serial_No " & Chr(34) & = Me!Serial_No & Chr(34)


Set rst = dbs.OpenRecordset(strSQL)

If Not rst.BOF and Not rst.EOF then Call AddRecord

rst.Close

Set rst = Nothing
set dbs= Nothing
Set wsp = Nothing

Exit_Testing_Click:
Exit Sub

Err_Testing_Click:
MsgBox Err.Description
Resume Exit_Testing_Click
End Sub

I am assuming Serial_No is the name of the field in the table TABLE1 and the data type is numeric.

This code is much more efficient and will also work with linked tables.

[winxmun]

hi HiTechCoach, the new code work perfectly fine! thanks so much! :rotlaugh:

[HiTechCoach]

You're welcome.


Thanks for the update. Glad we could assist.