need suggestion and technique to searching…

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

[/size][/size][/font]
Something Like this ought to work.[/size]
[/size][/font]

[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
[/Code]
[/size][/size][/font]
[/size][/font]Please Not in the SQL var Needs to be enclosed inside single ’ characteres assuming it is a text field

e.g. WHERE [Test]='” & var & “‘”[/size]
[/size]
[/size][/font]

Reply | Quote

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

[/size][/size][/font]
Something Like this ought to work.[/size]
[/size][/font]

[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
[/Code]

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote