Best way to Design Queries in Form View

[Guest_imported]

Hi

This is my first post so here goes. I wil try and keep this simle.

I have a database made up of people and their favorite fruits.So there is one main table with two fields, names and fruits. The fruits filed is a lookup field, which looks up a fruits table where their is data stored like apple,orange, banana etc

I have designed a form where I can edit or add new people and their favorite fruits. On the form there is a drop down box so that they can choose their fav fruits rather than type in their own (controlled).

Now imagine I my databse has grown to 100`s of users and I want to create some querys that will group by favourite fruit.

I thought of doing this two ways (queries run in form view):-

1. Create separate querys for each fruit, this could get messy as I could have potentially hundreds of queries and forms as there are may diffent types of fruits.

2. Use the [Enter Type of Fruit] option when designing the query, now this causes me a problem as my users cant spell so if they mispell orange then they will have no records found.

So what I would like to do is have a drop down box which is maybe the lookup table, the user selects a fruit, then they would run a query which would use this value and find all people who like what ever fruit waqs selected in the drop down box ? (all run in form view)

I hope that makes sense.

I have tried to the explanation simple.

I would appreciate anyone who can help me out.

BR

Jimmy

 

[Ma3x323]

OK Jimmy!!!
Here it goes. Here's how I see your thang. You have a form which contains textboxes to enter people's name and then a drop down to choose a particular 'favorite' fruit. And then you want a List Box or something to have it show all the people that likes a certain fruit so they would know who else was stupid enough to pick that fruit???
Well here is my idea. If you want to put that list box inside the form you have, you can make a subform and put the list box in there. The list box is bound to the one table you have. Then, you have to make a drop down box (so your fancy user cannot put 'pig' as a type of fruit!) and have them select from that. This drop down box of course is bound to the fruit field of the table. Then, on the event of the drop down box of ON_CHANGE you have to put this:
Private Sub comboRst_Change()
NameofForm/NameofitonmainForm.Requery
End Sub

That should solve your problemo. -Feryl
--> Just a silly software engineer wannabee.
If you feel a post has been helpful to you, click on the link at the bottom of their post to cast a vote for "TipMaster Of The Week". You don't need to be the one who asked the question to vote.