Results 1 to 15 of 15
  1. #1
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29

    best way to find a record in a linked table

    I have a string that contains 5 digits (text & numbers).



    I want to compare this string to a field in a linked table. This field has 7 digits, but I will only be comparing the last 5 to see if they match my string.

    Once I find a matching record, I want to bring the data from just a few fields (out of many in the table) into strings.

    I think that it may make sense to work from a query as most of the data in the table does me no good. From there, I have no idea how to properly search the table and how to return the data from there.

    Please, any suggestions are appreciated!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    To clarify:
    You're working in an Access database, and you have a linked Table from another database. Is the 2nd database also Access?
    Does your 5 digit(cars) string represent a field?
    To compare a 5 char string to the last 5 chars in a 7 char field you can say

    If My5CharFld = Right(The7CharFld,5)

    but we need to know your table set up and exactly what you're trying to achieve.

    Can you tell us which field in which table is being compared and to what?

  3. #3
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    Quote Originally Posted by orange View Post
    To clarify:
    You're working in an Access database, and you have a linked Table from another database. Is the 2nd database also Access?
    Does your 5 digit(cars) string represent a field?
    To compare a 5 char string to the last 5 chars in a 7 char field you can say

    If My5CharFld = Right(The7CharFld,5)

    but we need to know your table set up and exactly what you're trying to achieve.

    Can you tell us which field in which table is being compared and to what?
    Yes, they are both access. One is a database from a program that we've used for years. The other is the new program that I'm designing. They both house our inventory, but in different ways, with different information. Some of the fields are the same or related and I'd like to import them, manipulate them, and store them in my tables. This is a one way relationship, I'll only be importing data, never changing it in the old database.

    The stock # field is the primary key on both tables. This is the one that I'll be comparing. User will give the 5 digit that I use, I'll compare it like you mentioned above.
    From there, I will get some fields such as Year, Make, Model, Color, Weights, cost, etc. I'll have the code auto fill these fields, allow the user to fill in the rest and save the record.

    Hope this is clear.

  4. #4
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    Oh, also:

    Since there is a possibility of my stock # matching multiple records in the old database, it will need to offer the user the option to use or disregard and then continue the search.

    I was planning on tackling this aspect later as a seperate consideration, but I might as well mention it now incase it makes a difference on how to plan this.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried using a Select query to just find the matching records?


    You could adjust the sql below to match your table name and field name, and use it in the query wizard

    Select * FROM myLinkedTable Where Right(The7CharFld,5) = [ENTER Your 5 Char string]

    You could then build some vba procedures to do additional things.

  6. #6
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    Hi Orange. I've been reading more about SQL and it does seem like this is probably the most functional way to get this done. I'm really starting from scratch here, so I'm relying on somebody to point me towards the proper method, then researching and learning how to implement it.

    I'll push forward on the SQL query and see if that works for me.

    Thanks.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree that the approach that offers a lot of benefits is to go slow.
    Pick a specific issue/objective and work to achieve it. Then you can build on what you've learned. If you try to conquer the entire (usually ill-defined) all encompassing item in one go, your chances of failing are high, and your learning will be relatively low.

    This forum, and others, are here to assist/advise/suggest. The big issue is that people on the forum(s) don't really know your business, nor your environment, at the same level of detail as you. So, what may seem like a simple, straight forward question to you, may be misunderstood/foreign to many of us. Another issue is that people on the forum(s) don't know your experience/level of expertise, and some responses may seem off-base, too simplistic or over your head to you.

    One step at a time seems to work.

    Some useful tutorials:

    http://www.accessmvp.com/strive4peace/ general Access with references
    http://www.datapigtechnologies.com/AccessMain.htm free video tutorials several subjects
    http://www.fontstuff.com/access/index.htm Martin Green vba and sql and a lot of examples
    http://forums.aspfree.com/attachment...achmentid=4712 RDBMS overview -- very easy read

  8. #8
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    Thanks for the links. I've been reading through the tutorials.

    I'm still confused by one thing:
    The SQL gets the data with a query. I think I'm clear on how to accomplish this.
    Once the query is in place, how do I get the data from the record that it finds?

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you use the query with the query wizard, when you run the query, the selected data will be presented in a grid. I take it that you have done that.

    To work with the data that is returned by a query (in this case) you'll have to investigate Recordsets and vba.

    Here are a few links but I'm sure others will have additional info.
    http://www.functionx.com/vbaccess2003/index.htm
    http://www.everythingaccess.com/tuto...ith-Recordsets
    http://www.everythingaccess.com/tuto...ows=10&Order=7

  10. #10
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    Thanks again orange.

    In going through your links, I came across the DFIRST function. It seems like this will work. But is it the best way?

  11. #11
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    OK, more reading done and I think I have a handle on this.

    Let me know if I'm on the right track here:
    -Create the query based on what we talked about above.
    -Dim a Str with .OpenRecordset(on this query).
    -Reference it as Str.[Field Name] to get the data.

    Sound about right?

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by BRV View Post
    OK, more reading done and I think I have a handle on this.

    Let me know if I'm on the right track here:
    -Create the query based on what we talked about above.
    -Dim a Str with .OpenRecordset(on this query).
    -Reference it as Str.[Field Name] to get the data.

    Sound about right?
    Not really. Here's a brief bit of vba:

    Sub FriNov4()

    Dim db As DAO.Database 'identify the database
    Dim rs As DAO.Recordset 'indicate use of a recordset
    Set db = CurrentDb 'assign the current database to db
    '** assign some data (your query) to the recordset and open it
    Set rs = db.OpenRecordset("Select a,b from YourTablename")
    '
    '**create a LOOP to process the records in the recordset
    Do While Not rs.EOF
    '
    ' you can refer to elements in your recordset
    ' as rs!fieldname, and since I have fields a and b
    ' I can use rs!a rs!b
    '

    '**** This where your logic/processing would go
    Debug.Print rs!a & vbCrLf & rs!B ' Print values of a and b for this record
    rs.MoveNext 'move to the next record in the recordset
    Loop ' end of the Do Loop
    rs.Close ' close the recordset
    End Sub

  13. #13
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    I think I'm catching on.

    I was thinking that I would draw a query first to find the record that has the matching stock # rather than looping through the entire database.

    I was planning on processing the OpenRecordset on just this (or if there are one or two more matching, these) record(s).

    It sounds to me like you're suggesting not processing the query first?

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No. I wasn't showing you specifically how to address your problem. I was showing some very basic vba that included a recordset. I'm not sure how familiar you are with sql or vba or recordsets.

    You could take the code I showed in the previous post, and plop your query sql
    between the two quotes" on the OpenRecordset line, and change the rs!a and rs!b
    to fields in your query.

    By the way you shouldn't have special characters (&#/% etc) or spaces in your names.

    If you do that and run this procedure, it will print out the fields you put in the rs!a rs!b.

    Now this may be old hat to you, but it seems a good place to start if you are not familiar with
    Once the query is in place, how do I get the data from the record that it finds
    Post back the sql for your query and we'll get a simple procedure working.

  15. #15
    BRV is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    29
    orange,
    Thank you very much for your help. I was able to research what you'd showed me and implement it beautifully.

    It is much appreciated!

    I posted a new thread regarding Data types for the createobject function here.
    Please take a look if you find time. Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA: Find Record from in table from form
    By shimmy84 in forum Programming
    Replies: 6
    Last Post: 03-19-2012, 10:51 PM
  2. Lock Record for Editing on a Linked table
    By khalid in forum Programming
    Replies: 3
    Last Post: 06-14-2011, 08:37 AM
  3. Find Record using Query
    By tomself1 in forum Programming
    Replies: 3
    Last Post: 04-18-2011, 09:34 AM
  4. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  5. How to Find a Record
    By lschlitt in forum Programming
    Replies: 2
    Last Post: 09-09-2006, 06:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums