Solved

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

Posted on 2011-03-21
6
248 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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