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 "frmsparepartslist"
The parameter comes from a label.

Is the above query incorrect?Please 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]![InstalledQuantity],[Forms]![frmPARTS]![ID]) as the control source
The error in the textbox is #Name now..
The query is the same as before.InstalledQuantity 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])

'frmSparePartsinBase' 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

=FunctionName()

in the control source means function calling.

[HiGu]

By the way, about the query that I have saved..
What if the data in the table changes?Will the query run and update the changes?Just want to know if the query is useful even if the data in the data source changes or some additions are made?

[Mihail]

Is Ok. The query will do the job.

[HiGu]

Such a relief.Thanks a lot..

[HiGu]

The function is getting called now.However, the value is empty.It seems, the function is not able to capture the value of ID from the form.
To take the value of the label ID, I have done this

strQueryID=Me.ID.Value

[Mihail]

Assuming that:
-Your label is named ID;
-You see in your label the ID value (i.e. 758)

Try this:

strQueryID=Me.ID.Caption

[HiGu]

Not working this way too.

[Mihail]

It seems that the function is not in the form module. It is in a separate (general) module.
If so, modify, again:

strQueryID=YourFormName.ID.Caption

See what is your form name in VBA because if the real name of your form is YourFormName, in VBA become something like Form_YourFormName.

If steel not work, post the entire code for your function here.

[HiGu]

Okay.
There is one more question.If I have 3 records, the count is displayed in the row of each record on the UI.As in if the count is 'n' it is displayed 'n' times because there are 'n' records displayed and count is in the same row as the reocrd.How can I rectify this?I want only one textbox to display the count of all.

[HiGu]

hey! I just tried the code you gave..i.e.
strQueryID=frmParts.ID.Caption

the name of my form is frmParts..
but there was an error: object not found

[HiGu]

tried Form_frmParts.ID.Caption also..
didn't work

[HiGu]

Private Sub Form_Current()
    ' Display the picture for the current employee record if the image
    ' exists.  If the file name no longer exists or the file name was blank
    ' for the current employee, set the errormsg label caption to the
    ' appropriate message.
    Dim res As Boolean
    Dim fName As String
    strQueryID = Form_frmPARTS.ID.Caption
    path = CurrentProject.path
    On Error Resume Next
        errormsg.Visible = False
        If Not IsNull(Me!Picture) Then
            res = IsRelative(Me!Picture)
            fName = Me![ImagePath]
            If (res = True) Then
                fName = path & "\" & fName
            End If
 
            Me![ImageFrame].Picture = fName
            showImageFrame
            Me.PaintPalette = Me![ImageFrame].ObjectPalette
            If (Me![ImageFrame].Picture <> fName) Then
                hideImageFrame
                errormsg.Caption = "Picture not found"
                errormsg.Visible = True
            End If
        Else
            hideImageFrame
            errormsg.Caption = "Click Add/Change to add picture"
            errormsg.Visible = True
        End If
 
 

[Mihail]

My skill in SQL is almost null. But I think your SQL must be something like this:

SELECT Count(TableName.FieldName) AS CountRecords FROM TableName;

Better is that the FieldName to be the primary key. That ensure that there are not null values in this field.

Hope you understand that, in order to count all records, I use a single field in the query.
But why you not use DCount() function to perform this calculation? In Access help files this function is very well explained.

[HiGu]

Yes the column of which rows are to be counted is a primary key.If I use DCount(), won't it be repeated with the other rows?I will check DCount() soon.I checked with the function we were discussing in the previous posts.

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

In the above function, I applied a breakpoint to debug and found that strQueryID is able to take the value from the main form but ValueSpareQuery =""

[HiGu]

Hey Mihail..I used a query instead as the count is to be selected from inner join of three tables.

[Mihail]

Ufff.
ZIP your database and attache it to your next post.

[HiGu]

:P Well,Zipping the DB is going to be very difficult.Above is the query that I have used to inner join the tables. I think I am trying to get solutions for two problems at a time.

[NeoPa]

Thirty posts for one of the simplest questions we ever get on Bytes!

Let's waste no more time and effort on this. Tell me clearly what you need (which you haven't yet done over these 30 posts) and I'll write it for you.

HiGu:
I think I am trying to get solutions for two problems at a time.

Which we don't allow because it makes life so complicated that neither problem is likely to be resolved (and other reasons - but is certainly against site rules).

[HiGu]

I am facing two problems while working.
1.The funtion which is getting called now but is not taking the value it is supposed to take like this

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

In the above function, I applied a breakpoint to debug and found that strQueryID is able to take the value from the main form but ValueSpareQuery =""
I do not understand why is this happening.Both strQueryID and ValueSpareQuery are of datatype string.

2.Any hint or idea how I can have only 1 textbox to display the count in the above query?Since I other details are also to be displayed on the screen along with the count,the count is displayed multiple times in the row.

If I have not been able to explain the second problem,let's concentrate on the first one.

[Mihail]

God bless you, NeoPa !

[NeoPa]

HiGu,

We are interested in one problem only in this thread :- The problem explained in the first post.

If you have other problems, then we have a big site that can manage more threads from you. What we don't have, and don't allow, is multiple questions in the same thread. May I understand that this thread is completed now, as I don't see any further mention of the original question? Presumably you have a valid answer to that already. If so, I suggest you select an answer from all those posted, and mark it as Best Answer. After that, you take whatever questions you have left, and post them in separate threads. It may be that you only want to post one at a time, as the answer to one may make everything clearer anyway. That's your choice.

[HiGu]

Hey NeoPa and Mihail..Everything related to the 1st problem is working now is working now.Thanks a lot!
Thanks again for the patience :D

Here's the Dlookup()

=DLookUp("[CountInstalledQuantity]","qrySpareparts","[ID]=" & [txtQueryID])