Another question on linking subforms...

[aweizd]

Hi,

I tried searching for an answer, but with no luck.
My problem is with linking master/child forms.
What I want the subform to do is to show me a list of records that have the same "GroupID" as the master form. To do that I set the link properties to:

Link Master Fields = "GroupID"
Link Child Fields = "GroupID"

This works perfectly fine! Now I wanted to take it a step further and add another link so now I have this:

Link Master Fields = "GroupID;LastName"
Link Child Fields = "GroupID;LastName"

Now what (I figured) Access does is show me the records where the "GroupID fields match AND where the LastName fields match". But what I really wanted was that it displays the records where the "GroupID fields match OR where the LastName fields match".

Can anybody suggest something?

Thanks
Luke

 

[KenHigg]

The best that I can come up with is to do a set of radio buttons that swap the links so that you're either linking on id or name...

Would this work?

 

[aweizd]

Thanks for the fast reply...

Well I guess that would be a way to do it, but if it's possible I'd rather have it all in one list. Thanks a lot for the suggestion. I'll keep looking for a solution, but if I don't find anything, then I guess radio buttons are the way to go. It does kinda seem like a rather stupid limitation of Access...

Thanks again

P.S. This reminds me... when I link the forms like this:
Link Master Fields = "GroupID"
Link Child Fields = "GroupID"

The subform should also show the record that is currently being view in the master, right?? I there a way to show a text like "Currently viewed" next to this record in the subform??

My subform is another form called "List" and it's "Default View" is set to "Continuous Forms". On the form I made a label which says "Currently viewed" and is invisible by default. The script I wrote to check if the record matches the one in the master form and make the label visible, seems to be totally wrong...

Can you show me how this should be done?
Thanks again...

 

[aweizd]

Hi again,

Just in case anybody would be interested I found the solution to my problem ! I didn't do a lot of testing but yet but it seems to work. This is what I did:

I cleared the Link Child and Link Master fields and I set the subform to get data from a query. The query looks sort of like this:

SELECT MainTable.*
FROM MainTable
WHERE ((MainTable.GroupID)=FetchGroupID()) Or ((MainTable.LastName)=FetchLastName());

In a new module I created the functions to Fetch and to Set the appropriate variables. The module code looks like this:

Option Compare Database
Option Explicit

Private varGroupID As Integer
Private varLastName As Integer

Public Function FetchGroupID() As Integer
On Error Resume Next
    FetchGroupID = varGroupID
End Function

Public Sub SetGroupID(ByVal NewGroupID As Integer)
On Error Resume Next
    varGroupID = NewGroupID
End Sub

Public Function FetchLastName() As Integer
On Error Resume Next
    FetchLastName = varLastNameID
End Function

Public Sub SetLastName(ByVal NewLastName As Integer)
On Error Resume Next
    varLastName = NewLastName
End Sub

Then I tell the master form to set the variables to whatever I want (using the Set...() functions), then the child uses the query to fetch the appropriate records and voila!!! The subform shows the data you want!!!

This code is just a modification of what I found on this website . @author: thanx for this cool example!

Hope this helps someone!
Luke