Results 1 to 4 of 4
  1. #1
    billdavidson is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    2

    Pulling values from a table

    Hi everyone,

    I found this code online. What I would like it to do is pull a value from a field(ConcAdd) in a table (tbl_ForcD2) which I will manipulate and use to populate a field in a query using this module. the problem I am running in to is that this code pulls the first value from the field ConcAdd repeatedly. What I would like is for this module to grab the next value in that field instead of the first value repeatedly.


    Example:

    ConcAdd Target Field(where module is called in query)
    111 Street Road 111 Street Road(Actual)111 Street Road(Desired)
    222 Street Road 111 Street Road(Actual)222 Street Road(Desired)
    333 Street Road 111 Street Road(Actual)333 Street Road(Desired)
    ...


    Here is the Code

    Dim db As Database
    Dim lrs As DAO.Recordset
    Dim lsql As String
    Dim lgst As String

    Set db = CurrentDb()

    lsql = "select ConcAdd from tbl_ForcD2"




    Set lrs = db.OpenRecordset(lsql)

    If lrs.EOF = False Then
    lgst = lrs("ConcAdd")
    Else
    lgst = "Not found"
    End If

    lrs.Close
    Set lrs = Nothing


    Thanks

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Pulling values from a table ??

    I don't understand the post or the premise of what is taking place. A query is what finds/pulls values from a table. Why not simply use a query?

    confused.

  3. #3
    billdavidson is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    2
    Sorry,

    I'm trying to take many values from a field in a table, manipulate them automatically based on certain conditions, and create another field using the manipulated values. Does that shed any light?

    Thanks for the response.

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    I still tend to think you should work with queries.

    Query1 that sets up the beginning set of records to begin working with.

    Query2 using Query1 as it's source - - that uses calculated values to create new fields. A calculated value can manipulate strings or interact between fields of a record. i.e.
    CalcValue1: [Field1] + [Field2]
    CalcValueText: [Field1] & "add some words" & [Field2]

    using the iif method you can establish conditional values

    Query2 can be just enhancements of Query1 and not necessarily its own standalone query - it all just depends.

    finally - if the info is for display, then you make a form/report sourced on your query....... or if you really do need to enter this new value into a table (and that should be considered with some caution as generally one does not re-record calculated values) then one would need to set up Insert or Append query that use Query2 as its source.

    I see alot of programmers approach databases from a loop mentality - and while there are places where it should be used - in general working with queries (record sets) is more efficient, particularly if large record counts are involved.

    Hope this helps.

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

Similar Threads

  1. Replies: 0
    Last Post: 03-28-2011, 05:06 PM
  2. Replies: 3
    Last Post: 12-21-2010, 11:52 AM
  3. pulling text values into FK number column
    By REBBROWN in forum Database Design
    Replies: 2
    Last Post: 08-30-2010, 05:04 PM
  4. Pulling most recent data from child table
    By davidv43 in forum Access
    Replies: 0
    Last Post: 02-07-2009, 11:36 AM

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