Subform to Subform Problem...

[EdwardWWW]

Hi,

I'm only a beginner with Access and VBA (on a windows platform - Access 2000) etc so apologies up front.

I have a main form (called frmMAININTERFACE), within which I've inserted a tab control item. Each page in the tab control thing is going to contain a subform, each subform relating to a different table. This means (as far as I know) that the overall main containing form cant be based upon a table (or at least, not inasmuch as it will have any effect on what I'm trying to achieve).

Here is the problem I have. On one of the tab pages within the main form, I have a subform down the left. This is a continuous form displaying only the CompanyName field from the Companies table - just a list really. I'll call this subform LISTSUB. Then, to the right of this subform, I have another subform, containing the entire set of fields from the Companies table (this second subform is called frmCOMPANYDETSUB). Each company name within LISTSUB is set up as a hyperlink, and I want to set this up with an OnCurrent event to synch the records in the frmCOMPANYDETSUB subform to whatever Company name I click on in LISTSUB.

On a previous version of this database, I set this up so that the parent form is driven from the Companies table, and contains all of the details field (phone, address, email etc) for the companies - the LISTSUB form is embedded within the parent form, and it synchs in a very straightforward manner. But because each aspect of the form in this version sits within a tabbed page item, I can't do it the way I did before. In this version, I'm trying to control subform two from subform one and I'm a little puzzled as to the syntax of how to do it.

This is what I've got so far - it can't suss out what it's supposed to be referencing...
___________________________________________

If IsNull(Me.CompanyName) Then
Exit Sub
End If

Dim formname As Form, SyncCriteria As String
Dim F As Form, rs As Object
Set formname = Forms![frmBABYMAININTERFACE]![frmCOMPANYDETSUB].Form

'the bit above is the bit it keeps highlighting as the error

Set F = Forms(formname)
Set rs = F.Recordset.Clone

If InStr(1, Me.CompanyName, "'") > 0 Then
SyncCriteria = "[CompanyName] =" & Chr(34) & Me!CompanyName & Chr(34)
Else
SyncCriteria = "[CompanyName] ='" & Me!CompanyName & "'"
End If

rs.FindFirst SyncCriteria

If rs.EOF Then
MsgBox "No match exists!", 64, formname
Else
F.Bookmark = rs.Bookmark
End If
_____________________________________________

I haven't looked at the actual synching bit yet, coz I haven't yet figured out how to get the 2 to reference each other. Using the code above, it highlights the Setform line, and chucks up the error "Microsoft Access can't find the field 'frmCOMPANYDETSUB' referred to in your expression" so I guess that syntax is the wrong syntax to use.

I don't know whether to use some kind of Parent syntax, or whether to reference the second form via the control name (which is Child6 by the way)

I hope this all makes sense. Can anyone give me any clues as to how I should go about this? It would be *greatly* appreciated.

Thanks.

Edward.

 

[bradcccs]

Hi Ed,

Given that your LISTSUB is only a list, have you considered using a list box (rather than continuous subform). This will avoid having to reference between subforms.

Your second subform frmCOMPANYDETSUB could be based on a query recordsource with the criteria referencing your new list box.

eg: criteria line states: forms!frmMAININTERFACE.NewListBox

Then in the On_Click event of the NewListBox, you will simply need: me.frmCOMPANYDETSUB.requery

Make sense??

Brad.

 

[EdwardWWW]

Subform to Subform Problem

Unfortunately I can't do that because there's other stuff going on in LISTSUB - ie I've got a flag down the left of each record in the list, highlighting the status of each company, plus there's a search field at the top to dynamically query out and find certain records / recordsets based upon the Recordsource. So no go there unfortunately - it's got to be the subform solution.

Thanks for your suggestion tho - any other ideas? I know it can be done coz I've seen it done - I just don't know the syntax or indeed the method.

E.

 

[bradcccs]

On one of the tab pages within the main form, I have a subform down the left. This is a continuous form displaying only the CompanyName field from the Companies table - just a list really.

Was "just a list", now not "just a list" - Ah, the plot thickens.

OK:

Your frmCOMPANYDETSUB should be based on a recordsource with the criteria line for the PK (ID) in the following format:

[forms]![frmMAININTERFACE]![LISTSUB]![CompanyID]

(ensure that the CompanyID and TextBox CompanyID do not have the same name - as done by form wizard etc)

You will then need to requery the frmCOMPANYDETSUB subform on selection from the LISTSUB

On the LISTSUB subform, attach the following to a button (or Form On_Click event etc).

Forms!frmMAININTERFACE!frmCOMPANYDETSUB.Requery

Should work as required.

Cheers

Brad.

 

[EdwardWWW]

...

Looks promising Brad...

It's bedtime for me..but by your leave I'll get onto it and see how we get on first thing in the morning

Thanks.

 

[bradcccs]

'tis Friday morning here, so your tomorrow will be my "Beer O'Clock" time.

I am sure some other poor working soul will be happy to help you whilst I am at the Pub.

Cheers.

Brad.

 

[EdwardWWW]

OK, I tried that - if I understood correctly - what I did was set up a query which queries out the entire field set based upon a query criteria of CompanyID from the Companies Table - the criteria of WHICH CompanyID being pulled from the record being clicked on in LISTSUB.

I then set up the recordsource in COMPANYDETSUB as being pulled from this new query.

Sounds good in principle, but I think the same problem applies here as before - except the other way around. Now it seems that Subform2 has to get it recordsource from Subform1, and it's chucking up an error much the same as when we were trying to reference subform1 from subform2 - "Cannot find LISTSUB". It then highlights the

[forms]![frmMAININTERFACE]![LISTSUB]![CompanyID]

line. It's the same problem as before except reversed

Then to add insult to injury, when you somehow bomb out of the first error by clicking end, when it comes time to requery the record source on the second form, it throws up "Cannot find COMPANYDETSUB" - again it's trying to reference the second form from the first one and it can't find it and we're back to square one.

I don't think it likes that form to form syntax. I read somewhere (whilst scouring the net desperately for an answer) that, if you want to reference a form from another form, Access sees the second subform control (not the form itself, but the subform gizmo) as an object and therefore you have to reference the form that makes up the subform via the subform control (ie the name you've given to the subform, which in this case is Child6). I don't know if this offers any clues as to the syntax issue, but I couldnt get this to work either.

I guess you're probably happily on the beer by now - maybe someone else could offer some advice. (thanks again, by the way).

E.

 

[bradcccs]

Well E,

I have been happily on the beer for ~ 7 hrs, so this advice may be a little "suspect".

Have just returned from the pub (quite early) and noticed you are still having probs.

I have attached the db that I tested the theory on.

Please note: This is not my db structure. It is actually a db that I was helping (liberal use of the term) someone else with. You will have to forgive the naming convention violations in the example.

I have roughed a subform (and a listbox) example on a tabbed control form. (Acc 2000 Version - Let me know if not suitable)

Have a good day. (I am off to bed in order to nurse a hangover tomorrow)

Cheer(zzzzz)

Brad.

 

[EdwardWWW]

Sweeet

Well Dude, after much messing around with that example (an MDB paints a thousand words) finally got it to work.

As I suspected, you did reference it via the control rather than the form. I've just spent the last 4 hours trying to figure out why it still wasnt working, only to find an errant space in the query - but that's another story.

Thanks for all your help dude, good luck with the hangover

Cheers,

E.