Userform  and Pagetab selection  Create unuqie dynamic sql statement based on selections

Fordraiders
Fordraiders used Ask the Experts™
on
Excel 2003
vba

Userform1
MultiPage1
several tabs but specifically  "Page4"
33 option buttons ( using the caption names as my value for the "where" sql statement

I currently have no "Frame"


I need to be able to select no more than 3 option buttons at one time.
They can select only 1 or 2 or 3 option boxes

So using I assume a Case Select statement build my "WHERE" sql statement.

BASIC sql Statement below : For 3 Options chosen

SELECT tblXrefInfo.*
FROM tblXrefInfo
WHERE (((tblXrefInfo.MFRNAME)="NAME1")) OR (((tblXrefInfo.MFRNAME)="NAME2")) OR (((tblXrefInfo.MFRNAME)="NAME3"));

NAME1 2 3  would be substituted for 3 option boxes "Caption Value"


Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Commented:
I think it would be easier to use an IN clause:


SELECT tblXrefInfo.*
FROM tblXrefInfo
WHERE (tblXrefInfo.MFRNAME IN ('NAME1','NAME2','NAME3'));

then you can just loop the controls and add to a string
for each ctl in me.multipage1.pages(3).controls
   if typename(ctl) = "CheckBox" then
      if ctl.value then strTemp = strtemp & ",'" & ctl.caption & "'"
   end if
next ctl
strtemp = mid$(strtemp, 2)

strWhere = "WHERE  (tblXrefInfo.MFRNAME IN (" & strtemp & "));"

Open in new window


kind of thing. :)

Author

Commented:
ok...just for all , I added a frame control and then assigned the "optionbox" a unique "groupnname" so I could multiselect !

Author

Commented:
what if I only select "1" control ?
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Most Valuable Expert 2011
Top Expert 2011

Commented:
It should still work with an IN clause.

Author

Commented:
ok will try it later

Author

Commented:
Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial