Enter Parameter Value ERROR 1

[kermitforney]

Getting a dreaded Enter "Parameter Value" message. I have a fomr setup that runs off of a query. I have a subform that also runs from a query, although it is a different query and not based on the main form's query. So when I test it using Form View I get the error message.

Here is the query:
SELECT DISTINCTROW tblcpPlans.*
FROM tblcpPlans
WHERE (((tblcpPlans.cpCommID)=Forms!frmCommProfile!txtCommunityID))
ORDER BY tblcpPlans.cpNumber;

Just a simple query based on the value that is present in a text box on the main form. The only reason I could think of is that the subform query is running before the main form. Therefore there is an empty text box and that is why the query is asking for a value.

Thanks in advance for any and all help guys, it is very much appreciated. )

 

[TheAceMan1]

How are ya kermitforney . . .

Note: [blue]A form with subform opens from the innermost subform[/blue] (subforms can be 3 levels deep) [blue]out to the mainform[/blue]. The point being subforms open 1st! So when the subform opens, [purple]the mainform is not open yet and there's no data available for your use[/purple] . . . hence the error!

To correct this try the following:
[ol][li]In [blue]design view[/blue] of the subform remove anything in the [blue]RecordSource[/blue] property so its blank. [blue]Save & close[/blue] the subform.[/li]
[li]In the [blue]Load[/blue] event of the MainForm, copy/paste the following:

[blue]   Dim SQL As String
   
   SQL = "SELECT DISTINCTROW * " & _
         "FROM tblcpPlans " & _
         "WHERE ([cpCommID]=Forms!frmCommProfile!txtCommunityID) " & _
         "ORDER BY cpNumber;"
   [subFormName].Form.RecordSource = SQL[/blue]

[/li][/ol]
All your doing is [blue]insuring the MainForm opens 1st[/blue] so that value is there for the subForm.

[blue]Thats it! . . . check it out . . .[/blue]

See Ya! . . . . . .

 

[kermitforney]

For some reason I am still getting the same error.
This time when I click Cancel, a Runtime Error is displayed.

Run-time error '2001'

You canceled the previous operation.

 

[kermitforney]

Let me clarify the above comment.

When I run the program I get a 'Parameter Value' error dialog box.
If I click cancel I get the expected runtime error.

 

[TheAceMan1]

kermitforney . . .

Post the actual Parameter Message Error.

See Ya! . . . . . .

 

[kermitforney]

Same as before, "Forms!frmCommProfile!txtCommunityID". (

 

[kermitforney]

Basically asking me for the value.

 

[TheAceMan1]

kermitforney . . .

I couldn't believe so I ran a simulation. Received the Parameter InputBox as expected. Incorperated the secnario I gave you and voila . . . No Problemo!

The only other thing I can think of is to check the [blue]Link Master/Child[/blue] properties of the subform. They should be blank . . .

See Ya! . . . . . .

 

[TheAceMan1]

kermitforney . . .

Did you arrive at solution?

See Ya! . . . . . .

 

[kermitforney]

I am going to re-create the subform, but I think your right. The Sub may still be linked to the Master. I will post with my findings.

 

[kermitforney]

OHHH GOD!!!!!!!!!!!
Finally got it to work. It is always the damn file names with me.


I should have and uaually do, but this time I didn't put the 'frm' prefix vefore my form. So it was actually looking for 'frmCommProfile', and it was actually CommProfile. s

But I have one more quick noob question for you Ace. )

I have Primary Keys in my tables and of course foreign keys of those primary's in joined tables. How do I get an Autonumber that is created in my main table to place the given value in the foereign key's of my joined (child) tables.

 

[kermitforney]

Ok, different deal now.
I need the exact same type of query ran like I did before, I am actually reusing the code but filling in different form/query info. I just dont know where to place it.

Basically, it is dependant upon the sub form in the prior snippet of code u gave me. I tried to place it in the main form (load sequence)below the first snippet, but it gives me the same parameter value that the first snippet gave me. So I am assuming that the first VB code is read and then the second snippet runs so quickly the query hasn't placed the info in the 1st sub form yet.

Well let me know if you can understand all of that. )
It is 5:30 and I am leaving for the night. I will check Tek-tips l8r on tonight.

 

[TheAceMan1]

kermitforney . . .

Not quite sure where you've gone with this but to make things easier:
[ol][li]Design an actual query for the one you've stated above (if not already).[/li]
[li]Incorporate the secenario I gave you (2nd post from the top) and instead of:

[blue]   Dim SQL As String
   
   SQL = "SELECT DISTINCTROW * " & _
         "FROM tblcpPlans " & _
         "WHERE ([cpCommID]=Forms!frmCommProfile!txtCommunityID) " & _
         "ORDER BY cpNumber;"
   [subFormName].Form.RecordSource = SQL[/blue]

. . . use:

[blue]   [subFormName].Form.RecordSource = "[purple][b]QueryName[/b][/purple]"[/blue]

[/li][/ol]

Hope I'm on the right track . . .

See Ya! . . . . . .

 

[kermitforney]

Ok, here is a broken down version of the rushed explanation I had given you earlier.

The first solution you provided me with works fine now.

So what I have now is a main form and a subform that pulls info based on a value in a textbox of the main form.

I now need to create a second sub form (on the same main form), but this time the query will be dependant upon a field in the first subform.

How or where do I place the VB code in the application for it to load the records from the second subform correctly?

 

[kermitforney]

ttt

 

[TheAceMan1]

kermitforney . . .

Sorry to get back so late . . .
[ol][li]In the criteria for the field of interest in the 2nd query:

[blue]=Forms!MainFormName!1stSubFormName.Form!FieldName[/blue]

[/li]
[li]In the 2nd subform make sure the [blue]recordsource[/blue] and the [blue]Link Master/Child[/blue] properties are blank.[/li]
[li]In the Load event of the mainform:

[blue]   [[purple][b]subFormName1[/b][/purple]].Form.RecordSource = "[purple][b]QueryName1[/b][/purple]"
   DoEvents
   [[purple][b]subFormName2[/b][/purple]].Form.RecordSource = "[purple][b]QueryName2[/b][/purple]"[/blue]

[/li][/ol]

See Ya! . . . . . .