How to open form with subset of data?

[Annalyzer]

This should be an easy one, but I can't figure it out.

frmProvider (data source: tblProvider) displays information about child care providers. Since tblProvider is fairly large, I am using a tabbed control to display all the fields on frmProvider.

In addition to the tabs which contain the provider information, I have another tab that displays a sub-form (sfrmProviderChildren) of the children registered to this provider. This sub-form gets it's data from a query that pulls data from tblChild and tblRegistration (a bridge table to tie tblProvider to tblChildren together in a many-to-many relationship). Then, the subform is tied to it's parent from with provider_id (child field) and id (master field).

A second form in the database, called frmChild, displays information on the children from tblChild.

I have a control (cmdAddKids) on frmProvider that is supposed to open frmChild with a subset of records that contains only the children registered to the current provider.

Here are the structures for the tables involved:

tblProvider
tblprovider.id (PK)
tblprovider.lname
tblprovider.fname

tblChild
tblchild.id (PK)
tblchild.lname
tblchild.fname

tblRegistration
tblRegistration.provider_id (PK)
tblRegistration.child_id (PK)

Here is my code, but it opens frmChild with all kids instead of just the ones registered to to the current provider:

Private Sub cmdAddKids_Click()
On Error GoTo Err_cmdAddKids_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmChild"
    stLinkCriteria = "[tblAssignment].[provider_id]=" & Me![id]
 
    DoCmd.OpenForm stDocName, , , , acFormEdit
 
Exit_cmdAddKids_Click:
    Exit Sub
 
Err_cmdAddKids_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddKids_Click
 
End Sub

Help??

[puppydogbuddy]

Change Line 10 of your code:
>>>>>>>> DoCmd.OpenForm stDocName, , , , acFormEdit

To This:
>>>>>>>> DoCmd.OpenForm stDocName, , ,stLinkCriteria , acFormEdit

[Annalyzer]

I had the criteria in there originally, but it didn't recognize the field provider_id.

As a side note, I just realized that I posted the actual code from my database, but the table structure and explanation refer to tblAssignment as tblRegistration (I thought that would make more sense). In the actual database, the table names do actually match:

tblProvider
tblprovider.id (PK)
tblprovider.lname
tblprovider.fname

tblChild
tblchild.id (PK)
tblchild.lname
tblchild.fname

tblAssignment
tblAssignment.provider_id (PK)
tblAssignment.child_id (PK)

Even with the right table names and the criteria included in the string, it doesn't work. It doesn't recognize tblAssignment.provider_id and so pulls no kids into the form at all. When I remove the criteria, of course, it pulls all kids into the form.

[PianoMan64]

I had the criteria in there originally, but it didn't recognize the field provider_id.

As a side note, I just realized that I posted the actual code from my database, but the table structure and explanation refer to tblAssignment as tblRegistration (I thought that would make more sense). In the actual database, the table names do actually match:

tblProvider

tblprovider.id (PK)
tblprovider.lname
tblprovider.fname

tblChild
tblchild.id (PK)
tblchild.lname
tblchild.fname

tblAssignment
tblAssignment.provider_id (PK)
tblAssignment.child_id (PK)

Even with the right table names and the criteria included in the string, it doesn't work. It doesn't recognize tblAssignment.provider_id and so pulls no kids into the form at all. When I remove the criteria, of course, it pulls all kids into the form.

I have a question, is there ever a case where a child is going to have more than one provider and any one time or could a child have more than one provider?

[puppydogbuddy]

If your button is on the main form, try this:

Change this:
stLinkCriteria = "[tblAssignment].[provider_id]=" & Me![id]

To this:
stLinkCriteria = "[tblAssignment].[provider_id]=" & Me!YourSubformControl.Form![id]

Replace YourSubformControl above with the actual name of your subform control.

[Annalyzer]

Yes, Piano Man, most of the kids have more than one provider. Some have 2 and a few have 3 (in case their main provider is ill, they need a backup place to go). In my original database, frmChild had 3 fields for primary, secondary, and tertiary, but then along came the child with - you guessed it - 4 providers! I can't imagine needing 3 backup providers, but apparently some parents believe in redundancy ad infinitum.

PuppyDogBuddy, I tried your idea, but I get the message "Access can't find the field 'provider_id' referred to in your expression."

stLinkCriteria = "[tblAssignment].[provider_id]=" & Me![provider_id].Form![id]
 
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit

provider_id is the control on the subfrom (sfrmKids4ProviderForm) since it pulls it's data using a query that INNER JOINs tblProvider with tblAssignment. Even when I qualify it:

stLinkCriteria = "[tblAssignment].[provider_id]=" & Me![sfrmKids4ProviderForm!provider_id].Form![id]
 
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit

Access still can't find that ...field? control?

This is making me dizzy. lol

[puppydogbuddy]

Ok, if you are referring to a nested 2nd level subform from the main form, the syntax is like this:

Me!Subform1.Form!Subform2.Form!ControlName

useing this link as a guide (for future reference):

http://www.mvps.org/access/forms/frm0031.htm

[Annalyzer]

No, there is only 1 subform (sfrmKids4ProviderForm) with a control named provider_id. This subform resides on frmProvider which has a control named id. So, if I'm understanding you correctly, the syntax should be:

stLinkCriteria = "[tblAssignment].[provider_id]=" & Me![provider_id].Form![id]

However, this syntax throws the error message: "Access can't find the field provider_id referred to in your expression."

Am I misunderstanding?

[puppydogbuddy]

Ok, you have been confusing me with different names for your subforms (see below). Also, you indicate that you want to open the subform, but in your code you are opening frmChild.


Forms………………………Record Sourrce
frmProvider…………………..tblProvider.[id]
sbfrmProvderChildren…….qryXXXX..[provider_id]
sfrmKids4ProviderForm…. qryXXXX..[provider_id]
frmChild…………………..tblchild.[id]

Is the button is to open frmChild or is it supposed to open sfrmKids4ProviderForm from the main form?

If the intent is to open the subform, and not frmchild , try the following code.


1. stDocName = "sfrmKids4ProviderForm"
2. stLinkCriteria = "[tblAssignment].[provider_id]=" & Me![id]

[Annalyzer]

You know, I think maybe that's the biggest part of my problem. I'm confusing myself with the names and data sources of all these objects. I originally threw together this database in about a week and a half to prevent losing some funding and now I'm trying to clean it up. I've renamed so many things and rewritten so much code that I can't remember what's what.

Throw into that the fact that I'm now under the gun to finish up some programming projects in another database on a LAMP system and create some budgeting spreadsheets in Excel that do everything managers need them to do without allowing them to delete any formulas or budget any expenses to grants that aren't supposed to be paid from those grants and sometimes I wonder which way is up. Half the time I can't remember if I'm supposed to be thinking in Access, MySQL, PHP, Linux, or Excel. I feel like I'm just putting band-aids on what's wrong instead of fixing things the right way.

I think it's time to go back to the ERD on this one and start from scratch. If, after that, I'm still having problems, I'll be back. I'm sorry I've wasted your time and I truly appreciate all your help and tenacity. YOU ROCK, puppydogbuddy!