Why the hell is this difficult?

[bigbob]

Suppose a form needs to display the following:
A master spreadsheet, keyed (for example) by Social Security number. This
grid would contain name, phone number, age, etc.
A second grid also keyed by SSN listing children's names and ages related
to the SSN in the master
A third grid also keyed by SSN listing the academic record related to the
SSN in the master

Note that all grids are independent at design time, and there could be any
number of related tables I might want to display. I just want to filter the
'many' side grids based on the current SSN in the master spreadsheet, and I
do not want the idiot Microsoft tree dropdown. Why the hell is this hard to
do? If I were doing this in C# or VB I would be finished in about 10
minutes, but Access is a real pain in the ass. In Access terminology I'm
trying to link subforms to subforms, but it seems that Bill's boys do not
support this. Is there a way to accomplish kindergarten tasks using Access
or should I try to talk the customer into abandoning Access?

 

[Rick Brandt]

Suppose a form needs to display the following:
A master spreadsheet, keyed (for example) by Social Security number. This
grid would contain name, phone number, age, etc.
A second grid also keyed by SSN listing children's names and ages related
to the SSN in the master
A third grid also keyed by SSN listing the academic record related to the
SSN in the master

Note that all grids are independent at design time, and there could be any
number of related tables I might want to display. I just want to filter the
'many' side grids based on the current SSN in the master spreadsheet, and I
do not want the idiot Microsoft tree dropdown. Why the hell is this hard to
do? If I were doing this in C# or VB I would be finished in about 10
minutes, but Access is a real pain in the ass. In Access terminology I'm
trying to link subforms to subforms, but it seems that Bill's boys do not
support this. Is there a way to accomplish kindergarten tasks using Access
or should I try to talk the customer into abandoning Access?

Perhaps you should talk your customer into hiring an Access developer.

BTW you can link subforms to subforms. You just can't use the
MasterLink/ChildLink wizard to do so because that assumes that field names
will be used for both properties. You have to make the appropriate entries
manually directly in the property sheet.

 

[Mike Painter]

Suppose a form needs to display the following:
A master spreadsheet, keyed (for example) by Social Security number. This
grid would contain name, phone number, age, etc.
A second grid also keyed by SSN listing children's names and ages related
to the SSN in the master
A third grid also keyed by SSN listing the academic record related to the
SSN in the master

Note that all grids are independent at design time, and there could be any
number of related tables I might want to display. I just want to filter the
'many' side grids based on the current SSN in the master spreadsheet, and I
do not want the idiot Microsoft tree dropdown. Why the hell is this hard to
do? If I were doing this in C# or VB I would be finished in about 10
minutes, but Access is a real pain in the ass. In Access terminology I'm
trying to link subforms to subforms, but it seems that Bill's boys do not
support this. Is there a way to accomplish kindergarten tasks using Access
or should I try to talk the customer into abandoning Access?

If you are talking about ten minutes to develop the forms and the
relationships in C# or VB then that is certainly the language to use.
If not then ten minutes to drop the subforms on a form and fill in the
blanks is a rather long time.

If you could type fast and use default forms it would be possible to create
the tables, establish the relationships, design the forms and build the
whole thing in about ten minutes in Access.


Somehow I think it is more a RYFM problem you are discussing.

One of the hardest things a person can do when moving to something different
is to not try to do it the other way.

 

[Albert D. Kallal]

You can do the whole thing you want with two lines of VBA code.

Of course, the same task in VB will take REAMS AND REAMS of code to do such
a simple task.

First, you need to decide which type of controls to use here. Take a quick
read and look at the following screen shots, as they will give you some
ideas here.

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

Now, for your solution

I would built 3 grids using continues forms, or 3 forms in data sheet view.

You then create a 4 form, and drop in the above 3 forms.

In the master form (which is now a subform), in the on current event, you
place the following command to make the child forms follow this form.

me.Parent.Child1.Requery
me.Parent.Child2.Requery.


In the link child/master settings for child 1, you place:

linkChildFields main_id (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)
LinkMasterFields [MasterForm].[form].[ID]

In the link child/master settings for child 2 form you place

linkChildFields main_id (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)
LinkMasterFields [MasterForm].[form].[ID] ("masterForm" is the name of
the contorl you used to hold the master form).

That is it..you are done.

I count 2 lines of code..and this takes less time to implement then it does
to write this post. Try that in VB....I bet I could get a pizza deliver
faster then what it takes in VB do this!

 

[bigbob]

Thanks Albert. Your reply was both reasoned and helpful. I understand that
Access developers are sometimes defensive about their development arena.
Back in my VB days we took flak from time to time about the use of a
'kiddie' language, despite its popularity with developers. I really didn't
mean to be as harsh as the post came off. I was frustrated because the
properties I wanted to manipulate were not exposed. I cussed a lot more
getting used to the event model for .Net web apps. Access has some
outstanding features. In addition to an excellent report generator, it has a
query builder superior to ALL other products. I don't think I've ever met a
database developer who doesn't use the Access query builder to create
complex SQL statements for use in DB apps, and it doesn't matter what
language they write in.

 

[Albert D. Kallal]

I understand that

Access developers are sometimes defensive about their development arena.

Yea, we are a "bit" that way!

I really didn't

mean to be as harsh as the post came off. I was frustrated because the
properties I wanted to manipulate were not exposed.

Thanks, the above shows a real nice attitude on your part.

by the way, I really did not see you as being harsh at all. You just thought
something should be easy..and the access solutions are "often" more elusive.
I been MUCH more frustrated then you...Nah....you were quite tame!

Actually, one problem with ms-access is that the forms model, and the event
model is quite different from MOST other Microsoft tools (all other tools
are based on the same activeX controls, and when using data connector
objects on a form..again are very similar approach, this is true for c++
forms, and VB forms. It is only access that is very different and weird. I
do stand up and say that this different model is very cool once you learn
it.

I mean, really, the programming language in access is VB6. There is VERY
little different, if any in the actual languance here..

However, the cotnorls, and forms, and events ARE differnt, and I would be
silly not to admit that forms, and contorls are the very essage of the
devlopment process.

Back in my VB days we took flak from time to time about the use of a
'kiddie' language, despite its popularity with developers.

Yes..we get the same..and a heck a lot more then you VB folks ever got!

And, interesting enough..the others poster while helping simply hinted at
using the link/master child idea. However, YOUR case was NOT a child to
child to child problem, but a master, with two childs to the SAME master.

I have to admit that using 3 subforms on a main form is a creative solution
on my part...but then again..sub forms are that way. You can read about some
more sub-form tricks here:

http://www.attcanada.net/~kallal.msn/Articles/fog0000000005.html