Form based on a query

[Courtney91]

Hi. I've created a form based on a query for tractors.. for example I have the field 'make' and I've set it up to search for jcbs, when I want to change it to search for John Deeres for example the query works but the form does not update... can someone please explain to me how to do this.

[Bulzie]

Try this in AfterUpdate event of the combobox(assume name of combobox and field in table is called "make":

Me.Filter = "make = '" & me.make & "'"
Me.FilterOn = True

[NTC]

A form must have its record set requeried. One can close & reopen the form - or at an appropriate event you must trigger the code: me.requery

[Courtney91]

Thank you for your responses!
I have head of requery and tried it (propabkt doing it wrong)
Could someone explain to me how to do this?

I'd also like to set up a button that allows me type in for example jcb or tractor and it brings up the relevant data instead of always doing it design view of a query first. How could this be done?

Btw my form does update when I close it and reopen. But that's annoying to do all the time

[aytee111]

Do post #2. The third line is Me.Requery

[Bulzie]

You can do the me.filter option or do a requery of the data after you select a value from a combo box for example. If you can close and reopen your form and it shows the correct data, sounds like you already have the correct criteria in your form's query? So in properties for that "make" field, in the Events tab in AfterUpdate, add Forms!YourFormName.Requery so it will rerun the query for your form.

[Courtney91]

I still can't get it to work
The form updates but I have to close the form and rerun it instead of just refreshing which is what I want to do.

If i start from scratch in the property sheet box in form.. what exactly do I need to put in so when I open my form it asks what I want to search for?
Is it also possible for it to ask me the make of what I want to search and also the model (these two fields are already set up)

Trying to make his sound as much sense as I can :')

[aytee111]

How is the user selecting the make, is it a combobox? The filter/requery will be done in the AfterUpdate of the combobox, once the user has selected the make.

[Courtney91]

At the minute I'm doing a search in the query table saving then opening the form... I tried a combo box but it only lets you select the full make and model, I want to search for specific make

[aytee111]

Well, I suggest that you get the combobox working before moving on with this. In Access, that is the accepted way of selecting which data to show to the user. You can set it to display any thing you want (not sure what you mean by specific make). If you are doing wildcard searches, such as looking for all records that being with a R, then make a textbox and in the VBA you will change the SQL to reflect that. You will then have criteria in your query referencing the combobox (=Forms!FormName!ComboboxName) and then the filtering.

[ssanfu]

Maybe you would post your dB?

(Do a "Compact & Repair", then Zip it.)