469,581 Members | 1,960 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,581 developers. It's quick & easy.

Make Automatic pass value to filter

Hi All!

I have two table tblHome1, contains all the unique PinNo and tblHome2 contains a duplicated PinNo and description. I put an automatic numbering (ProdID) and set it as my index. What I want to do is make an automatic filtering and labeling method for my table. The reason for this is because I m handling more than 500 records and labeling them would take me forever, so I started with what I can understand, which is seeking 5 records first using unique ProdID and using ADO.

Dim f As String 'used as global variable

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & _
"\homeTrial.mdb"
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblHome1", _
conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
Set rst = New ADODB.Recordset

For myval = 0 To 5
i = myval + 1
rsDB.MoveFirst
rsDB.Seek (i), adSeekFirstEQ
Debug.Print rsDB("ProdID") rsDB("PinNo")
f = rsDB.Fields("PinNo").Value 'store my current value, then go to
'filter and move next to new record
rsDB.MoveNext
Next
FilterMeNow
I have an auto numbering in tblHome1 so finding the unique ProdID is successful, but I can only print them out and I get a runtime error. What I want to do is for every ProdID (Unique) found, the PinNo (Unique) should be stored on my string variable f and this will be used to filter my second table tblHome2. assuming that I put a value on my f, my f will be passed to my filtering (FilterMeNow procedure) below.

Public Function FilterMeNow()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SeekFor As String
Dim SeekString As String
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & _
"\homeTrial.mdb"
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblHome2", _
conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
Set rst = New ADODB.Recordset

SeekFor = f
SeekString = "[PinNo] like '*" & SeekFor & "*'"

Me.Filter = SeekString
Me.FilterOn = True
Debug.Print rst.Fields("PinNo").Value

End Function
this is the hardest part for me bec. I donít know what to do next, unfortunately I get a run time error 6: Overflow, and thereís a problem in printing out the results, because I cannot assign any value to my f, to filter tblHome2. I've been doing this for a week now and Iím really stuck, I really don't know what to do next, PLEASE help me understand this seek and filtering way. it's been days and reallyI want to sleep.

thanks in advance!
Apr 28 '08 #1
0 1255

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Sharon | last post: by
1 post views Thread by LabWINC | last post: by
1 post views Thread by Michel Esber | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.