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.Connectio n
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & CurrentProject. Path & _
"\homeTrial.mdb "
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblHome1", _
conn, adOpenDynamic, adLockOptimisti c, adCmdTableDirec t
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("Pi nNo").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.Connectio n
Dim rst As ADODB.Recordset
Dim SeekFor As String
Dim SeekString As String
Set conn = New ADODB.Connectio n
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & CurrentProject. Path & _
"\homeTrial.mdb "
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblHome2", _
conn, adOpenDynamic, adLockOptimisti c, adCmdTableDirec t
Set rst = New ADODB.Recordset
SeekFor = f
SeekString = "[PinNo] like '*" & SeekFor & "*'"
Me.Filter = SeekString
Me.FilterOn = True
Debug.Print rst.Fields("Pin No").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!
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.Connectio n
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & CurrentProject. Path & _
"\homeTrial.mdb "
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblHome1", _
conn, adOpenDynamic, adLockOptimisti c, adCmdTableDirec t
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("Pi nNo").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.Connectio n
Dim rst As ADODB.Recordset
Dim SeekFor As String
Dim SeekString As String
Set conn = New ADODB.Connectio n
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & CurrentProject. Path & _
"\homeTrial.mdb "
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblHome2", _
conn, adOpenDynamic, adLockOptimisti c, adCmdTableDirec t
Set rst = New ADODB.Recordset
SeekFor = f
SeekString = "[PinNo] like '*" & SeekFor & "*'"
Me.Filter = SeekString
Me.FilterOn = True
Debug.Print rst.Fields("Pin No").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!