Open Subform with Matching Data from Separate Form

[abbyanu]

I'm trying to pass data from two combo boxes in a form to a subform in a second form , but I'm getting this error: Enter Parameter Value. I've tried all possible ways, including using a textbox instead of a combo box, but I'm getting the same error. When I enter input values into the "Enter Paremeter Value" box I get the correct results.

Below is my code:

[tt]
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Entity"

stLinkCriteria = "[forms]![Entity]![Entity Evaluation subform]!Form![location_id]= " & "'" & Me![location_id] & "' AND [period_id]= '" & Me![period_id] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

[/tt]
Any idea how I can resolve this problem? I'm getting the same error even when I try to pass only one parameter instead of two.

Many thanks.

Sincerely,
Abbyanu.

 

[M8KWR]

when is this code running, and from what form to which other form with the subform.

If you could let me know the names of the each form, inc subform, and the name of the combo boxes.

 

[abbyanu]

This code is running from a command button in a form called "Locations Internal Controls". This form has two combo boxes - location_id and period_id.

The second form is called "Entity" and it has a subform called "Entity Evaluation subform". The "Entity Evaluation subform" also has two combo boxes - location_id and period_id.

When a user clicks on the command button in the "Locations Internal Controls" form, I would like it to pass the values in the location_id and period_id combo boxes and match it with the location_id and period_id in the "Entity Evaluation subform".

In the event that there are no matching values... it should return the "Entity Evaluation Subform" blank so that the default values for data entry would be the location_id and period_id from the "Locations Internal Controls" form.

The main code that I have thus far for linking the forms is as above, and i'm prompted to manually enter the values into the "E - which works fine. I would like values to be automatically passed into the oter

 

[abbyanu]

This code is running from a command button in a form called "Locations Internal Controls". This form has two combo boxes - location_id and period_id.

The second form is called "Entity" and it has a subform called "Entity Evaluation subform". The "Entity Evaluation subform" also has two combo boxes - location_id and period_id.

When a user clicks on the command button in the "Locations Internal Controls" form, I would like it to pass the values in the location_id and period_id combo boxes and match it with the location_id and period_id in the "Entity Evaluation subform".

In the event that there are no matching values... it should return the "Entity Evaluation Subform" blank so that the default values for data entry would be the location_id and period_id from the "Locations Internal Controls" form.

The main code that I have thus far for linking the forms is as above, and i'm prompted to manually "Enter the parameter values" - which works fine when I put the values. But I would like values to be automatically passed.

I was getting the same "Enter Parameter Value" nag even I tried passing only the location_id. I would appreciate any help.

 

[abbyanu]

Oops! Please ignore the second-to-last post.... I accidentally submitted.

 

[TheAceMan1]

How are ya abbyanu . . . . .

[blue]I'm trying to [purple]pass data[/purple] from two combo boxes in a form to a subform in a second form[/blue]

You say you want to [blue]pass two combobox values[/blue], then [blue]you show there used as criteria![/blue]

[purple]Be more specific about passing the values and there intended use![/purple]

And please answer [blue]M8KWR's[/blue] question.

See Ya! . . . . . .

 

[abbyanu]

Thanks TheAceMan1,

I have two forms: "Locations Internal Controls" and "Entity." The "Entity" form has a subform called "Entity Evaluation Subform" and is linked to it via entity_id.

The "Locations Internal Controls" form is linked to the "Entity Evaluation Subform" via location_id and period_id.

The names of the combo boxes in the originating form ("Locations Internal Controls") are location_id and period_id. When a user selects values in both of these combo boxes and hits a command button, I want the selected values in location_id and period_id to be used to match the location_id and period_id in the "Entity Evaluation Subform" subform, which is linked to the "Entity" form.

Hence the above code... which is in the command button on the "Lcations Internal Controls" form.

So basically I wish to use the location_id and period_id values from the "Locations Internal Controls" form to match the location_id and period_id values in the "Entity Evaluation Subform".

In the event that there are no matching values.... then I wish to have the period_id and location_id from the "Locations Internal Controls" form as the default values in the "Entity Evaluation Subform".

My main problem is that "Enter Parameter Value" nag.

Hope this helps... and thanks for the help.

 

[TheAceMan1]

abbyanu . . . . .

And [blue]stLinkCriteria[/blue] is criteria for Query/SQL of the subforms [blue]RecordSource[/blue], or used in the [blue]Filter Property[/blue]? . . . . I'm guessing the filter property . . . .

See Ya! . . . . . .

 

[TheAceMan1]

abbyanu . . . . .

Scratch my last post. Answer would have bit me if it was a snake . . . .

