Solved

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

Posted on 2011-03-21
6
244 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

831 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