select more then one magazine

[mathanraj76]

Hi..

I have a problem on my database

I have 2 combobox name cbomagazine1 and cbomagazine2 with two command buttons name cmdSearch and cmdViewQuery

Wht i doing here is
first i select magazine name from cbomagazine1 and select magazine name from cbomagazine2 and click the button cmdSearch
one it's show me the filter result then i click cmdViewQuery to sent the data which filter into my query.

for an example
select first cbomagazine2="CAR" and second cbomagazine2=" STYLE" and click the cmdSearch it should filter and the show the result on my subform

some customer might have on magazine and some customers might have more then one magazine

I having a problem on here ..
I don't know how do this ..

i have attached sample database
Thanks
Mathanraj

[June7]

This is similar to issue in post https://www.accessforums.net/program...tml#post174383

"WHERE M" & Me.cbomagazine1 & "='" & Me.cbomagazine1 "' OR M" & Me.cbomagazine2 & "='" & me.cbomagazine2 & "'" & _
" ORDER BY M" & Me.cbomagazine1 & ", M" & Me.cbomagazine2 & ";"

As has been noted already, this non-normalized structure will continually be a source of difficulty.

[mathanraj76]

I just want how many customer may have more then one magazine according to the selection from the both combobox which have in my forms
This is not by quantity but by magazine name
I do this in sql by using the toggle filter but i don't know how to use in vba

the coding which u give me shows error "compile error: expected: end of statement"

Thanks
Mathanraj

[June7]

Sorry, missing an &. The debugger should highlight the text where the error is. Put an & just in front of the highlighted text which should be: "' OR M"

[mathanraj76]

hi ..
i have problem on source code..
when i run both cbomag for rest of the fields is working fine for me expect the qryall.Qmensuno and qryall.Mmensuno it's show nothing..

I have attached the codes

for an example
if cbomag = "style" and cbomag1 ="intrend" it's show result in query table
if cbomag = "mensuno" and cbomag1 ="intrend" no result shows in query table
if cbomag = "mensuno" and cbomag1 ="mensuno" no result shows in query table

other then "Mensuno" the the source code can give the result in query table

Thanks
Mathanraj

[June7]

Code for the query def:

Set db = CurrentDb
If Not QueryExixts("tbltesting") Then
    Set qdf = db.CreateQueryDef("tbltesting")
Else
    Set qdf = db.QueryDefs("tbltesting")
End If
 If Not IsNull(Me.cbomagazine1) Then
     strMag = "M" & Me.cbomagazine1 & "='" & Me.cbomagazine1 & "'"
 End If
 If Not IsNull(Me.cbomagazine2) Then
     strMag = strMag & IIf(strMag <> "", " OR ", "") & "M" & Me.cbomagazine2 & "='" & Me.cbomagazine2 & "'"
 End If
 qdf.SQL = "SELECT qryall.* FROM qryall " & _
     "WHERE " & strMag & _
     IIf(Not IsNull(Me.cbomagazine1) Or Not IsNull(Me.cbomagazine2), " ORDER BY ", "") & _
     IIf(Not IsNull(Me.cbomagazine1), "M" & Me.cbomagazine1, "") & _
     IIf(Not IsNull(Me.cbomagazine1) And Not IsNull(Me.cbomagazine2), ", ", "") & _
     IIf(Not IsNull(Me.cbomagazine2), "M" & Me.cbomagazine2, "") & ";"

Code for the form search:

 Me.qryall_subform.Requery
 Me.qryall_subform.Form.FilterOn = False
 Me.qryall_subform.Form.Filter = "[M" & Me.cbomagazine1 & "]='" & Me.cbomagazine1 & "' OR [M" & Me.cbomagazine2 & "]='" & Me.cbomagazine2 & "'"
 Me.qryall_subform.Form.FilterOn = True

[mathanraj76]

Thanks ..
That's what i really looking for.
and u have save time ..

U r really very good ..expert..
But m..not..
I still learning all the new things..

U have solved my problem..

Thanks
Mathanraj