469,300 Members | 2,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

passing input parameter to the subform from a form

99
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:
Expand|Select|Wrap|Line Numbers
  1. select count(tblequipmentbase.id) from 
  2. (tblequipmentbase inner join tblequipmentparts on 
  3. tblequipmentbase.id=tblequipmentparts.idconnect) inner join 
  4. tblparts on tblequipmentparts.idpart=tblparts.id
  5.  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.
Feb 15 '12 #1

✓ answered by Mihail

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

35 4558
NeoPa
32,173 Expert Mod 16PB
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.
Feb 15 '12 #2
HiGu
99
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.
Feb 16 '12 #3
HiGu
99
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.
Feb 16 '12 #4
Mihail
759 512MB
A possible approach:
You must provide the value for the public variable (see attachment).
Attached Files
File Type: zip Test.zip (21.5 KB, 136 views)
Feb 16 '12 #5
NeoPa
32,173 Expert Mod 16PB
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 :-)
Feb 16 '12 #6
HiGu
99
:( 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
Expand|Select|Wrap|Line Numbers
  1. =ValueSpareQuery()
Expand|Select|Wrap|Line Numbers
  1. Public Function ValueSpareQuery() As String
  2. ValueSpareQuery=strQueryID
  3. End Function
Now for the textbox that displays the count from the query, the Dlookup function that's not working
Expand|Select|Wrap|Line Numbers
  1. =DLookUp([qrySpareParts]![InstalledQuantity],"[tblParts.ID]="_&[Forms]![frmSparePartsinBase]![txtQueryID])
'frmSparePartsinBase' is the subform
Feb 17 '12 #7
HiGu
99
I have removed the where clause from the query so that all the records are displayed alongwith the column the records are based on.
Expand|Select|Wrap|Line Numbers
  1. select tblparts.id,count(tblequipmentbase.id) from  
  2. (tblequipmentbase inner join tblequipmentparts on  
  3. tblequipmentbase.id=tblequipmentparts.idconnect) inner join  
  4. tblparts on tblequipmentparts.idpart=tblparts.id group by tblparts.id 
  5.  
Feb 17 '12 #8
Mihail
759 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. YourPublicVariable = DCount(...) 'Or
  2. YourPublicVariable = DLookup(...)
then use that variable as I show you in order to have the value in your text box.
Feb 17 '12 #9
HiGu
99
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..
Feb 17 '12 #10
HiGu
99
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?
Feb 17 '12 #11
NeoPa
32,173 Expert Mod 16PB
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.
Feb 17 '12 #12
HiGu
99
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.
Feb 18 '12 #13
NeoPa
32,173 Expert Mod 16PB
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 ;-)
Feb 18 '12 #14
HiGu
99
:-) I thought writing
Expand|Select|Wrap|Line Numbers
  1. =FunctionName()
in the control source means function calling.
Feb 19 '12 #15
HiGu
99
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?
Feb 19 '12 #16
Mihail
759 512MB
Is Ok. The query will do the job.
Feb 19 '12 #17
HiGu
99
Such a relief.Thanks a lot..
Feb 20 '12 #18
HiGu
99
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
Expand|Select|Wrap|Line Numbers
  1. strQueryID=Me.ID.Value
Feb 20 '12 #19
Mihail
759 512MB
Assuming that:
-Your label is named ID;
-You see in your label the ID value (i.e. 758)

Try this:
Expand|Select|Wrap|Line Numbers
  1. strQueryID=Me.ID.Caption
Feb 20 '12 #20
HiGu
99
Not working this way too.
Feb 20 '12 #21
Mihail
759 512MB
It seems that the function is not in the form module. It is in a separate (general) module.
If so, modify, again:
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 20 '12 #22
HiGu
99
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.
Feb 20 '12 #23
HiGu
99
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
Feb 20 '12 #24
HiGu
99
tried Form_frmParts.ID.Caption also..
didn't work
Feb 20 '12 #25
HiGu
99
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     ' Display the picture for the current employee record if the image
  3.     ' exists.  If the file name no longer exists or the file name was blank
  4.     ' for the current employee, set the errormsg label caption to the
  5.     ' appropriate message.
  6.     Dim res As Boolean
  7.     Dim fName As String
  8.     strQueryID = Form_frmPARTS.ID.Caption
  9.     path = CurrentProject.path
  10.     On Error Resume Next
  11.         errormsg.Visible = False
  12.         If Not IsNull(Me!Picture) Then
  13.             res = IsRelative(Me!Picture)
  14.             fName = Me![ImagePath]
  15.             If (res = True) Then
  16.                 fName = path & "\" & fName
  17.             End If
  18.  
  19.             Me![ImageFrame].Picture = fName
  20.             showImageFrame
  21.             Me.PaintPalette = Me![ImageFrame].ObjectPalette
  22.             If (Me![ImageFrame].Picture <> fName) Then
  23.                 hideImageFrame
  24.                 errormsg.Caption = "Picture not found"
  25.                 errormsg.Visible = True
  26.             End If
  27.         Else
  28.             hideImageFrame
  29.             errormsg.Caption = "Click Add/Change to add picture"
  30.             errormsg.Visible = True
  31.         End If
  32.  
  33.  
Feb 20 '12 #26
Mihail
759 512MB
My skill in SQL is almost null. But I think your SQL must be something like this:
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 20 '12 #27
HiGu
99
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function ValueSpareQuery() As String 
  2. ValueSpareQuery=strQueryID 
  3. End Function 
  4.  
  5.  
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 =""
Feb 20 '12 #28
HiGu
99
Hey Mihail..I used a query instead as the count is to be selected from inner join of three tables.
Feb 20 '12 #29
Mihail
759 512MB
Ufff.
ZIP your database and attache it to your next post.
Feb 20 '12 #30
HiGu
99
: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.
Feb 20 '12 #31
NeoPa
32,173 Expert Mod 16PB
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).
Feb 20 '12 #32
HiGu
99
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
Expand|Select|Wrap|Line Numbers
  1. Public Function ValueSpareQuery() As String  
  2. ValueSpareQuery=strQueryID  
  3. 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.
Feb 20 '12 #33
Mihail
759 512MB
God bless you, NeoPa !
Feb 20 '12 #34
NeoPa
32,173 Expert Mod 16PB
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.
Feb 20 '12 #35
HiGu
99
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()
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[CountInstalledQuantity]","qrySpareparts","[ID]=" & [txtQueryID])
Feb 21 '12 #36

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Thomas Philips | last post: by
1 post views Thread by josephpage | last post: by
2 posts views Thread by David W. Fenton | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.