Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use in my vb application the tipical ado jet 4.0 conn to Access table.
    Now i have a var filled with "01".
    In the table of my mdb have fields Test and Test1 with a value similar:

    Test Test1
    01 AAAA
    09 BBBBB
    07 CCCC
    06 DDDD
    ... ...

    Ho to find in to the field Test the value of var (in this case is "01") and return into variable named Var_Finded the related value, in this case AAAA.
    Help me, please!!!!!
    Note:
    The max number of recordset into the filed are aprox 1000/1500.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by sal21 View Post
    I use in my vb application the tipical ado jet 4.0 conn to Access table.
    Now i have a var filled with "01".
    In the table of my mdb have fields Test and Test1 with a value similar:

    Test Test1
    01 AAAA
    09 BBBBB
    07 CCCC
    06 DDDD
    ... ...

    Ho to find in to the field Test the value of var (in this case is "01") and return into variable named Var_Finded the related value, in this case AAAA.
    Help me, please!!!!!
    Note:
    The max number of recordset into the filed are aprox 1000/1500.

    Do you have DLookup available to you? I presume you are doing this in code.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I assume that this is NOT running from Access in which case DLOOKUP is not available to you.
    so you will need to reference a table eg tblTest via an ADO Recordset


    Something Like this ought to work.



    Code:
    set rst=New ADODB.Recordset
    strSQL="SELECT * FROM tblTest WHERE [Test]='" & var & "'"
    rst.Open strSQL,conn, adOpenStatic, adLockReadOnly 
    If Not rst.EOF Then
        Var_Finded=rst![Test1]
    Else
        Var_Finded="No Matching Entry Found"
    End If
    rst.Close
    Set rst=Nothing

    Please Not in the SQL var Needs to be enclosed inside single ' characteres assuming it is a text field

    e.g. WHERE [Test]='" & var & "'"

    Andrew

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew W View Post
    I assume that this is NOT running from Access in which case DLOOKUP is not available to you.
    so you will need to reference a table eg tblTest via an ADO Recordset


    Something Like this ought to work.

    [font="arial, helvetica, sans-serif"][size="2"][font="arial, Verdana, sans-serif"][size="3"][size="3"]

    Code:
    set rst=New ADODB.Recordset
    strSQL="SELECT * FROM tblTest WHERE [Test]='" & var & "'"
    rst.Open strSQL,conn, adOpenStatic, adLockReadOnly 
    If Not rst.EOF Then
        Var_Finded=rst![Test1]
    Else
        Var_Finded="No Matching Entry Found"
    End If
    rst.Close
    Set rst=Nothing
    ok... tks for code.
    But the var is dinamic.... i fill var from a loop in range in Excel colum.
    If i use your code is required to open and closed recordset each time, or not?

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Ah.. But you didn't say that.

    Yes it does

    In that case you probably need to open the recordset outside of the loop

    Code:
    'Outside your Loop
    
    set rst=New ADODB.Recordset
    strSQL="SELECT * FROM tblTest "
    rst.Open strSQL,conn, adOpenStatic, adLockReadOnly 
    
    'Then  inside your loop after var is set
    
    rst.Movefirst
    strWhere = "[Test]='" & var & "'"
    rst.Find strWhere
    If rst.EOF Then
    	Var_Finded="No Matching Entry Found"
    Else
            Var_Finded=rst![Test1]
    End If
    
    'Whatever else you want to do
    
    'End of Your Loop
    
    rst.Close
    set rst=Nothing
    This opens the Recordset Up Once with all records and uses the FIND Method to Look for the Specific Record
    It is important each time through the loop to set the Record Pointer Back to the First Record
    Andrew

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew W View Post
    Ah.. But you didn't say that.

    Yes it does

    In that case you probably need to open the recordset outside of the loop

    Code:
    'Outside your Loop
    
    set rst=New ADODB.Recordset
    strSQL="SELECT * FROM tblTest "
    rst.Open strSQL,conn, adOpenStatic, adLockReadOnly 
    
    'Then  inside your loop after var is set
    
    rst.Movefirst
    strWhere = "[Test]='" & var & "'"
    rst.Find strWhere
    If rst.EOF Then
    	Var_Finded="No Matching Entry Found"
    Else
            Var_Finded=rst![Test1]
    End If
    
    'Whatever else you want to do
    
    'End of Your Loop
    
    rst.Close
    set rst=Nothing
    This opens the Recordset Up Once with all records and uses the FIND Method to Look for the Specific Record
    It is important each time through the loop to set the Record Pointer Back to the First Record
    similar:

    'Outside your Loop

    set rst=New ADODB.Recordset
    strSQL="SELECT * FROM tblTest "
    rst.Open strSQL,conn, adOpenStatic, adLockReadOnly

    'Then inside your loop after var is set

    for each cells in my range
    var = cells....


    rst.Movefirst
    strWhere = "[Test]='" & var & "'"
    rst.Find strWhere
    If rst.EOF Then
    Var_Finded="No Matching Entry Found"
    Else
    Var_Finded=rst![Test1]
    End If

    'Whatever else you want to do

    'End of Your Loop

    next cells

    rst.Close
    set rst=Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •