Subform Field linking?

[phillsheen]

Hi Guys,

Not quite sure where to start with this one so I need a little help. I have a main form which is split up with tabs which all contain subforms of various different forms and queries. I have attached pictures for clarity.

On Pic1 there are two subqueries. Pic2 shows the subform of our client database. What I have been asked to do is to be able to jump to the relevant clients details (pic2) by double clicking on the membership number on the subquery (pic1). The membership number is on the first column on both subqueries.

I’m totally open on ideas of how to do this one if the double click idea is a little too much to ask, maybe a command button would work better?

If anyone has any ideas or examples Id be very grateful!

Cheers
Phill

PS Excuse the scruffyness of the forms as there still under construction

 

[CyberLynx]

In your Summery Tab, if your first SubForm with the Query war built using the SubForm Wizard, a Form was automatically created and it would most likely be named: QryActiveAdInDebt subform

The second SubForm within the Summery Tab looks like it was generated based from a Table named: Frozen Members and the SubForm created is most likely named Frozen Members subform

The following assumes that the SubForm within the Members tab of your application is named Members subform AND that the SubForm control within the Members tab is named the same.

From the Database Objects Window, in the Forms Section, open the SubForm named QryActiveAdInDebt subform into Design View.

Select the MemNumber Text Box and then in the Properties Window select the Events tab. Look down to the On Dbl Click property and double-click into the properties box for this event....Event Procedure should be automatically placed into the Property. On the far right of the property window select he button with the two dots in it. The VBA Editor should open up and he Editing cursor should be within the Event sub procedure. Copy and paste this code into this event procedure:

[COLOR="DarkGreen"]'Set a Filter to the Members subform.
'It is assumed that [MemNumber] is the name of the Member Number field in te Table [/COLOR]
[COLOR="DarkGreen"]'that is bound to the Member Subform.[/COLOR]
Forms("frmMainForm")("Members subform").Form.Filter = [MemNumber] = [COLOR="Red"][B]'[/B][/COLOR]" & Me.MemNumber & "[B][COLOR="Red"]'[/COLOR][/B]"

[COLOR="DarkGreen"]'Turn the Filter ON for the Members subform.[/COLOR]
Forms("frmMainForm")("Members subform").Form.FilterOn = True

[COLOR="DarkGreen"]'Display the Members Tab.
'Members Tab must be the second Tab. THe First Tab is always 0[/COLOR]
Forms("frmMainForm").TabControlName.Value = 1

Do the very same thing for the MemNumber Text Box within the Frozen Members subform.

That should do it. The main thing here is that the controls and Table Fields are properly named within the code. For example the code line:

Forms("frmMainForm").TabControlName.Value = 1

assumes that the Tab Control on your Main Form is named TabControlName. You will need to place in the proper name for this Control.

Hope that helps......and if there are any spelling mistakes, I blame it on my wireless keyboard.

.

 

[phillsheen]

Great reply thank you very much!

I have put the code into my database but I have a two problems. When I try to run the code it comes up with the error 'cant find the field 'members' which to me seems that its looking for a field not a subform???

Also at the end of the code

Forms("frmMainForm")("Members subform").Form.Filter = [MemNumber] = '" & Me.MemNumber & "'"

these seems to be one to many '?

Cheers for the reply!

Phill

 

[CyberLynx]

To begin with you need to make sure that the Main Form name is frmMainForm

The SubForm Control name is Members subform.

The Table Field that hold the member Number is named MemNumber.

You need to make sure the proper names are in in code. In my previous post....I assumed a lot of the names.

Is that a SubForm within the Members Tab? If so, what is the Name of the Control Box the SubForm is contained in. Whatever that name is goes here (in italic red):

Forms("frmMainForm")("[I][COLOR="Red"]Members subform[/COLOR][/I]").Form.Filter = [MemNumber] = '" & Me.MemNumber & "'"

The actual Form contained within the SubForm Control within the Members tab is either bound to a Table or Query. What is the Field Name used within that Table or Query? Is it MemNumber? If not...put in the proper name in code.

.

 

[phillsheen]

Here is the code with all the references updated:

Private Sub MemNumber_DblClick(Cancel As Integer)

'Set a Filter to the Members subform.
'It is assumed that [MemNumber] is the name of the Member Number field in te Table
'that is bound to the Member Subform.
Forms("frmMainForm")("Members").Form.Filter = [MemNumber] = " & Me.MemNumber & " '"

'Turn the Filter ON for the Members subform.
Forms("frmMainForm")("Members").Form.FilterOn = True

'Display the Members Tab.
'Members Tab must be the second Tab. THe First Tab is always 0
Forms("frmMainForm").TabControlName.Value = 1

End Sub

Where do I find the Subform Control Name and the control box the sub form is contained in? I cant find any reference to them in the help file?

Yes it is a subform with in the members tab. How do I reference to this?

Thanks for the reply!
Phill

 

[CyberLynx]

- Place the Main Form into Design View;

- Select the Members tab on the Tab Control

- Select the container that normally displays the Members Sub Form

- In the MS-Access Menu bar select View | Properties. The Properties Window will open.

- Select the Other tab located t the top of the Properties Window

Can you see the Name property. This is the name of the SubForm container. Normally, your SubForm contained within the Forms section of the Database Objects Window is named the Same if the SubForm wizard created the SubForm.

It is the SubForm Container name we want to use in code. Not the SubForm name itself. For Example:

Forms("frmMainForm")("Sub Form Container Name").Form.Filter =

The code itself needs to be placed into the On Dbl Click event for the Text Box that displays the Member Number within each of your actual Sub Forms (the Form itself).

If you send a sample of your Database with some fictitious data then I can apply the code for you and explain it somewhat better since then I would know the true names of you Tables, Table Fields, Forms and SubForms, and Form Controls.

.

 

[phillsheen]

Hi!

Ok i did as you said and it kind of works. The only problem is it doesnt filter the form, just comes up with a blank form. Could the error be in this line?

Forms("frmMainForm")("MembersSub").Form.Filter = [MemNumber] = "" & Me.MemNumber & """"

At the end of the code was an error on the origional one you sent me. I looked at some examples and threw a few " in to see if that would work?

Thanks for the reply!
Phill

 

[CyberLynx]

Phil, just use apostrophes instead of all those quote marks:

Forms("frmMainForm")("MembersSub").Form.Filter = [MemNumber] = '" & Me.MemNumber & "'"

.

 

[phillsheen]

When I use that line of code I get a 'syntax error'?

 

[phillsheen]

Im trying to attach the database but its to big. Any alternative ways of sending it?

 

[CyberLynx]

If you use a File Compression program like WinZIP or WinRAR then you can compress the Database file and it should be small enough to send. Do a Compact and Repair first. Access likes to bloat so badly even after running once.

Start the DB while holding down the SHIFT KEY. Keep the SHIFT KEY held down at all times. When the Database has loaded in and you still have you finger on the SHIFT KEY....do a Compact and Repair. When the compacting is complete close the Database and then use a File compression program to Compress the MDB file.

Just in case...I will Private message you with my E-Mail address so that you can send it up that way.

.

 

[CyberLynx]

The reason you are getting the Syntax Error is because the line:

Forms("frmMainForm")("MembersSub").Form.Filter = [MemNumber] = '" & Me.MemNumber & "'"

is in fact in Error and this is my fault. I give you my deepest apologies

You do have the line right, it's just that it's missing a Quote mark as shown below in red:

Forms("frmMainForm")("MembersSub").Form.Filter = "[MemNumber] = '" & Me.MemNumber & "'"

With this correction all should work fine.

.