Code won't run after spliting Access 07 DB (1 Viewer)

[grdavis]

I needed to separate an Access 2007 database, tables from forms, queries, programing, etc.
No problem doing that except that a bit of code in BeforeUpdate that checks to make sure entry is not a duplicate entry, will no longer work. I've tried every suggestion I could find and nothing works. When I reattach tables to the rest of program the code runs perfectly. Very frustrating!!! What can I do???
On Error GoTo Form_BeforeUpdate_Err

Dim dbs As Database
Dim tdf As TableDef
Dim rst As Recordset
Dim strSeek As String


Set dbs = CurrentDb
Set tdf = dbs.TableDefs("MyTable")
Set rst = tdf.OpenRecordset(dbOpenTable)

strSeek = Forms![MyForm]![MyName]
MyLen = Len(strSeek)

rst.Index = "Name"
rst.Seek "=", strSeek

If rst.Index = "" Then
rst.Close
Set rst = Nothing
Exit Sub

ElseIf rst.NoMatch Then
'MsgBox "NO MATCH FOUND"
rst.Close
Set rst = Nothing
Exit Sub


Else
MsgBox ("I have found this Name, " & "'" & strSeek & "'" & _
vbCrLf & vbCrLf & "already listed in your database! " & vbCrLf & vbCrLf & _
"I will clear this name and reset Name for a new entry.")
SendKeys "{ESC 3}"
DoCmd.CancelEvent
rst.Close
End If


Set dbs = Nothing

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Error$
Resume Form_BeforeUpdate_Exit
End Sub

 

[vbaInet]

I needed to separate an Access 2007 database, tables from forms, queries, programing, etc.
No problem doing that except that a bit of code in BeforeUpdate that checks to make sure entry is not a duplicate entry, will no longer work. I've tried every suggestion I could find and nothing works. When I reattach tables to the rest of program the code runs perfectly. Very frustrating!!! What can I do???

You've talked about the problem but without any evidence? No code? lol.

 

[dcb]

You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects. (Source: Access 2007 Help File)

FINDFIRST !!!

 

[vbaInet]

Try investigating the connect property of dao.database

I believe you need to connect to your data (via the path) before accessing the tabledefs.

Editted: I made a late reply there dcb. You are right about the Seek method.

 

[dcb]

Try investigating the connect property of dao.database

I believe you need to connect to your data (via the path) before accessing the tabledefs.

Editted: I made a late reply there dcb. You are right about the Seek method.

However you may be able to use the seek method if you specify the db as the foreign db not currentdb ....

 

[vbaInet]

Interesting, I didn't know that. Might try it sometime. Cheers