VBA Problem ???

[powerblade]

Hi,

I've searched for days, but not found a solution.:banghead: Here is what i want:

i have a combo: idd
i type something and it begins searching ( search as you type ).

Everything works when 1 table. example.. when i begin typing is see:

name - price

I want put an extra field : stock
so what i want see is :
name - price - stock

I have half working vba and cannot get it 100 % work. here s the code:

With Me.idd
        If Len(Trim(.Text)) > 0 Then
            strText = Replace(.Text, "*", "")
            strFind = " model Like ""*" & strText & "*"""
            strSQL = "SELECT [imei-1] AS imei,inkoop_id_no,model, kleur, staat, prijsink, prijsverk FROM status_id LEFT JOIN tbl_ink_id ON status_id.statusid = tbl_ink_id.status WHERE " & _
    strFind & ""
        Else
            strSQL = "SELECT tbl_ink_id.[imei-1] AS imei, tbl_ink_id.inkoop_id_no, tbl_ink_id.model AS Naamproduct, tbl_ink_id.kleur, status_id.staat, tbl_ink_id.prijsink, tbl_ink_id.prijsverk, qry_totaalinkoop_bulk.SomVanaantalbulk AS Totinkoop, IIf((IsNull([SomVanaantal])),0,[SomVanaantal]) AS Totverkocht, [Totinkoop]-[Totverkocht] AS voorraad " & vbCrLf & _
"FROM ((tbl_ink_id LEFT JOIN qry_totaalinkoop_bulk ON tbl_ink_id.inkoop_id_no = qry_totaalinkoop_bulk.inkoop_id_no) LEFT JOIN qry_totaalverkocht ON tbl_ink_id.inkoop_id_no = qry_totaalverkocht.[Product-id]) LEFT JOIN status_id ON tbl_ink_id.status = status_id.statusid ;"

        End If

The ELSE part works perfect. " [Totinkoop]-[Totverkocht] AS voorraad " part is the extra field i want see while typing.

If LEN = 0 then the ELSE parts works great, but if >0 then the first part only works whithout this " [Totinkoop]-[Totverkocht] AS voorraad " part..

any help appreciate..

 

[Cronk]

If you had inserted a debug.print line in your code, you would have seen the problem
ie debug.print strFind

Try

 " model Like " & """*" & strText & "*"""

 

[powerblade]

If you had inserted a debug.print line in your code, you would have seen the problem
ie debug.print strFind

Try

 " model Like " & """*" & strText & "*"""

Thanks for reply. The problem is only the WHERE statment.

strSQL = "SELECT tbl_ink_id.[imei-1] AS imei, tbl_ink_id.inkoop_id_no, tbl_ink_id.model AS Naamproduct, tbl_ink_id.kleur, status_id.staat, tbl_ink_id.prijsink, tbl_ink_id.prijsverk, qry_totaalinkoop_bulk.SomVanaantalbulk AS Totinkoop, IIf((IsNull([SomVanaantal])),0,[SomVanaantal]) AS Totverkocht, [Totinkoop]-[Totverkocht] AS voorraad " & vbCrLf & _
"FROM ((tbl_ink_id LEFT JOIN qry_totaalinkoop_bulk ON tbl_ink_id.inkoop_id_no = qry_totaalinkoop_bulk.inkoop_id_no) LEFT JOIN qry_totaalverkocht ON tbl_ink_id.inkoop_id_no = qry_totaalverkocht.[Product-id]) LEFT JOIN status_id ON tbl_ink_id.status = status_id.statusid ;"

        End If

above code is ELSE part. this is working.

the IF part:

With Me.idd
        If Len(Trim(.Text)) > 0 Then
            strText = Replace(.Text, "*", "")
            strFind = " model Like ""*" & strText & "*"""
            strSQL = "SELECT [imei-1] AS imei,inkoop_id_no,model, kleur, staat, prijsink, prijsverk FROM status_id LEFT JOIN tbl_ink_id ON status_id.statusid = tbl_ink_id.status WHERE " & _
    strFind & ""
        Else

is working too. So there is nothing to debug. BUT

what i want is,

If LEN = 0 then i see a list with fields:
imei- Naamproduct- kleur-staat-prijsink,-prijsverk- voorraad

BUT if LEN >0, then i see only:
imei- Naamproduct- kleur-staat-prijsink,-prijsverk

SO NO VOORRAAD...

IF i copy paste the strSQL from the else part to the IF part, then the WHERE part with strFind doesnt work...

hope you can understand the problem.

 

[Cronk]

Did you really paste the sql of both parts and examine the output?

It seems to me that the SELECT fields are

imei, inkoop_id_no, Naamproduct, kleur, staat, prijsink, prijsverk, Totinkoop, Totverkocht, voorraad

and
mei,inkoop_id_no,model, kleur, staat, prijsink, prijsverk

ie no 'voorraad' in one.