If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Recordset in subform based on field in parent form
Hi,
I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#2
|
|||
|
|||
Recordset in subform based on field in parent form
Hi Lyn
You could try: WHERE (Person.IDFather = Nz(Forms!frmMainForm!IDFath,0)) OR (Person.IDMother = Nz(Forms!frmMainForm!IDMoth,0)) The Nz() should cope with the case where the main form is at a new record. However, you will probably get an initial error on load, because Access loads the subform before the main form. On your broader question, you can join a table to itself, by dragging multiple copies of the table into the Relationships window. There is an example of this with animal pedigrees at: http://allenbrowne.com/ser-06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#3
|
|||
|
|||
Recordset in subform based on field in parent form
Lyn, just put the following in the criteria line of the query:
[Forms]![frmMainForm]![IDFath] Or [Forms]![frmMainForm]![IDMoth] Hope it helps! -- Reggie www.smittysinet.com ---------- "Lyn" wrote in message ... Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#4
|
|||
|
|||
Recordset in subform based on field in parent form
Allen,
Thanks for your suggestions. The animal pedigree tip looks very similar to what I am doing. I will give this a try. BTW, I live in Sydney. -- Cheers, Lyn. "Allen Browne" wrote in message u... Hi Lyn You could try: WHERE (Person.IDFather = Nz(Forms!frmMainForm!IDFath,0)) OR (Person.IDMother = Nz(Forms!frmMainForm!IDMoth,0)) The Nz() should cope with the case where the main form is at a new record. However, you will probably get an initial error on load, because Access loads the subform before the main form. On your broader question, you can join a table to itself, by dragging multiple copies of the table into the Relationships window. There is an example of this with animal pedigrees at: http://allenbrowne.com/ser-06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#5
|
|||
|
|||
Recordset in subform based on field in parent form
Thanks Reggie. From all the responses, it looks as though the main thing I
was missing was the "Forms!" prefix. I have gotten too used to the "Me!" shortcut which of course does not work here. -- Cheers, Lyn. "Reggie" wrote in message ... Lyn, just put the following in the criteria line of the query: [Forms]![frmMainForm]![IDFath] Or [Forms]![frmMainForm]![IDMoth] Hope it helps! -- Reggie www.smittysinet.com ---------- "Lyn" wrote in message ... Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#6
|
|||
|
|||
Recordset in subform based on field in parent form
Thanks again to everyone who responded to my original question. I think
Allen was right, using a control value in the main form as a criterion does not work because the subform loads before the main form. The Person record must be available before the main form or subform opens. So I have tried using multiple copies of the table (Person) in the query (with alias names) with different types of joins. Firstly, just the one alias (Person_Sib) joined by the IDFather field in each table (I have left out IDMother for simplicity). The Person table in the query is intended to be the record in the main form, and the Person_Sib alias is intended to find all the records with the same IDFather. However, in this case, Person_Sib just seems to track Person -- ie, Person_Sib returns only the same (single) record as Person. So I have tried a different tack. I inserted a second alias of Person (Person_Father) between the two original tables in the query. In this case, Person.IDFather is joined to Person_Father.IDPerson (the autonum record ID) which is joined to Person_Sib.IDFather. At this point I got confused over what sort of joins to use. Using INNER joins for both (which I am sure is wrong), Person_Sib still seems to track the same record as Person. The Person record is the subject of the main form. I need the Person_Sib alias to list ALL records with the same IDFather (and eventually "OR same IDMother"). I will of course eventually filter out the Person_Sib record that matches the Person in the main form (who cannot be his/her own sibling). How can I do this in the Access query? Or do I have to resort to VBA to create the sibling recordset? What am I doing wrong? (I hope that this makes sense.) Thanks for any further assistance. -- Cheers, Lyn. "Allen Browne" wrote in message u... Hi Lyn You could try: WHERE (Person.IDFather = Nz(Forms!frmMainForm!IDFath,0)) OR (Person.IDMother = Nz(Forms!frmMainForm!IDMoth,0)) The Nz() should cope with the case where the main form is at a new record. However, you will probably get an initial error on load, because Access loads the subform before the main form. On your broader question, you can join a table to itself, by dragging multiple copies of the table into the Relationships window. There is an example of this with animal pedigrees at: http://allenbrowne.com/ser-06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#7
|
|||
|
|||
Recordset in subform based on field in parent form
Could you set the subform so it intially loads no-one:
SELECT * FROM MyTable WHERE (False); Then use the Current event of the main form to assign its RecordSource. Something like this: Dim strWhere As String If Me.NewRecord Then strWhere = "(False);" Else strWhere = "(Person_Father.IDPerson = " & Me.IDPerson & ") OR (Person_Mother.IDPerson = " & Me.IDPerson & ");" Me.[Child1].Form.RecordSource = "SELECT * FROM SomeTable WHERE " & strWhere End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... Thanks again to everyone who responded to my original question. I think Allen was right, using a control value in the main form as a criterion does not work because the subform loads before the main form. The Person record must be available before the main form or subform opens. So I have tried using multiple copies of the table (Person) in the query (with alias names) with different types of joins. Firstly, just the one alias (Person_Sib) joined by the IDFather field in each table (I have left out IDMother for simplicity). The Person table in the query is intended to be the record in the main form, and the Person_Sib alias is intended to find all the records with the same IDFather. However, in this case, Person_Sib just seems to track Person -- ie, Person_Sib returns only the same (single) record as Person. So I have tried a different tack. I inserted a second alias of Person (Person_Father) between the two original tables in the query. In this case, Person.IDFather is joined to Person_Father.IDPerson (the autonum record ID) which is joined to Person_Sib.IDFather. At this point I got confused over what sort of joins to use. Using INNER joins for both (which I am sure is wrong), Person_Sib still seems to track the same record as Person. The Person record is the subject of the main form. I need the Person_Sib alias to list ALL records with the same IDFather (and eventually "OR same IDMother"). I will of course eventually filter out the Person_Sib record that matches the Person in the main form (who cannot be his/her own sibling). How can I do this in the Access query? Or do I have to resort to VBA to create the sibling recordset? What am I doing wrong? (I hope that this makes sense.) Thanks for any further assistance. -- Cheers, Lyn. "Allen Browne" wrote in message u... Hi Lyn You could try: WHERE (Person.IDFather = Nz(Forms!frmMainForm!IDFath,0)) OR (Person.IDMother = Nz(Forms!frmMainForm!IDMoth,0)) The Nz() should cope with the case where the main form is at a new record. However, you will probably get an initial error on load, because Access loads the subform before the main form. On your broader question, you can join a table to itself, by dragging multiple copies of the table into the Relationships window. There is an example of this with animal pedigrees at: http://allenbrowne.com/ser-06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#8
|
|||
|
|||
Recordset in subform based on field in parent form
Allen,
I have tried your suggestion and variations of it, but no luck. After a lot of trial and error, I have resorted to using VBA code similar to what you provided in your previous. I have simplified the requirements to the bare minimum, just to get something that works, then I can build it up again. I now have a form containing a subform (sfmSibs) in a tab control. I have removed any query from the subform's Record Source. In the Current event for the main form, which I already use to populate some unbound controls on the main form, I have added the following code: Dim mySQL As String If Not IsNull(Me![IDFather]) And Me![IDFather] 0 Then mySQL = "SELECT Person.SurnameBirth, Person.FirstName, " & _ "Person.DOB, Person.Sex, " & _ "Person.IDPerson FROM Person " & _ "WHERE Person.IDFather = " & Me![IDFather] & ";" Me.[sfmSibs].Form.RecordSource = mySQL End If When I run this, if Me![IDFather] is null or zero, of course it works (blank form as expected). However, any record with a non-zero Me![IDFather] gives this error: Run-time error '2465': Microsoft Office Access can't find the field "|" referred to in your expression. Not very helpful, as I don't have "|" anywhere in the expression. (Once when I mistyped a word, this error message displayed the word in error, but not this time.) When I click Debug, the following line is highlighted: Me.[sfmSibs].Form.RecordSource = mySQL I presume that the problem is somewhere in the mySQL string. I have tried it with and without the terminating semicolon ";" (VBA often seems to insert that for you), but no effect. Any ideas how to troubleshoot this when the field in error is quoted as "|" ?!?! A related question: instead of doing this in the Current event of the main form, could I do it in the On Click event of the tab control Page? So that the subform is not populated unless the relevant tab is clicked. Just trying to learn as much as I can from this. Thanks again for your help. -- Cheers, Lyn. "Allen Browne" wrote in message . au... Could you set the subform so it intially loads no-one: SELECT * FROM MyTable WHERE (False); Then use the Current event of the main form to assign its RecordSource. Something like this: Dim strWhere As String If Me.NewRecord Then strWhere = "(False);" Else strWhere = "(Person_Father.IDPerson = " & Me.IDPerson & ") OR (Person_Mother.IDPerson = " & Me.IDPerson & ");" Me.[Child1].Form.RecordSource = "SELECT * FROM SomeTable WHERE " & strWhere End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... Thanks again to everyone who responded to my original question. I think Allen was right, using a control value in the main form as a criterion does not work because the subform loads before the main form. The Person record must be available before the main form or subform opens. So I have tried using multiple copies of the table (Person) in the query (with alias names) with different types of joins. Firstly, just the one alias (Person_Sib) joined by the IDFather field in each table (I have left out IDMother for simplicity). The Person table in the query is intended to be the record in the main form, and the Person_Sib alias is intended to find all the records with the same IDFather. However, in this case, Person_Sib just seems to track Person -- ie, Person_Sib returns only the same (single) record as Person. So I have tried a different tack. I inserted a second alias of Person (Person_Father) between the two original tables in the query. In this case, Person.IDFather is joined to Person_Father.IDPerson (the autonum record ID) which is joined to Person_Sib.IDFather. At this point I got confused over what sort of joins to use. Using INNER joins for both (which I am sure is wrong), Person_Sib still seems to track the same record as Person. The Person record is the subject of the main form. I need the Person_Sib alias to list ALL records with the same IDFather (and eventually "OR same IDMother"). I will of course eventually filter out the Person_Sib record that matches the Person in the main form (who cannot be his/her own sibling). How can I do this in the Access query? Or do I have to resort to VBA to create the sibling recordset? What am I doing wrong? (I hope that this makes sense.) Thanks for any further assistance. -- Cheers, Lyn. "Allen Browne" wrote in message u... Hi Lyn You could try: WHERE (Person.IDFather = Nz(Forms!frmMainForm!IDFath,0)) OR (Person.IDMother = Nz(Forms!frmMainForm!IDMoth,0)) The Nz() should cope with the case where the main form is at a new record. However, you will probably get an initial error on load, because Access loads the subform before the main form. On your broader question, you can join a table to itself, by dragging multiple copies of the table into the Relationships window. There is an example of this with animal pedigrees at: http://allenbrowne.com/ser-06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#9
|
|||
|
|||
Recordset in subform based on field in parent form
Hi Lyn.
To debug this, ask Access to print mySQL out to the immediate window: mySQL = "SELECT ... Debug.Print mySQL Me.[sfmSibs].Form... Now try it. Open the Immediate window (Ctrl+G). Access will have printed the faulty SQL statement there. Copy it to clipboard. Then create a new query, switch it to SQL View (View menu), and paste in the faulty SQL string. This should help you to see what's wrong, and you will see how to fix the assigment to mySQL. The pipe character is a place holder for the field name. It appears that you are getting the error message without the correct name being inserted into the place holder. Ultimately it means that Access can't make sense of the SQL statement. For example, if IDFather is actually a Text field (not a Number field), then the SQL statement needs exta quote marks: "WHERE Person.IDFather = """ & Me![IDFather] & """;" Another cause of the error is Name AutoCorrect incorrectly tracking the field name. Uncheck the boxes under Tools | Options | General | Name AutoCorrect, and then compact the database (Tools | Database Utilities). You're on the right track, so it is worth continuing to debug this. Do include the trailing semicolon in the sql statement. It would be possible to use the Change event of the tab control (not the Click event of the page) rather than the Current event of the main form. The problem with that approach is that it leaves the wrong data showing in the subform when you change record in the main form, until you move to a different tab and move back to the one you want again. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... Allen, I have tried your suggestion and variations of it, but no luck. After a lot of trial and error, I have resorted to using VBA code similar to what you provided in your previous. I have simplified the requirements to the bare minimum, just to get something that works, then I can build it up again. I now have a form containing a subform (sfmSibs) in a tab control. I have removed any query from the subform's Record Source. In the Current event for the main form, which I already use to populate some unbound controls on the main form, I have added the following code: Dim mySQL As String If Not IsNull(Me![IDFather]) And Me![IDFather] 0 Then mySQL = "SELECT Person.SurnameBirth, Person.FirstName, " & _ "Person.DOB, Person.Sex, " & _ "Person.IDPerson FROM Person " & _ "WHERE Person.IDFather = " & Me![IDFather] & ";" Me.[sfmSibs].Form.RecordSource = mySQL End If When I run this, if Me![IDFather] is null or zero, of course it works (blank form as expected). However, any record with a non-zero Me![IDFather] gives this error: Run-time error '2465': Microsoft Office Access can't find the field "|" referred to in your expression. Not very helpful, as I don't have "|" anywhere in the expression. (Once when I mistyped a word, this error message displayed the word in error, but not this time.) When I click Debug, the following line is highlighted: Me.[sfmSibs].Form.RecordSource = mySQL I presume that the problem is somewhere in the mySQL string. I have tried it with and without the terminating semicolon ";" (VBA often seems to insert that for you), but no effect. Any ideas how to troubleshoot this when the field in error is quoted as "|" ?!?! A related question: instead of doing this in the Current event of the main form, could I do it in the On Click event of the tab control Page? So that the subform is not populated unless the relevant tab is clicked. Just trying to learn as much as I can from this. Thanks again for your help. -- Cheers, Lyn. "Allen Browne" wrote in message . au... Could you set the subform so it intially loads no-one: SELECT * FROM MyTable WHERE (False); Then use the Current event of the main form to assign its RecordSource. Something like this: Dim strWhere As String If Me.NewRecord Then strWhere = "(False);" Else strWhere = "(Person_Father.IDPerson = " & Me.IDPerson & ") OR (Person_Mother.IDPerson = " & Me.IDPerson & ");" Me.[Child1].Form.RecordSource = "SELECT * FROM SomeTable WHERE " & strWhere End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... Thanks again to everyone who responded to my original question. I think Allen was right, using a control value in the main form as a criterion does not work because the subform loads before the main form. The Person record must be available before the main form or subform opens. So I have tried using multiple copies of the table (Person) in the query (with alias names) with different types of joins. Firstly, just the one alias (Person_Sib) joined by the IDFather field in each table (I have left out IDMother for simplicity). The Person table in the query is intended to be the record in the main form, and the Person_Sib alias is intended to find all the records with the same IDFather. However, in this case, Person_Sib just seems to track Person -- ie, Person_Sib returns only the same (single) record as Person. So I have tried a different tack. I inserted a second alias of Person (Person_Father) between the two original tables in the query. In this case, Person.IDFather is joined to Person_Father.IDPerson (the autonum record ID) which is joined to Person_Sib.IDFather. At this point I got confused over what sort of joins to use. Using INNER joins for both (which I am sure is wrong), Person_Sib still seems to track the same record as Person. The Person record is the subject of the main form. I need the Person_Sib alias to list ALL records with the same IDFather (and eventually "OR same IDMother"). I will of course eventually filter out the Person_Sib record that matches the Person in the main form (who cannot be his/her own sibling). How can I do this in the Access query? Or do I have to resort to VBA to create the sibling recordset? What am I doing wrong? (I hope that this makes sense.) Thanks for any further assistance. -- Cheers, Lyn. "Allen Browne" wrote in message u... Hi Lyn You could try: WHERE (Person.IDFather = Nz(Forms!frmMainForm!IDFath,0)) OR (Person.IDMother = Nz(Forms!frmMainForm!IDMoth,0)) The Nz() should cope with the case where the main form is at a new record. However, you will probably get an initial error on load, because Access loads the subform before the main form. On your broader question, you can join a table to itself, by dragging multiple copies of the table into the Relationships window. There is an example of this with animal pedigrees at: http://allenbrowne.com/ser-06.html "Lyn" wrote in message ... I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list all the brothers and sisters of the person currently being displayed in the main form. The basis of the selection is all records which have the same father ID or mother ID field values (excluding of course the person displayed in the main form). I am trying to use the values in the controls on the main form where the father ID and mother ID are displayed in the subform query. I created the subform originally as a form with a query in which I hardcoded test values for the father ID and mother ID. This works fine. Then I added this form as a subform in the Tab Control of the main form. I viewed the SQL for the query and updated it by substituting the values of the controls on the main form for the hardcoding. That is, instead of using the following format in the SELECT statement: WHERE Person.IDFather = 5 OR Person.IDMother = 6 ... I substituted: WHERE Person.IDFather = frmMainForm!IDFath OR Person.IDMother = frmMainForm!IDMoth ... I obviously have this syntax wrong, because when I run the main form, for each record I get msgboxes prompting for the values frmMainForm!IDFath and frmMainForm!IDMoth. If I respond with the correct values, the subform displays the correct data. I hope that this makes some sense. Essentially my question is: how can I make the query in the subform use criteria displayed in controls on the main form? Another aspect of this is that all the books and tutorials I have seen assume that the subform will be used to display data from a different but related table from the record in the main form. In my case, there is only one table and I want to display related records (via the parent IDs) from that same table. Am I not allowed to do this? Or will I need some sort of additional crossreference table? Thanks in advance for any assistance. -- Cheers, Lyn. |
#10
|
|||
|
|||
Recordset in subform based on field in parent form
Thanks again, Allen. This has given me some good debugging tools.
I did as you suggested, all steps, but without any improvement. However, I did discover the main problem. Rather than continually update the same subform and same query, I had created new versions of each. In the end I had about eight versions of the subform and a similar number of queries. Updating the query version of a form or subform is easy -- you just update the Record Source property. But I could not find any analogue of this for updating the subform version in the Form properties. I suspected that I might have had the wrong subform version configured in the main form. The only way I could find to fix this was to delete the current subform and to recreate it from the Toolbox so that I could get into the Subform Wizard. Is there any other way to invoke the Subform Wizard? Anyway, after deleting and recreating the subform in the tab control page of the main form, the error went away. I now get data in the subform, but it is always only the same (single) Person record as in the main form. That is, if I have a number of brothers and sisters, all with the same IDFather, only my own record is displayed in the subform. Which I eventually want to suppress, leaving only brothers and sisters. It seems that instead of the subforms Record Source being the whole of the Person table, it is being restricted to the one record already selected by the main form. I had expected the VBA effectively to create a new recordset (I have successfully used such recordsets in this manner elsewhere in the main form VBA). How can I make the VBA for the subform create a new Record Source based on the WHOLE of the Person table, rather than the single record already selected by the main form? I feel that I am getting closer to the solution, there is just one piece of the puzzle still missing. -- Cheers, Lyn. "Allen Browne" wrote in message . au... Hi Lyn. To debug this, ask Access to print mySQL out to the immediate window: mySQL = "SELECT ... Debug.Print mySQL Me.[sfmSibs].Form... Now try it. Open the Immediate window (Ctrl+G). Access will have printed the faulty SQL statement there. Copy it to clipboard. Then create a new query, switch it to SQL View (View menu), and paste in the faulty SQL string. This should help you to see what's wrong, and you will see how to fix the assigment to mySQL. The pipe character is a place holder for the field name. It appears that you are getting the error message without the correct name being inserted into the place holder. Ultimately it means that Access can't make sense of the SQL statement. For example, if IDFather is actually a Text field (not a Number field), then the SQL statement needs exta quote marks: "WHERE Person.IDFather = """ & Me![IDFather] & """;" Another cause of the error is Name AutoCorrect incorrectly tracking the field name. Uncheck the boxes under Tools | Options | General | Name AutoCorrect, and then compact the database (Tools | Database Utilities). You're on the right track, so it is worth continuing to debug this. Do include the trailing semicolon in the sql statement. It would be possible to use the Change event of the tab control (not the Click event of the page) rather than the Current event of the main form. The problem with that approach is that it leaves the wrong data showing in the subform when you change record in the main form, until you move to a different tab and move back to the one you want again. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Lyn" wrote in message ... Allen, I have tried your suggestion and variations of it, but no luck. After a lot of trial and error, I have resorted to using VBA code similar to what you provided in your previous. I have simplified the requirements to the bare minimum, just to get something that works, then I can build it up again. I now have a form containing a subform (sfmSibs) in a tab control. I have removed any query from the subform's Record Source. In the Current event for the main form, which I already use to populate some unbound controls on the main form, I have added the following code: Dim mySQL As String If Not IsNull(Me![IDFather]) And Me![IDFather] 0 Then mySQL = "SELECT Person.SurnameBirth, Person.FirstName, " & _ "Person.DOB, Person.Sex, " & _ "Person.IDPerson FROM Person " & _ "WHERE Person.IDFather = " & Me![IDFather] & ";" Me.[sfmSibs].Form.RecordSource = mySQL End If When I run this, if Me![IDFather] is null or zero, of course it works (blank form as expected). However, any record with a non-zero Me![IDFather] gives this error: Run-time error '2465': Microsoft Office Access can't find the field "|" referred to in your expression. Not very helpful, as I don't have "|" anywhere in the expression. (Once when I mistyped a word, this error message displayed the word in error, but not this time.) When I click Debug, the following line is highlighted: Me.[sfmSibs].Form.RecordSource = mySQL I presume that the problem is somewhere in the mySQL string. I have tried it with and without the terminating semicolon ";" (VBA often seems to insert that for you), but no effect. Any ideas how to troubleshoot this when the field in error is quoted as "|" ?!?! A related question: instead of doing this in the Current event of the main form, could I do it in the On Click event of the tab control Page? So that the subform is not populated unless the relevant tab is clicked. Just trying to learn as much as I can from this. Thanks again for your help. -- Cheers, Lyn. |
|
Thread Tools | |
Display Modes | |
|
|