Requery in the form (1 Viewer)

[xaysana]

Hi there,

I have a main form. in the main form i have linked it to query. in the main i also have 3 unbounded combo boxes to filter for Provinces, Districts and Villages.

My question is that: is it possible to run requery within the main form button please?

Thank you in advance
Cheers,

 

[JamesMcS]

Yes it is - you could put form.requery in either a button, or the afterupdate events of your combo boxes.

 

[xaysana]

Hi JamesMcS,

Thank you for your assistance. It works.
James, I also have other problem. After having those combos worked, I still have problem with filter button. Now, I have Main form links to qry. the qry should be filtered in the Main form too.
What should I workaround this please?
Could you help please?
Cheers,

 

[JamesMcS]

Can you be a bit more descriptive? You didn't mention a filter button to start with. How is your form set up? You could attach a picture....

 

[xaysana]

I am sorry for not make clear since the beginning.

OK, Now i have a form bounds to a qry called add new village. the form displays data from qry in continuous form view. I put 3 more combo boxes to filter the data in the main form. The comb boxes work fine as expected. I also created a search button to do the filter data in form based on what selected in combo boxes.

Please help
cheers,

 

[JamesMcS]

OK. So you want to filter the subform (that's the one in continuous view) based on the values selected in the combo box? Here's how you do it. In the on lcick event of your search button, you need the following

subformname.form.filter="[Province]='" & me.cboProvince &' AND [District]='" & me.cboDistrict & "' AND [Village]='" & me.cboVillage &"'"
subformname.form.filteron=true

I'm assuming here that the bound column of the combo boxes pulls back the data that matches the subform fields. If not, you'll have to put

me.comboname.column(x)

Where x is the column number containing the data you want to filter on, starting from 0.

Obviously don't forget to change subformname to the name of your subform control, combo box names and so on.

 

[xaysana]

OK. So you want to filter the subform (that's the one in continuous view) based on the values selected in the combo box? Here's how you do it. In the on lcick event of your search button, you need the following

subformname.form.filter="[Province]='" & me.cboProvince &' AND [District]='" & me.cboDistrict & "' AND [Village]='" & me.cboVillage &"'"
subformname.form.filteron=true

I'm assuming here that the bound column of the combo boxes pulls back the data that matches the subform fields. If not, you'll have to put

me.comboname.column(x)

Where x is the column number containing the data you want to filter on, starting from 0.

Obviously don't forget to change subformname to the name of your subform control, combo box names and so on.

Thank you James,
I am so sorry for confusing you. In fact, i restructured the Main form to bound to qry directly not from Subform caused i had problem updating form based data.

Do you find any solution so far please?
Cheers,
Xaysana

 

[xaysana]

Hi James,
Just in case, can i send you my dbase if you want to have looks please.
Thank you in advance
Cheers,

 

[JamesMcS]

Morning! Looking at your database, I can't see what you're trying to achieve. What is it you want to see exactly?

What you need is a table that will contain data that the users enter, rather than basing it on a query, as you have at the moment. So -

1. Create a table that contains fields for the data you want the user to enter - for example province number, district number, village number and whatever else the user is going to have to enter.

2. Create a form based on this table that the user will use to add data.

3. In this form, create combo boxes whose row source is a query, looking at the relevant table (Province, District, etc). Make the bound column of these combo boxes the relevant column that you want to store in the table (province ID and so on). Make the control source of the combo the relevant field in the table.

4. Select values in the combo boxes and add new records. You'll see the table being populated with the choices you're making in the combo boxes.

5. Do whatever you want with the table.

When viewing records, I think your best bet is to use a main form and subform. On the main form, create combo boxes as above, no need to have bound columns or control sources though. Use these to filter the subform similarly to the post above, everything should be fine.

 

[xaysana]

Morning! Looking at your database, I can't see what you're trying to achieve. What is it you want to see exactly?

What you need is a table that will contain data that the users enter, rather than basing it on a query, as you have at the moment. So -

1. Create a table that contains fields for the data you want the user to enter - for example province number, district number, village number and whatever else the user is going to have to enter.

2. Create a form based on this table that the user will use to add data.

3. In this form, create combo boxes whose row source is a query, looking at the relevant table (Province, District, etc). Make the bound column of these combo boxes the relevant column that you want to store in the table (province ID and so on). Make the control source of the combo the relevant field in the table.

4. Select values in the combo boxes and add new records. You'll see the table being populated with the choices you're making in the combo boxes.

5. Do whatever you want with the table.

When viewing records, I think your best bet is to use a main form and subform. On the main form, create combo boxes as above, no need to have bound columns or control sources though. Use these to filter the subform similarly to the post above, everything should be fine.

Thank you James for your helped effort. I think you have a point there. That makes sense that a bound combo should requery the subform not the main form where a bound combo located.

Now, i need to restructure my dbase.

Thank you again James.
Regards,