By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Make Automatic pass value to filter

P: 38
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
Share this question for a faster answer!
Share on Google+

Post your reply

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