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.

 

[TheAceMan1]

abbyanu . . . .

Great . . . just what I need. I'll work it up and see ya some time in the mourning! Its 12:15 AM EST.

[blue]You have a goodnight . . . Ya Hear![/blue] ;-)

See Ya! . . . . . .

 

[abbyanu]

Thanks, TheAceMan1... I gather you must be in the East Coast

 

[TheAceMan1]

Good Mourning abbyanu . . . . .

Hate to stretch this out, but [blue]another issue has to be considered[/blue] (no way around it) . . .

Your [blue]controlling the subForm fom at least two sources[/blue] (possibly three, depending on the function of the comboxes in the subForm), the [blue]MainForm[/blue] (normal navigation when changing record) and form [blue][Locations Internal Controls][/blue].

[blue]In controlling the subform with [Locations Internal Controls], we dont want to interfere with normal navigation via the MainForm, or the functionality of the comboboxes.[/blue]

So a few more questions are in order:

[blue]The "Entity" form has a subform called "Entity Evaluation Subform" and is [purple]linked to it via entity_id[/purple].[/blue]

Is the link via [purple]Link Master/Child Properties of the subform (I'm sure it is, but just for clarity)?[/purple]

Give a brief description of the functionality of the comboxes on the subform!

Since it appears [purple]Query/SQL[/purple] will be at the heart of the modification, I need a complete listing of the table the subForms [blue]RecordSource[/blue] is based on.

Were almost there . . . so hang tight!

See Ya! . . . . . .

 

[abbyanu]

Hi TheAceMan1

Here's the complete scenario... sorry for the repetition... I want you to have as much info as possible 'cuz I need help

Here are the table layouts: * is primary key:
[tt]
Locations:
location_ID*, location_name

Locations_Internal_Control:
location_id*, period_id*

Entity_Evaluation:
location_id*, period_id*, entity_id*

Entity:
entity_id*, entity_name
[/tt]

The relationships are as follows:
[tt]
Locations --< Location_Internal_Control (via location_id)

Locations_Internal_Control --< Entity_evaluation (via location_id, period_id)

Entity --< Entity_Evaluation (via entity_id)
[/tt]

There are two master forms - each of the form/subform's RecordSource is the table with the same name as the form/subform name.

1. Locations is a master form that has Locations Internal Controls as a subform. Linked via Master/Child Properties. I've named the combo boxes and text boxes with the associated field names (not a good programming concept, i know )

2. Entity is a master form that has Entity Evaluation subform as a subform. Linked via Master/Child Properties. Again textboxes and combo boxes names are similar to the associated field names (i.e. in the Entity Evaluation subform there are three combo boxes - location_id, period_id, and entity_id).

As stated earlier, to connect the two master forms, there's a command button in the Locations Internal Controls subform... it simply uses the values in this subform's location_id and period_id combo boxes to filter the location_id and period_id in the Entity Evaluation subform.

And in the event that there's no match, I wish to use the location_id and period_id values in the Locations Internal Controls subform as the default values in the Entity Evaluation subform.

So basically I'm using one subform's values to filter another subform's values.

Thanks for your patience.... and help.

 

[TheAceMan1]

abbyanu . . . . .

Forgive me! . . . . . but your last post did not answer any of my questions. It simply reiterated what I already know.

If for some reason anything of what I ask is [blue]proprietary[/blue], just say so . . . . I can work around it. Not only did I explain why, but I'm sure you understood it.

These are things I need to know if I'm gonna give ya error free code. So please answer the questions, [purple]these answers are vital![/purple]

See Ya! . . . . . .

 

[abbyanu]

Oops! Lemme keep it short and to the point

1. The link is via Link Master/Child Properties of the subform.

2. Functionality of the comboboxes in the subform... to describe the location (location_id) and the period (period_id) that the entity is being evaluated (entity_id).

Indeed, some of the data is proprietary... but the subform's RecordSource has the structure as described in the previous post.

Have now I answered your questions? You seem to have a pretty good picture of what I wish to accomplish. Many thanks for your patience.

 

[TheAceMan1]

OK abbyanu . . . .

[blue]Now were playing with power![/blue]

Just one more question and I can get busy!

Do [blue]location_id[/blue] & [blue]period_id[/blue] prescribe a [blue]unique single record[/blue] or possibly a [blue]group of records?[/blue]

See Ya! . . . . . .

 

[abbyanu]

Hi TheAceMan1

I'm not sure that I understand your question.

In the Locations Internal Control subform, location_id & period_id constitute a primary key - thus a unique single record.

In the Entity Evaluation subform, location_id & period_id, along with entity_id constitute a unique single record. Thus in this subform both location_id & period_id would be common to a group of records.

Regards,
Abbyanu.

 

[TheAceMan1]

abbyanu . . . . .

Good enough . . . . I'll get back with the code. I'll try to post by this afternoon.

See Ya! . . . . . .

 

[TheAceMan1]

abbyanu . . . . .

Not so good news. Got halfway thru the code when the code revealed to me an [purple]error in relationships[/purple]. Using the data you provided I worked up a simulation with relationships provided, and as I suspected, there's an [purple]indeterminate relationship[/purple] between [blue]Locations_Internal_Control[/blue] and [blue]Entity_evaluation[/blue]. Although there's one other relationship I question, this is the one that stops me. [blue]I can't give ya code on an[/blue] [purple]indeterminate relationship[/purple]. I wouldn't know what to expect. It also shows a lack of understand relationships (not trying to sound demeaning here).

Since I do not have full knowledge of your table layout and what your trying to accomplish, its hard to advise you. Thes best I can do at this point is to have you look at the following links:

[blue]Normalizing Tables[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534&Product=acc2000

[blue]Table Relationships[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467&Product=acc2000

Let me know what ya think . . . .

See Ya! . . . . . .

 

[abbyanu]

Dear TheAceMan1

I very much appreciate your effort and time. Actually I think I do have the tables normalized, and the Locations_Internal_Control and Entity_Evaluation have a 1:many relationship.

But I'm gonna revisit the design and get back to you.

Again, many thanks for your assistance. I truly appreciate your kindheartedness.

Sincerely,
Abbyanu.

 

[TheAceMan1]

abbyanu . . . . .

I'm not not trying to say you don't know what your doing. But as the expert here, I am saying something is wrong. It may appear to work to you, but something is wrong. For instance: I can't believe that an Indeterminate Relationship appeals to you in any way! You may think you have a one to many relationship where in actuality it is not. Then again you may surprise me! So to prove this out, lets just check it!

Rght-Click the [purple]connecting relationship line[/purple] between [blue]Locations_Internal_Control[/blue] and [blue]Entity_Evaluation[/blue].

Select [blue]Edit Relationship...[/blue]

In the [blue]Edit Relationships[/blue] window that pops-up, at the very bottom, post back the [blue]Relationship Type:[/blue]. You should see what I mean here!

Understand . . . . I could give you the code and it would probably work, but I'm not gonna help you travel down the wrong path, when I know something is wrong!

See Ya! . . . . . .

 

[abbyanu]

Hi TheAceMan1

Yip... 1:many, it says - Locations_Internal_Control (location_id, period_id) --< Entity_Evaluation (location_id, period_id, entity_id)

I'm using location_id and period_id as composite primary key in the Locations_Internal_Control table.

Entity_Evaluation has location_id, period_id, and entity_id as composite primary key.

There are no errors or Indeterminate relationships.

Again, many thanks.

Sincerely,
AbbyAnu.

 

[TheAceMan1]

OK abbyanu . . . . . (Got It!)

I [blue]forgot to set indexing[/blue] when I laid it out!

Working on completing the code. While I do that, earlier you said:

[blue]1. Locations is a master form that has Locations Internal Controls as a subform. Linked via Master/Child Properties. [purple]I've named the combo boxes and text boxes with the associated field names[/purple] (not a good programming concept, i know[/blue]

Yes this is very bad! example:

[blue]Me!location_id

Forms!Locations![Locations Internal Controls].Form!location_id[/blue]

[purple]There's no way to tell if were referencing the TextBox or ComboBox![/purple], or perhaps you know of a way to tell. I'm surprised you didn't get an error message about duplicate names! In any case, this has to be fixed. You could just prefix [blue]cb[/blue]. As an example:

[blue]cb_location_id
cb_period_id[/blue]

I'll get back to ya by this afternoon . . .

See Ya! . . . . . .

 

[TheAceMan1]

OK abbyanu . . . . here we go! . . . .

Be aware since [blue]your combobox names are duplicates[/blue] of the textboxes, the wrong values may be returned for use. [purple]Be sure to backup the database so you can come back to square one if you have to.[/purple]

[blue]Special Note: Check spelling and/or substitute names in [purple]purple[/purple] throughout the code. If something doesn't work this is the first thing to check![/blue]

The code requires detection of wether or not forms are open. So in a New Module in the module window, copy/paste the following funtion:

[blue]Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function[/blue]

Next, we take care of the command button. The code allows you to open the form [blue]Entity[/blue] if its not already, or [blue]requery the subform[/blue] (criteria is the main control) if it is. In this way, when both forms (Location & Entity) are open, you can continually make selections from the comboboxes, hit the button and see the changes. So, in the event where you have your button code (I believe its the [blue]Click Event[/blue]), substitute the following code:

[blue]   Dim frm As Form, sfrm As Form
   Dim frmName As String, sfrmame As String
   
   frmName = "[purple][b]Entity[/b][/purple]"
   sfrmName = "[purple][b]Entity_Evaluation[/b][/purple]"
   
   [green]'Detect if form Entity is open. If it is then we can
   'simply Requery the subform since control is via criteria.
   'If its not, Open the form.[/green]
   If isopenfrm(frmName) Then
      Set frm = Forms(frmName)
      Set sfrm = frm(sfrmName).Form
      
      sfrm.Requery
      
      Set sfrm = Nothing
      Set frm = Nothing
   Else
      DoCmd.OpenForm frmName
   End If[/blue]

Now . . . the heart of the secnario. A query is used for the [blue]recordSource[/blue] of subform [blue]Entity_Evaluation[/blue] which uses user defined function calls as criteria. In [blue]Query Design View[/blue], make a query that includes all the fields in the subform [blue]Entity_Evaluation[/blue].

Then [blue]on the criteria line[/blue] for [blue]location_id[/blue] copy'paste the following (location_id is the name of the combobox on subform [blue]Location_Internal_Control[/blue] . . . double check the spelling):

[blue]PingID("[purple][b]location_id[/b][/purple]")[/blue]

Do the same for [blue]period_id[/blue]:

[blue]PingID("[purple][b]period_id[/b][/purple]")[/blue]

Save, name, and close the query.

Open the subform [blue]Entity_Evaluation[/blue] in [blue]Design View[/blue] and change the R[blue]ecordSource[/blue] to the name of the [blue]query you just made[/blue]. It should now be in the [blue]dropdown list[/blue] for the RecordSource.

While in design view, add the following code to the [blue]On Current Event[/blue] of the form. This code takes care of setting the defaults if no records are returned via the values of [blue]location_id & period_id[/blue] of subform [blue]Location_Internal_Control[/blue]:

[blue]   Dim frm As Form, sfrm As Form, RecCnt As Long
   Dim frmName As String, sfrmName As String
   Dim cbxLocName As String, cbxPrdName As String
   
   frmName = "[purple][b]Location[/b][/purple]"
   sfrmName = "[purple][b]Location_Internal_Control[/b][/purple]"
   cbxLocName = "[purple][b]location_id[/b][/purple]"
   cbxPrdName = "[purple][b]period_id[/b][/purple]"
   RecCnt = Me.RecordsetClone.RecordCount
   
   [green]'Qualify if Location_Internal_Control is in control and
   'no records were returned. If not set defaults[/green]
   If isopenfrm(frmName) And RecCnt = 0 Then
      Set frm = Forms(frmName)
      Set sfrm = frm(sfrmName).Form
      
      Me!location_id.DefaultValue = sfrm(cbxLocName)
      Me!period.DefaultValue = sfrm(cbxPrdName)
      
      Set sfrm = Nothing
      Set frm = Nothing
   End If[/blue]

Finally the [blue]User Defined Function[/blue] used to set criteria in the query. Add it to the same module in the modules window:

[blue]Public Function PingID(cbxName As String)
   [green]'Note: cbxName is the combobox name passed from the query.
   'Its used to return the proper value form the associated
   'combobox in subform Location_Internal_Control for criteria.
   'As such, this routine runs twice. Once for location_id then
   'again for period_id.[/green]
   
   Dim frm As Form, sfrm As Form
   Dim frmName As String, sfrmName As String
   Dim cbxLocName As String, cbxPrdName As String
   
   frmName = "[purple][b]Location[/b][/purple]"
   sfrmName = "[purple][b]Location_Internal_Control[/b][/purple]"
   
   [green]'Combobox names from form Location_Internal_Control[/green]
   cbxLocName = "[purple][b]location_id[/b][/purple]"
   cbxPrdName = "[purple][b]period_id[/b][/purple]"
   
   If isopenfrm(frmName) Then
      Set frm = Forms(frmName)
      Set sfrm = frm(sfrmName).Form
      
      [green]'Verify if combobox data is available. If data available,
      'return appropriate combobox value, otherwise nothing.[/green]
      If Len(sfrm(cbxLocName) & "") > 0 And _
         Len(sfrm(cbxPrdName) & "") > 0 Then
         PingID = frm(cbxName) [green]'Rerurned Criteria Value[/green]
      End If
      
      Set sfrm = Nothing
      Set frm = Nothing
   End If[/blue]

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

Note: There's alot I could say about the relationships graphic you sent, but as long as its working I'll leave it alone. On question though . . .

Are you finding your having problems with data entry?

See Ya! . . . . . .