Solved

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

Posted on 2011-03-21
6
243 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
  • 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now