Subform w/table records as option choices (NOT OPTION GROUP)

[Robin]

I'd like to create a subform (NOT A CONTIUOUS FORM) that uses records from a
table as option descriptions. Specifically, a table has a list of US Tax
Jurisdictions and an extra field called TempSelect (Yes/No type). The number
of tax jurisdictions can increase/change. I'd like a subform on a Client
data form to show each Tax Jurisdiction with the option indicator next to it
so when setting up the client, all the Tax Jurisdictions in which the client
has nexus can be "check". (To create a table of tax returns due.) Due to
limited space, I'd rather not have a tall, narrow continuous form that has to
be scrolled to see all Jurisdictions. Is there anyway to show this on a
single detail area of a subform? I've tried everything I can think of and
may just have to live with a continuous form.

 

[Dirk Goldgar]

I'd like to create a subform (NOT A CONTIUOUS FORM) that uses records from
a
table as option descriptions. Specifically, a table has a list of US Tax
Jurisdictions and an extra field called TempSelect (Yes/No type). The
number
of tax jurisdictions can increase/change. I'd like a subform on a Client
data form to show each Tax Jurisdiction with the option indicator next to
it
so when setting up the client, all the Tax Jurisdictions in which the
client
has nexus can be "check". (To create a table of tax returns due.) Due to
limited space, I'd rather not have a tall, narrow continuous form that has
to
be scrolled to see all Jurisdictions. Is there anyway to show this on a
single detail area of a subform? I've tried everything I can think of and
may just have to live with a continuous form.

You could do it, but it would require a lot of code.

Your subform would be an unbound form on which you have previously placed
what you expect to be the maximum number of check boxes. You'd have to use
code at run time to read the table of all tax jurisdictions and set the
label for each check box to one of the tax jusrisdictions. There would be
some check boxes you don't need -- you would set their Visible properties to
False. When processing each client on the main form, you'd read the table
of jurisdictions that apply to them and set the appropriate check boxes, and
then in the AfterUpdate event of each check box you would call a function
that updates that table appropriately.

 

[Dirk Goldgar]

If you're familiar with listboxes, and intermediate VBA coding, what about
a
multi-select listbox? That way, it can be as tall as you want, but it can
have other objects next to it. Then you could add code to loop through
the
selected items in the listbox to update/append. I think with that option,
your extra temp field wouldn't even be necessary.

That's a good suggestion. I've implemented this sort of thing and can
provide sample code. It's lots easier than the code to
show/hide/load/unload unbound check boxes would be.

 

[Robin]

Both good suggestions but I think the multi select listbox would be not much
different than a continuous form solution. (Unless I'm missing something.)
Too tall to view all Jurisdictions simultaneously. Because of the layout of
the Client Data form I was hoping to have the Jurisdictions listed in about 5
columns of about 20 Jurisdictions per column with their associated check
boxes so all could be viewed simultaneously. I hoped for this because the
form would also be used to view a Client who was already set up by
temporarily repopulating the check boxes when the form is opened if the
records existed in the Tax Returns due table. Dirk's suggestion seemed the
most promising and I'm OK with VBA but this may be a bit much for something
that's more of an aesthetics issue.

You have confirmed that my idea has no simple solution and that may save me
the most time in the long run! Thanks for your input.

 

[Dirk Goldgar]

Both good suggestions but I think the multi select listbox would be not
much
different than a continuous form solution. (Unless I'm missing
something.)
Too tall to view all Jurisdictions simultaneously. Because of the layout
of
the Client Data form I was hoping to have the Jurisdictions listed in
about 5
columns of about 20 Jurisdictions per column with their associated check
boxes so all could be viewed simultaneously. I hoped for this because the
form would also be used to view a Client who was already set up by
temporarily repopulating the check boxes when the form is opened if the
records existed in the Tax Returns due table. Dirk's suggestion seemed
the
most promising and I'm OK with VBA but this may be a bit much for
something
that's more of an aesthetics issue.

You have confirmed that my idea has no simple solution and that may save
me
the most time in the long run! Thanks for your input.

You're welcome; sorry I couldn't give you a better solution. Here's one
small thought: you could save space on the main form by using a popup form
just to display the jurisdictions of the current client. On the main form,
you'd just have a command button or toggle button to show/hide the popup

 

[Peter Hibbs]

Robin

A Flex Grid Control will probably do what you want but you will need
to write a bit of VBA code, have a look at my Flex Grid Demo program
for some examples.

Go to http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

HTH

Peter Hibbs.

 

[Robin]

Peter

I downloaded your demo and could not get it to work on my installation of
2002. (Object doesn't support this property or method/Run-Time 438) It has
something to do with the flxgrd.Redraw line. BUT, I took it to work and
opened it on 2003 and it worked fine. You're right, that's exactly what I
was trying to do. Excellent demo! Lots of nifty tools.

Thank you!
Robin

 

[Peter Hibbs]

Robin,

That is odd, I haven't actually tried it on A2002 but it works on
A2000, A2003 and A2007 so it would expect it to work on all versions.
Assuming you registered it correctly it sounds as if there is
something different on your A2002 installation.

Anyway, good luck with your project.

Peter Hibbs.