Sub-SUB-form?

[HockeyNut]

Hi everyone, This is my Table Structure Diagram which got the thumbs up a couple of weeks back in the "Tables" forum.

However, I've come to design the forms now and am hitting a problem.
I based the form on the tblClient as this is what everything ultimately belongs to. The sub form for the tblApplication is fine, it pulls out the information based on the ClientID in the main form. My problem comes after this, I don't know whether
1. I'm trying to do the impossible,
2. Whether my table structure is causing the problem, or
3. That I don't know enough about Access/Programming yet

(I suspect options 1 & 3 are to blame ).

I think I'm after what I can only describe as a sub-sub-form.

I have the client normal/main details near the top. Then a datasheet subform displaying the application details based on tblApplication.

Then what I'm after is a further sub form based on which application is selected on the application subform. This further sub form would then display payment details for each application. (A similar effect is required for my tblBadge).

I've been experimenting for a while this afternoon trying to get this to work, but am now turning to you guys in hope of some help.

A further thought I had is that I could perhaps use unbound objects in similar principal to a cascading combo solution. But I'm unsure whether that is the best way to go to get the solution I require, as we need to input data via this form, not just view it.

Yours very hopefully,

HN

EDIT: Might I add, I know not to base my forms on a table, I was doing this merely as a time saver whileI experimented with the wy the form would work before beginning work on the form properly.

 

[HockeyNut]

Anyone?

 

[HockeyNut]

One way to get my post count up eh... have a conversation with myself!

Anyway, I think I've worked out how to do this.

I've based a query on the form criteria (of the ApplicationID) and the query displays the payment detail for the application ID's which are vissible in the first sub form.

Next I want to display the application ID which is currently selected. I'm assuming this may involve a bit of VBA coding!

HN

 

[jessa_lee]

I'm hoping someone will reply as well. I've been watching your post because I'm looking for a relatively similar answer. I need to enter information for a software inventory DB. I have all the tables/relationships set up, but can't seem to set up a functional form that will let me enter the info. The only way I can get it to allow me to enter one-to-many-to-many-to-many info is by nesting a subform in a subform in a subform in a form. This is just too chaotic and I wouldn't dare want to try training someone how to enter the data into that. It's insane!

Keep me posted on what you find out, and I'll continue to research what I can and share my findings.

Thanks,
Jessa Lee

 

[HockeyNut]

My progress so far

I've been experimenting a bit, but I've hit a bit of a wall now.
Attached is my progress so far.

My 2nd subform is based on this:
[Forms]![frmMainClient]![tblApplication].[Form]![ApplicationID]

But, if there is more than one record in the 1st sub form area, it still only returns 1 record in the 2nd sub form, even if there is another record in the 2nd sub form which matches another in the 1st sub form.
It's as though the query only looks at the first [ApplicationID], rather than the whole column on the 1st sub form.


Note: The colour warped when I saved it as a pic, so please ignore the greeeeeeeeeeen!

In my attached example there is a record in the 2nd sub form which is missing, as an [ApplicationID] of 6, but this isn't picked up.

So I was contemplating working out a code/query to make teh 2nd sub form display whichever record on the 1st sub form was selected.

I had a similar problem to yourself where I originally tried a sub form within a subform... etc.

 

[jessa_lee]

Yes, I noticed the same problem when I tried to connect my subforms through queries. It seems it works when you pull info to the forms directly from the table, but then I cannot seem to "bind" or "link" the subforms to each other despite their set relationships.

 

[HockeyNut]

Well, just incase anyone has a simliar problem in future I'll post my findings on this.

The key is something called "Synchronised Subforms". Hope this helps anyone in the future.


I've managed to get mine working, well, my first sub-sub level anyway. I used the code which is in an example form, from Chapter 8 in the "Access 2002 Desktop Developers Handbook".

Essentially it involves putting a unbound text box on your main form (in this case called "txtLink"), and the following code:

Option Explicit
Private Sub Form_AfterUpdate()
Call UpdateLink
End Sub

Private Sub Form_Current()
Call UpdateLink
End Sub

Private Sub UpdateLink()
On Error Resume Next
Me.Parent("txtLink") = Me.OrderId
End Sub

It seems to have got me start now, however only time will tell if it definately fully meets me requirements, I'm still experimenting with it.

HN