program unable to output anything

tiku

New member
Local time
Today, 18:05
Joined
Feb 2, 2012
Messages
6
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

-----------
 
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..
--------

...SNIP
Do Until rsCmd.EOF
If rsCmd("Manufacturer") Like "*MN*" Then 'compare IP MN with data in tblClient, Manufacturer
....SNIP
-----------

MN in your statement bolded is a literal, you're asking for everything that is LIKE *MN* not everything LIKE *The Contents of the variable MN*

i.e.
Code:
If rsCmd("Manufacturer") Like "*" & MN & "*" Then

You're doing a very inefficient search though. The recordset in and of itself is fine, but when you know what you're looking for to begin with, why open everything and step through it record at a time? You could just use a query to populate only the record(s) that you want.

eg:

Code:
tabledb = "Select Manufacturer,Region from tblClient WHERE manufacturer LIKE " & chr(34) & "*" & MN & "*" & chr(34)

Then you've got the data you want to begin with and don't need to step through an entire table to find the couple of records that you actually want.

Are you really sure that you want a LIKE here though?

A search for "3M" would also capture a company, for argument's sake, called "3Monkeys".

I've not looked too closely at the rest of the code because it's very difficult to read, if you use the [ code] tags and indent it so that it's readable you'll probably get some more opinions.
 
Thank you tehNellie, now my program can display some data, but the data was wrong.. it seems to copy every single data in the database and output it.. but i counldnt figure out what went wrong.. im thinking was the INSERT statement im using it wrongly. below is the part of program which i have removed the comments i made. To my understanding, every correct matches found, it will copy the data at "tblClient "which the recordset is currently pointing to and insert into "Tablefinal"... i used breakpoint to check the flow of the program, it seems fine to me. so its the problem with my coding.. should i use UPDATE instead? but how should i initialize the disty1, disty2 and so on? is it tblClient.disty1 ?

And yes, im trying to find anything that consider of 3M example... if user wanted to find Manufacturer that consist of "technologies", the program will display all the Manufacturer that consist of "technologies"

i did came across the way you wrote for "If rsCmd("Manufacturer") Like "*" & MN & "*" Then " . Just that the website did not mention much on it, as such i thought it means the same as what i typed. By the way, do you have any nice website that you can introduce to me which is good and easy to understand. Thanks



Code:
Do Until rs.EOF
LcapMN = rs("Manufacturer") 
R = rs("Region")         
MN = UCase(LcapMN)       


 tabledb = "Select Manufacturer,Region from tblClient"  
 rsCmd.Open tabledb, CurrentProject.Connection, adOpenStatic 
 
 Do Until rsCmd.EOF
 If rsCmd("Manufacturer") Like "*" & MN & "*" Then      
 
     If rsCmd("Region") Like "*" & R & "*" Then          
    [B]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[/B]
     
     
    Else 
    
        If R Like "All" Then   
        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
 
Last edited:

Users who are viewing this thread

Back
Top Bottom