Hi, i need some help. below is my prog.. im actually new to MS access, never learn in school, self picked up
i bold the part which i think the program went wrong, example i keyed in "3M" for Manufacturer, even if the prog found "3M" in the tblClient , it cant proceed to the other if statement. any idea what went wrong? i was told by my fren not to use recordset, but i dont know the alternative for recordset..
--------
Private Sub cmdFilter_Click()
Dim rs As New ADODB.Recordset
Dim rsCmd As New ADODB.Recordset
Dim strSQL As String
Dim SMI As String
Dim LcapMN As String
Dim R As String
Dim tabledb As String
Dim MN As String
strSQL = "Select * from Table1"
rs.Open strSQL, CurrentProject.Connection, adOpenStatic
Do Until rs.EOF
LcapMN = rs("Manufacturer") 'rs points to Table1 (IP data), MN is equals to the Manufacturer in Table1
R = rs("Region") ' R is equals to Region in Table1
MN = UCase(LcapMN) 'convert MN to upper case
'R = UCase(R)
tabledb = "Select Manufacturer,Region from tblClient" 'tabledb string that stores Manufacturers and Region from tblClient
rsCmd.Open tabledb, CurrentProject.Connection, adOpenStatic 'rsCmd that stores tabledb data
Do Until rsCmd.EOF
If rsCmd("Manufacturer") Like "*MN*" Then 'compare IP MN with data in tblClient, Manufacturer
If rsCmd("Region") Like "R" Then ' if true, compare IP R with data in tblClinet, Region
tabledb = "Insert into Tablefinal (Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6)" & _
"Select Distinct Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6 from tblClient" 'copy things from tblCilent to Tablefinal"
CurrentProject.Connection.Execute tabledb
Else 'if Manufacturer is true, but region false,
If R Like "All" Then ' compare R with All, if All, will copy data into tabledb
tabledb = "Insert into Tablefinal (Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6)" & _
"Select Distinct Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6 from tblClient"
CurrentProject.Connection.Execute tabledb
End If
End If
End If
CurrentProject.Connection.Execute tabledb
rsCmd.MoveNext
Loop
rsCmd.Close
rs.MoveNext
Loop
rs.Close
DoCmd.OpenTable "Tablefinal"
DoCmd.Close acForm, "frmClientSearch"
DoCmd.OpenForm "frmClientSearch"
tabledb = "Delete * from Table1"
CurrentProject.Connection.Execute tabledb
End Sub
-----------
i bold the part which i think the program went wrong, example i keyed in "3M" for Manufacturer, even if the prog found "3M" in the tblClient , it cant proceed to the other if statement. any idea what went wrong? i was told by my fren not to use recordset, but i dont know the alternative for recordset..
--------
Private Sub cmdFilter_Click()
Dim rs As New ADODB.Recordset
Dim rsCmd As New ADODB.Recordset
Dim strSQL As String
Dim SMI As String
Dim LcapMN As String
Dim R As String
Dim tabledb As String
Dim MN As String
strSQL = "Select * from Table1"
rs.Open strSQL, CurrentProject.Connection, adOpenStatic
Do Until rs.EOF
LcapMN = rs("Manufacturer") 'rs points to Table1 (IP data), MN is equals to the Manufacturer in Table1
R = rs("Region") ' R is equals to Region in Table1
MN = UCase(LcapMN) 'convert MN to upper case
'R = UCase(R)
tabledb = "Select Manufacturer,Region from tblClient" 'tabledb string that stores Manufacturers and Region from tblClient
rsCmd.Open tabledb, CurrentProject.Connection, adOpenStatic 'rsCmd that stores tabledb data
Do Until rsCmd.EOF
If rsCmd("Manufacturer") Like "*MN*" Then 'compare IP MN with data in tblClient, Manufacturer
If rsCmd("Region") Like "R" Then ' if true, compare IP R with data in tblClinet, Region
tabledb = "Insert into Tablefinal (Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6)" & _
"Select Distinct Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6 from tblClient" 'copy things from tblCilent to Tablefinal"
CurrentProject.Connection.Execute tabledb
Else 'if Manufacturer is true, but region false,
If R Like "All" Then ' compare R with All, if All, will copy data into tabledb
tabledb = "Insert into Tablefinal (Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6)" & _
"Select Distinct Manufacturer,Region,Disty1,Disty2,Disty3,Disty4,Disty5,Disty6 from tblClient"
CurrentProject.Connection.Execute tabledb
End If
End If
End If
CurrentProject.Connection.Execute tabledb
rsCmd.MoveNext
Loop
rsCmd.Close
rs.MoveNext
Loop
rs.Close
DoCmd.OpenTable "Tablefinal"
DoCmd.Close acForm, "frmClientSearch"
DoCmd.OpenForm "frmClientSearch"
tabledb = "Delete * from Table1"
CurrentProject.Connection.Execute tabledb
End Sub
-----------