Solved

when i split database to fe and be- i get errors

Posted on 2011-03-21
6
246 Views
Last Modified: 2012-05-11
hello,
am attaching code- when i split database i have to change recordset to dynaset-
but do not know why i am getting error on code line:
----------------------------------
rstFind.Index = "Key"
---------------------------------------
code works fine when it is just a database  ( not split in fe and be)
any ideas
thank you
Dim db As DAO.Database
Dim rstFind As DAO.Recordset
Dim strSQL As String
Dim response As Integer

If KeyAscii = 13 Then
    Set db = CurrentDb
    Set rstFind = db.OpenRecordset("Twins_Detail", dbOpenDynaset)

    strSQL = Me.Text0
    rstFind.Index = "Key"
    rstFind.Seek "=", strSQL
    If rstFind.NoMatch = True Then
        response = MsgBox("There is no record with that serial number", vbCritical, "ERROR")
        Set rstFind = Nothing
        Set db = Nothing
        Exit Sub
    End If
    Set rstFind = Nothing

Open in new window

0
Comment
Question by:davetough
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 250 total points
ID: 35184079
Seek is not advertised to work on linked tables in Access, only local tables. However, there is a workaround posted:
http://www.mvps.org/access/tables/tbl0006.htm

HTH,

pT72
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 35184515
Why not just use FindFirst instead? Also, I recommend saving the value from the control to a variable of the appropriate type, and giving controls the appropriate prefixes.  Try this instead:
Private Sub cmdTest_Click()

   Dim strCustomerID As String
   Dim strSearch As String
   Dim db As DAO.Database
   Dim rstFind As DAO.Recordset
   Dim intReturn As Integer
   
   Set db = CurrentDb
   Set rstFind = db.OpenRecordset("tblCustomers", dbOpenDynaset)
   strCustomerID = Nz(Me![txtCustomerID].Value)
   strSearch = "[CustomerID] = " & Chr(39) & strCustomerID & Chr(39)
   rstFind.FindFirst strSearch
   If rstFind.NoMatch = True Then
       intReturn = MsgBox("There is no record with that serial number", vbCritical, "ERROR")
       Set rstFind = Nothing
       Set db = Nothing
       Exit Sub
   End If
   
   Set rstFind = Nothing

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35184517
I think you also need some code for when the Find is successful.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:davetough
ID: 35184639
Public Function OpenForSeek(TableName As String) As Recordset
' Assume MS-ACCESS table
    Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _
                    (Mid(CurrentDb().TableDefs(TableName).Connect, _
                    11), False, False, "").OpenRecordset(TableName, _
                    dbOpenTable)
End Function
i am looking at your link-pterandon-if my table is Twins_Detail and my database would be called db1- can you tell me how that would fit in the code there?
so far am getting errors-thank you
0
 

Author Comment

by:davetough
ID: 35186450
i am attaching db - am not getting any where close to having it work- I entered your code helen-and when I enter serial number- it only allows me to enter one digit at a time- if I try to enter more than one- it displays -maybe i entered wrong
i am attaching sample db - so you can see open database and what i have
thank you
one.mdb
0
 

Author Comment

by:davetough
ID: 35186458
open db and enter A111  in ser lookup box and see how works-
sn_lookup form is form-
private sub text0_key press (ascii as integer) - problem function
thank you
it works as one database - but i split and does not work
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question