Solved

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

Posted on 2011-03-21
6
247 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

729 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