passing input parameter to the subform from a form

[HiGu]

I need to select count of a column and display it on a subform.To count the column, I need to pass a parameter from a form. How can I capture the parameter form and display it on the subform?All this is in MS ACCESS 2007.

the query is:

select count(tblequipmentbase.id) from 
(tblequipmentbase inner join tblequipmentparts on 
tblequipmentbase.id=tblequipmentparts.idconnect) inner join 
tblparts on tblequipmentparts.idpart=tblparts.id
 where tblparts.id=Forms!frmparts!ID

"frmparts" is the form
the query is written for a textbox in the subform "frmsparepartsl ist"
The parameter comes from a label.

Is the above query incorrect?Pleas e suggest some idea anyone..
This query runs fine in the sql editor when i give direct value of the parameter.

[NeoPa]

Please read [CODE] Tags Must be Used.

The query seems fine, but where are you using it? You can't use it as a Control Source.

[HiGu]

I am using it in the control source this way control source: =(query)I can't put it in the record source as the subform already has a record source editing which leads to problems as the it does not have reference to all the tables used in the query for the text box.

[HiGu]

Now I have created a query using the query wizard and I have used
=DLookUp([qrySpareparts]![InstalledQuanti ty],[Forms]![frmPARTS]![ID]) as the control source
The error in the textbox is #Name now..
The query is the same as before.Installe dQuantity is the result of the query.

[Mihail]

A possible approach:
You must provide the value for the public variable (see attachment).

[NeoPa]

I suspect you're on the right lines with DLookup(), but I suggest you look up how to use it properly in Context-Sensitive Help. There is little point to me simply copy/pasting what is already there, but if you struggle then let us know where and we'll do what we can to help :-)

[HiGu]

:( trying options after options.I created a public variable 'strQueryID' which is assigned the value of the 'ID' of the main form in Current() function just like Mihail suggested.Then I have an invisible textbox 'txtQueryID' on the subform whose control source is

=ValueSpareQuery()

Public Function ValueSpareQuery() As String
ValueSpareQuery=strQueryID
End Function

Now for the textbox that displays the count from the query, the Dlookup function that's not working

=DLookUp([qrySpareParts]![InstalledQuantity],"[tblParts.ID]="_&[Forms]![frmSparePartsinBase]![txtQueryID])

'frmSparePartsi nBase' is the subform

[HiGu]

I have removed the where clause from the query so that all the records are displayed alongwith the column the records are based on.

select tblparts.id,count(tblequipmentbase.id) from  
(tblequipmentbase inner join tblequipmentparts on  
tblequipmentbase.id=tblequipmentparts.idconnect) inner join  
tblparts on tblequipmentparts.idpart=tblparts.id group by tblparts.id

[Mihail]

Of course that DCount (or DLookup) do not work as you try.
Store the result of Dcount() (or DLookup()) into a public variable, like this:

YourPublicVariable = DCount(...) 'Or
YourPublicVariable = DLookup(...)

then use that variable as I show you in order to have the value in your text box.

[HiGu]

Isn't there a simpler, easier way of capturing the value of a label from the form and passing it to the control source of a textbox on the subform?Things are becoming complicated for me..

[HiGu]

The function ValueSpareQuery () is not getting called. And so the txtbox txtQueryID displays #Name?
I put that function in the 'General' part of the module.
:(
What could be the reason?

[NeoPa]

What's going on here. The DLookup() reference should be fine on its own.

@HiGu.
Did you look in help for how to call the function? I'm happy to help if you still struggle when you've done that, but it seems clear that you haven't even done that yet. Let us know.

@Mihail.
Using a variable should not prove necessary if things are managed correctly.

[HiGu]

Well yes..I did not check help.I assumed that when I run the module it should get called.I will try that and hopefully I get it.Otherwise I am back here.

[NeoPa]

That sounds sensible. Normally though, if I (or anyone else) point you somewhere for an answer, you can assume it would be a good idea to try it first before coming back for more help ;-)

[HiGu]

:-) I thought writing

=[I]FunctionName()[/I]

in the control source means function calling.