form with 2 subforms help

[pvrwizard]

I would have thought this would be pretty simple but either I am missing something really simple or I just haven't found the answer yet. I've seen a couple different solutions for something similiar I am trying to do but nothing that was really exact.

I have 2 tables Customers and Tours 1 customer can have multiple tours.
the customers table has a unique CUSTID field that is the PK. the Tours Table has a CUSTID table that is linked in a one to many from the customer table. the tours table has its own unique TOURID Field which I think is going to be irrelevant for this discussion.

the tour table contains data like date and time of the tour.

I have 1 main form and 2 subforms

The main form simply displays the customer table

the 1st subform is a datasheet that displays the date, time and status of the tours for that customer from the tours table based on the custid of the currently displayed record. so there could be 1 customer with 6 different tour dates and times.

the 2nd subform displays the full tour data for a tour.

What I can't seem to get to work is I want to be able to click a tour date and time record from the datasheet view in the 1st subform and have the 2nd subform change to display that tours data.

Any help would be appreciated.

-Paul

EDIT***
If it's relevant this is access 2003 or 2007

 

[pbaldy]

There are a number of ways to do this type of thing. One would be to set the recordsource of the second subform from the click event of the first:

Forms!Mainform!Subform2.Form.RecordSource = "SELECT...WHERE TourID = " & Me.TourID

 

[RuralGuy]

Another approach is to have an invisible TextBox on the MainForm with the ControlSource pointing to the relevalant field on the first SubForm and the second SubForm LinkMasterField pointing to the invisible TextBox and the LinkChildField pointing to the proper field on the second SubForm.

 

[pvrwizard]

Another approach is to have an invisible TextBox on the MainForm with the ControlSource pointing to the relevalant field on the first SubForm and the second SubForm LinkMasterField pointing to the invisible TextBox and the LinkChildField pointing to the proper field on the second SubForm.

I saw this as a solution on another place.. the invisible text box didn't appear in the drop down list on the link master field.

 

[RuralGuy]

Just type it in, going through the Forms collection: Forms.YourForm.InvisibleControlName ...using your Form and Control names of course.

 

[pvrwizard]

There are a number of ways to do this type of thing. One would be to set the recordsource of the second subform from the click event of the first:

Forms!Mainform!Subform2.Form.RecordSource = "SELECT...WHERE TourID = " & Me.TourID

macro errors when i use this approach

Forms!FCustomers!FToursummary.Form.RecordSource = "SELECT...WHERE TourID = " & Me.TourID

can't find macro Forms!FCustomers!FToursummary

 

[pvrwizard]

Just type it in, going through the Forms collection: Forms.YourForm.InvisibleControlName ...using your Form and Control names of course.

Jesus if that works i'll feel dumb figured if it wasn't in the drop down it didn't see it as a control you could link to.....

 

[RuralGuy]

macro errors when i use this approach

Forms!FCustomers!FToursummary.Form.RecordSource = "SELECT...WHERE TourID = " & Me.TourID

can't find macro Forms!FCustomers!FToursummary

Is FToursummary the name of the SubFormControl that displays the FToursummary form? You know you are looking at the SubFormControl when the data tab has the LinkChild/MasterFields properties.

 

[pvrwizard]

Just type it in, going through the Forms collection: Forms.YourForm.InvisibleControlName ...using your Form and Control names of course.

well didn't work tells me it's not in the list and won't go any furthur.

 

[boblarson]

well didn't work tells me it's not in the list and won't go any furthur.

How about posting your database?
How to post a database on the forum

 

[RuralGuy]

Are you trying to use the SubForm Wizard? Just create the SubForm without any links and then go back in and edit those two properties later.

 

[pvrwizard]

Is FToursummary the name of the SubFormControl that displays the FToursummary form? You know you are looking at the SubFormControl when the data tab has the LinkChild/MasterFields properties.

okay actual form names

FCustomers - main form
FTourSummary - datasheet i want to click to display full data in
FTourDetail

I create an invisible text box on the main form and left the default name to test so it was named Text30 i set the controlsource as =FToursummary.Form!Date

on FTourDetail i went to the link child master lines clicked the ... and tried to type in the Text30.. I realize now that I didn't have to click the ... and just needed to type it into the master field. which I have now done.. now my detail form doesn't display any records.

 

[pvrwizard]

uploading a copy of the data base it has 2 test customers in it. one has 1 tour scheduled the other has 2 tours scheduled.

 

[pvrwizard]

okay here is the database

not very pretty yet but that will come later

 

[RuralGuy]

Your LinkMasterField for the FTourDetail SubFormControl should be: =Forms.FCustomers.Text30. Keep in mind that Date is a reserved name in Access and can get things confused. You should really change the name of that field. Here's a link to a list of reserved words: http://www.allenbrowne.com/AppIssueBadWord.html

EDIT: No "=" ... Forms.FCustomers.Text30

 

[pvrwizard]

so yea figured it our literally 10 seconds after i uploaded.. i changed the name of the form a while back from the default the wizard gave it and never actually went in to the properties and changed it to match.. sigh.. working perfectly now.

 

[pvrwizard]

Your LinkMasterField for the FTourDetail SubFormControl should be: =Forms.FCustomers.Text30. Keep in mind that Date is a reserved name in Access and can get things confused. You should really change the name of that field. Here's a link to a list of reserved words: http://www.allenbrowne.com/AppIssueBadWord.html

Yea I had an idea that might cause an issue.. easy enough to change along with the time field.

 

[RuralGuy]

so yea figured it our literally 10 seconds after i uploaded.. i changed the name of the form a while back from the default the wizard gave it and never actually went in to the properties and changed it to match.. sigh.. working perfectly now.

Thanks for posting back with your success.

 

[pvrwizard]

Thanks for posting back with your success.

Thanks for the guidance I knew it had to be something simple I forgot to do