Dynamic Option Group

[richy240]

Dynamic Option Group problem... How does this work?

Hello!

I am trying to create a form under which users can select criteria for a report. My end result should be a report that lists the invoices (just some data about the invoices, that is) from a specified company where the date_paid field is either Null or Not Null (this will tell me if an invoice has been paid or not). So, I want the ability to choose from 12 (or more!) reports simply by selecting different options on the form.

My criteria is (currently) as follows: four inspection agencies (actually three and a 'catch-all') and paid or unpaid (or both). I would like this to be dynamic. That is, if I were to add a new inspection agency to tb_agencies, I would like my form to automatically update itself.

Is there a way to do this programmatically? I have read a bit about option groups, but I have not come across anything regarding dynamic values in an Option Group.

Thanks in advance.

 

[Tiro Cupidus]

How is the user selecting the criteria? With a combo box? If that's the case, you should be able to just bind the row source to a table/query. Then any time you update the table, the selection in the combo will be updated as well.

Edit:
Oh, I guess you're trying to use radio buttons. In that case, I suggest you use combo boxes =)

 

[richy240]

Well, I wanted to use either a Combo box or an Option Group; whichever was most beneficial to the project. (By this I mean functions correctly, operates quickly, etc.)

I have actually figured this one out with the help of another user on another forum. What we did was create a Combo Box for the agencies (which may change; box is bound to tb_agencies table) and an Option Group for the status of the Invoice (Paid, Unpaid or Both; this will not change).

Then we built a dynamic WHERE clause using VBA behind the "Run Report" button. This uses the values of the Combo Box and Option Group to build a string with which we call the DoCmd.OpenReport function.

Thank you for your help. I think you recommended exactly what we did, which I think, makes this the most correct (vs. less correct; er, what?) solution for the problem.

Thanks again!