create a form based on a filter or query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table called companies, and I want to creat two forms for the data
entry which is based on whether the company is a member or non member (this
is a source type in the company table) as the forms would then have more
information or less information to be entered depending on whether the
company is a member or non member. For example members would be linked to the
fees table (so a sub form would be used with the companies form) and this
needs to be entered, but non members do not have any fees so it does not make
sense to have this information on their form
 
Hi Yeam69:
First let me perfectly clear....I am not an access guru like others on this
discussion group who are much better qualified to answer you question than I.
With that said I think my idea might work for you.

I believe you said the memeber/non-member is a field in your companies
table. I also assume this is a value to be entered on the form. What you
could do is use the value from the member/non-member field to help determine
if a field should be shown on the form.

What I am thinking is on the after update event on the member/non-menber
field you set the "visable" property of all the fields you do not want to
show to false. This should hide all the fields you do not want to show the
user. You would have to set the visable property of the fields to match the
default value of the member/non-member field.

Hope this helps,
FatMan
 
FatMan,

In my book, you're on the right track... just to add my penny's worth, i
personally prefer to set/reset the Enabled property rather than the
Visible one, so the controls are always visible, but in a "ghost" form
when not applicable. In my book, again, that makes it clearer to the
user that some attributes don't always apply.

Three suggestions for the OP:
* Use the form's On Current event to set/reset the Visible (or Enabled)
property of the controls when scrolling through records, so their state
adjusts to the existing value of the Member field for each record;
* While you're at it, you might want to reset any values in the controls
in question when switching from visible / enabled to invisible /
disabled (makes much more sense in the latter case, since they remain
visible);
* Since you'll be using the same code in two different events (field
update, form current), it makes perfect sense to put the code in its own
Private Sub in the form's module, and just call it from the two events,
rather than repeat the lot twice.

HTH,
Nikos
 
Nikos:
You are 100% correct and I would have chosen to use your method on my own
forms but choose to use the visable property becuse I thought Yeam69 said in
the last line of his/her post that he/she didn't want to display the all the
fields to users unless they are needed.

Thanks,
FatMan
 
This makes sense (well when I find out how to do this, I am no guru ethier
and I am learning as I go with 2 books and the online help!) Just so as to
make it really clear what I have done and what I am trying to achieve, here
is some more information:
I have created a table called companies, one field in this table is a yes/no
field, which is called member/non-meber, checked box determines if the
company is a member. I have then set-up some other tables to store specific
information depending on whether the company is a member or non-member
(members have alot more info than non-members). I now wish to create two
master forms for the companies which the user would see on the switchboard as
Enter Member Company, Enter Non Member Company. If the user selects the
Enter Member Company the form linked to the companies table is displayed at
the top of the form and the subforms (on tabs) would be those using tables
which are only relevant for members. The same would then be done for non
members, with subforms using tables only relevant for non members
 
... but choose to use the visible property because I thought Yeam69 said in
the last line of his/her post that he/she didn't want to display the all the
fields to users unless they are needed.
Click to expand...
Absolutely right. Nevertheless, I offered a close alternative because
(a) in my experience posters tend to pursue the solution they have
thought of, but which is not necessarily the best one - actually I've
seen cases where the alternative was preferred over the originally
sought one, and (b) posts are also read by other users, who might find
the alternative suits their needs better.

Rgds,
Nikos
 
Sorry I forgot to add that obviously a company entered as a non member, who
later becomes a member, would only need the addtional information adding, so
maybe I simply need to run some sort of query whereby there is only one entry
form for the companies, but if the check box for member is selected then the
subforms change - god I hope this makes sense!
 
I would say you only need one form (looks like you are going that
direction anyway) and one command button on the switchboard; let the
user declare if the new entry is a member or not when they actually open
the form, by simply checking / unchecking the checkbox bound to the
Member field. The (simple) code behind the After Update event of the
checkbox will do the rest.
Another hint: if the difference between members and non-members
translates into more/less tables being populated, you might consider
putting the subforms on separate tabs of a tab control on the form, and
making whole tabs visible/invisible (or enabled/disabled) by means of
the checkbox event code.

HTH,
Nikos
 
Yeam69:
Please keep in mind that I am not an access guru and hope that one will jump
in here and guide you down the right path with the best solution.

If I was in your shoes then what I would do is design only the one form
based on all the information you need to collect for the member companies.
Then using my origianl suggestion or Nikos suggestion either make the fields
that are not needed for non-member companies either not visable or not
enabled. (not visable means they will not show up on the form whilie enabled
means they are visable to the user but the user can not access them). In my
opinion it would be best that once the user has changed the member/non-menber
(yes/no data type) field then you set the visable or enable property
accordingly. This would be done on the member/non-member "after update"
event procedure. Of course you would want to set a default value for the
member/non-member field (based on the majority of records being member or
non-member) and set the visable or enabled property to match.

To access the event procedures for a field you need to see the properties
for the field and then click on the "event" tab. The "after update" event
procedure should be shown. To access the area to type in the VBA code click
on the three dots to the right. The code you will need to add is something
like:
me.fieldname.visable = False (if you do not wish to show the field on
the form)
me.fieldname.enabled = False ( if you do wish to show the field but not
allow the user to change its value).

By using this method you only need to create the one form and when a company
changes from non-member to member you should only have to add the information
now needed to the fields that are now visable or enabled.

Hope this helps and I hope if my advice is off the mark that one of the
access gurus will through you a life line and save you.

Good luck,
FatMan
 
Thank you FatMan & Nikos this all makes sense - so either I am learning or
you guys know how to write to the non Guru's - I think it is the latter! I do
get lost sometimes with all of the jargon. I will give this a try once I get
the relationships done - just finished the tables, after a few weeks of
planning and re-planning on paper, and I will let you know how I get on.
Thank you so much for your time and help.
 
Back
Top