See Ya! . . . . . .

 

[abbyanu]

Indeed... stLinkCriteria is used in the subform's Filter Property.

 

[TheAceMan1]

abbyanu . . . .

Here's the [blue]corrected criteria[/blue]:

[blue]stLinkCriteria = "'" & Forms!Entity![Entity Evaluation subform].Form!location_id & "' = '" & Forms![Locations Internal Controls]!location_id & "' AND '" & Forms!Entity![Entity Evaluation subform].Form!period_id & "' = '" & Forms![Locations Internal Controls]!period_id & "'"[/blue]

This should take care of the nagging [blue]Parameter Windows[/blue].

To take care of default values if comparison fails, add the following to the subforms [blue]Load Event[/blue]:

[blue]   Dim frm As Form
   
   If Not Me.RecordsetClone.RecordCount Then
      Set frm = Forms![Locations Internal Controls]
      
      Me!location_id.DefaultValue = frm!location_id
      Me!period_id.DefaultValue = frm!period_id
      
      Set frm = Nothing
   End If
[/blue]

Give it a whirl and let me know . . . . .

See Ya! . . . . . .

 

[abbyanu]

Hi TheAceMan1

Many thanks for your assistance... the code works fine, but now this has introduced a new problem...an error message [tt]Run-time error '2450'[/tt] that the database [tt]'can't find the form "Entity" referred to in macro expression or Visual Basic code[/tt]. I believe the code above assumes that the form "Entity" is already opened.

When I open the form "Entity" first and then click on the command button on the Locations form to execute the stLinkCriteria it works fine. I don't want this to be the case, because I wish to set the default values in the "Entity Evaluation" subform to the selected values in the "Locations Internal Controls" subform.

Any work-arounds? Please advise.

 

[TheAceMan1]

abbyanu . . . . .

I had my next post prepared but left it home, and its tuogh posting at work . . . .

Anyway there's an operational problem here. [blue]stLinkCriteria[/blue] in the [blue]DoCmd[/blue] is sent to the Entity [purple]Main Form[/purple] not [purple] the subForm[/purple]. This is whats causing the error.

I'll get back to ya ASAP on this . . . .

See Ya! . . . . . .

 

[abbyanu]

Hi

I still can't get it... 'cuz I wish to link to the subform in the Main form. So I'm assuming that we'll link the main form and do the filter on the subform.

Am I missing something? Please advise.

 

[TheAceMan1]

abbyanu . . . . .

Sorry to get back so late.

First, [purple]you can't open a subForm embedded on a MainForm.[/purple] You have to open the MainForm to gain access. This is why [blue]DoCmd.OpenForm stDocName, , , stLinkCriteria[/blue] in not working properly. The [blue]criteria arguement[/blue] filters the form your opening! [purple]So were filtering the MainForm with criteria meant for the subForm!.[/purple]

Second, when you open a form with subform/s, [blue]the subform/s open first[/blue], then the mainform. This is the reason for the [blue]Run-time error '2450'[/blue]. The second block of code I gave you is in the Load Event of the subform and needs to reference the mainform [blue]Entity[/blue] which is not open yet!

If you've followed me so far, since we can't directly open the subForm, [blue]we need to come up with another method to get the subform filtered[/blue], as I've shown [purple]we can't use DoCmd.OpenForm[/purple].

Before I continue, I want to clarify your objective:

[blue]You want to filter the subForm via location_id & period_id of the form Locations Internal Controls. If no records are returned, you want to set the defaults.[/blue]

Do I have this correct?

See Ya! . . . . . .

 

[abbyanu]

TheAceMan1, I'm reading you.... could and clear.

Please go on and share your wisdom

 

[TheAceMan1]

abbyanu . . . .

In my quote . . . do I have your objective correct?

This is your most important answer! . . . .

See Ya! . . . . . .

 

[abbyanu]

Yes, sir!

That's precisely what I wish to accomplish.

 

[TheAceMan1]

abbyanu . . . . .

I should've asked in my last post again for clarity).

What are the Data Types of the comboboxes in the form [blue]Locations Internal Controls[/blue]? (I believe their strings).

Are [blue]location_id[/blue] & [blue]period_id[/blue] the [blue]bound columns[/blue] of the comboboxes?

Is the [blue]RecordSource[/blue] for the subform Table/Query/SQL?

See Ya! . . . . . .

 

[abbyanu]

The data datatypes combo boxes in the form Locations Internal Controls are Strings.

Indeed... location_id and period_id are the bound columns.

The RecordSource is a Table.

Hope this helps.

Abbyanu.