Query definition on a form (MVP plz Help)

[mf_sina]

HI!

I do have a question which i don't know it is about a query or form in
access.
i have a table (TblCustomers) with 5 fields: (ID,name,City,State,Country)
and a form (frmcustomers).i have another form (FrmSearch) which i placed 5
checkboxes on it to enable user select which fields he/she needs to see as
result.checkboxes are as follows:
(ChcID,ChkName,ChkCity,ChkState,ChkCountry)
searchQuery should work based on any selection the user has made. for
example i only need fields Name and city. so on my form i check city and
name checkboxes. leaving ID,state and country checkboxes unchecked.
also i need to output query based on user selection to a report.
what should i do?

 

[Rick Brandt]

HI!

I do have a question which i don't know it is about a query or form in
access.
i have a table (TblCustomers) with 5 fields:
(ID,name,City,State,Country) and a form (frmcustomers).i have another
form (FrmSearch) which i placed 5 checkboxes on it to enable user
select which fields he/she needs to see as result.checkboxes are as
follows: (ChcID,ChkName,ChkCity,ChkState,ChkCountry)
searchQuery should work based on any selection the user has made. for
example i only need fields Name and city. so on my form i check city
and name checkboxes. leaving ID,state and country checkboxes
unchecked. also i need to output query based on user selection to a report.
what should i do?

Query criteria is normally used to select which ROWS you want to see not which
COLUMNS. It would be easier to always have all fields in the query, form, and
report and just hide the ones you don't want to see. Of course you would then
need code to move the controls around if you don't want to see gaps between
them.

This is really an unusual thing to do except perhaps in cases where there are
LOTS of fields (which suggests other problems). To actually remove the columns
returned by the query you would need code that re-writes the SQL in the query
on-the-fly. Then of course the controls on the forms and reports that are bound
to the fields that you leave out will throw errors because of the missing fields
so you would need additional code to assign the ControlSource values when the
form/report opens and then you would STILL need code to hide the unused ones and
rearrange the used ones.

I suggest you re-think the whole idea.

 

[mf_sina]

Dear Rick Brandt

Plz include a sample code of what you said in your reply. though it may have
other problems but still i believe it may work for me.
Best wishes.