By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

query an unbound field in form.. Possible?

100+
P: 164
I dont know if this is possible.. I have a form frmCustomers with a subform sfrmCustomers

and in the subform I have a unbound text field in the footer txtCOUNT with the control source of
=Sum(IIf([Action]="Received From",1,0))


and in the main form I have another unbound txt field txtReceivedtimes

It is just for a quick reference as I am combing through customers to see how many returns I have for them.


I was wondering if it were possible to get the txtReceivedtimes into a query. My query is pulling from the table of the main form and what I have so far looks like this.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Register Form].Status, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER]![Receivedtimes] AS Received
  2. FROM [Register Form]
  3. WHERE ((([Register Form].Status)="IN"))
  4. ORDER BY [Register Form].Model;
  5.  

I know its incorrect when I try and run the query it asks me to put in parameter value. and its just not working. I didnt know if there was a simple way to make this work or not???


Thanks a bunch!
Feb 6 '08 #1
Share this Question
Share on Google+
10 Replies


Jim Doherty
Expert 100+
P: 897
From where does [Forms]![TRACKING REGISTER]![Receivedtimes] derive its value? you say its unbound so is it grabbing a value presumably from somewhere? if it is a calculated control what is the controlsource of the ReceivedTimes control? Is it a DAggregate function or something?

Jim :)
Feb 7 '08 #2

100+
P: 164
From where does [Forms]![TRACKING REGISTER]![Receivedtimes] derive its value? you say its unbound so is it grabbing a value presumably from somewhere? if it is a calculated control what is the controlsource of the ReceivedTimes control? Is it a DAggregate function or something?

Jim :)

Sorry I guess I left that out on accident. It is getting its value from txtCount in the footer of the subform. All the footer txt field is doing is counting the records with "received" in the Action field. And I am just trying to get that field to go in the query.
Feb 7 '08 #3

Minion
Expert 100+
P: 108
If the value is being pulled from your sub form why not just reference the value directl in the sql. It should look something like:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Register Form].Status, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER].[Form]![<<Sub Form Name Here>>].[Receivedtimes] AS Received
  2. FROM [Register Form]
  3. WHERE ((([Register Form].Status)="IN"))
  4. ORDER BY [Register Form].Model;
  5.  
Hope this helps.

- Minion -

Sorry I guess I left that out on accident. It is getting its value from txtCount in the footer of the subform. All the footer txt field is doing is counting the records with "received" in the Action field. And I am just trying to get that field to go in the query.
Feb 7 '08 #4

100+
P: 164
If the value is being pulled from your sub form why not just reference the value directl in the sql. It should look something like:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Register Form].Status, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER].[Form]![<<Sub Form Name Here>>].[Receivedtimes] AS Received
  2. FROM [Register Form]
  3. WHERE ((([Register Form].Status)="IN"))
  4. ORDER BY [Register Form].Model;
  5.  
Hope this helps.

- Minion -

That was basically what I had for the query already. When I try and run it I get the enter parameters prompt. If I enter anything in it like "1" it will fill it all the fields with "1" if I dont put anything the rest of the query will run fine but that field will stay empty... *lost*
Feb 7 '08 #5

Jim Doherty
Expert 100+
P: 897
That was basically what I had for the query already. When I try and run it I get the enter parameters prompt. If I enter anything in it like "1" it will fill it all the fields with "1" if I dont put anything the rest of the query will run fine but that field will stay empty... *lost*

If you have a function of some sort in the subforms unbound field calculating something then whatever that function is.... whether it be DCount or DSum or something else it generally .... will have a reference part of its where clause to target what it is you are counting or summing presumably relating to an id of some kind??.

Your logical action would be to replicate the principle of that count but in the manner as described by Minion in that you need to really place that in the SQL for the form. Without seeing what you have there its difficult to articulate an answer further because the potential syntax might be skewed through not knowing enough of what you have

Jim
Feb 7 '08 #6

100+
P: 164
If you have a function of some sort in the subforms unbound field calculating something then whatever that function is.... whether it be DCount or DSum or something else it generally .... will have a reference part of its where clause to target what it is you are counting or summing presumably relating to an id of some kind??.

Your logical action would be to replicate the principle of that count but in the manner as described by Minion in that you need to really place that in the SQL for the form. Without seeing what you have there its difficult to articulate an answer further because the potential syntax might be skewed through not knowing enough of what you have

Jim

okay, sorry if it is all confusing. I took a bunch of screen caps because I know that helps a ton! -Jim ;)

Okay the first screen cap is of the form "TRACKING REGISTER" form including the subform "subform register" This is the footer of the subform

http://www.mykesdesigns.com/access/trackingregfooter.jpg its counting my fields with Received in the Action.



This cap is of the main form and it is just pulling from that txt field "txtCOUNT" in the subform.

http://www.mykesdesigns.com/access/trackingregmain.jpg


The next one is what the form looks like in form view:

http://www.mykesdesigns.com/access/trackingregform.jpg




This next one is what I have for the query in Design view "Inventory Query"

http://www.mykesdesigns.com/access/ventoryquerydv.jpg This is where my problem is.. When I run the query I get the pop up that says
"Enter Parameter Vaule"
Forms!subform register!txtCOUNT

If I hit OK
here is what my query looks like in Datasheet view:

http://www.mykesdesigns.com/access/ventoryquerydserror.jpg




I hope this will help out a bunch, if not Ill be willing to explain what ever needed. Thanks guys!
Feb 7 '08 #7

Jim Doherty
Expert 100+
P: 897
okay, sorry if it is all confusing. I took a bunch of screen caps because I know that helps a ton! -Jim ;)

Okay the first screen cap is of the form "TRACKING REGISTER" form including the subform "subform register" This is the footer of the subform

http://www.mykesdesigns.com/access/trackingregfooter.jpg its counting my fields with Received in the Action.



This cap is of the main form and it is just pulling from that txt field "txtCOUNT" in the subform.

http://www.mykesdesigns.com/access/trackingregmain.jpg


The next one is what the form looks like in form view:

http://www.mykesdesigns.com/access/trackingregform.jpg




This next one is what I have for the query in Design view "Inventory Query"

http://www.mykesdesigns.com/access/ventoryquerydv.jpg This is where my problem is.. When I run the query I get the pop up that says
"Enter Parameter Vaule"
Forms!subform register!txtCOUNT

If I hit OK
here is what my query looks like in Datasheet view:

http://www.mykesdesigns.com/access/ventoryquerydserror.jpg




I hope this will help out a bunch, if not Ill be willing to explain what ever needed. Thanks guys!

Hi again,

Well firstly the form reference in the query syntax for the subform is not properly referenced in that Forms!subform register!txtCount is hierarchichal to the main form given its a mounted subform it should be Forms!yourmainformname![Subform register].Form!txtCount in the query syntax.

Either way I'm not at all convinced that going down this chosen design route is the most efficient option here anyway. If this is mission
critical for you Mike I will take a look at what you have if you can send me the database.

As is, the thread is in danger of becoming a jumbled mess of misunderstanding based on being blind overall to what you have. A proper physical look at the database will enable me to give a proper answer and close the thread properly

Regards

Jim :)
Feb 8 '08 #8

100+
P: 164
Hi again,

Well firstly the form reference in the query syntax for the subform is not properly referenced in that Forms!subform register!txtCount is hierarchichal to the main form given its a mounted subform it should be Forms!yourmainformname![Subform register].Form!txtCount in the query syntax.

Either way I'm not at all convinced that going down this chosen design route is the most efficient option here anyway. If this is mission
critical for you Mike I will take a look at what you have if you can send me the database.

As is, the thread is in danger of becoming a jumbled mess of misunderstanding based on being blind overall to what you have. A proper physical look at the database will enable me to give a proper answer and close the thread properly

Regards

Jim :)
Jim thanks a ton for the offer, but I am afraid you will not even want to try and take a look at this database! It was the first one I have ever done and it is a MESS. I am the only one using it so I am fine with the mess for now. Eventually I will be rebuilding it. It also has linked tables and it pulls from files off our server. If you truly wish I can send it, but I honestly dont want you to waste any of your time with it.

The only reason I want this to be in that query is because I mainly control this database from an ASP.NET app I built with VWD. Having this totals field would just help me out a bunch with my daily tasks. I will try that query suggestion, which is what I think "minion" was getting at as well?

I will update tomorrow morning with how it went!

Thanks Jim!
Feb 8 '08 #9

Jim Doherty
Expert 100+
P: 897
Jim thanks a ton for the offer, but I am afraid you will not even want to try and take a look at this database! It was the first one I have ever done and it is a MESS. I am the only one using it so I am fine with the mess for now. Eventually I will be rebuilding it. It also has linked tables and it pulls from files off our server. If you truly wish I can send it, but I honestly dont want you to waste any of your time with it.

The only reason I want this to be in that query is because I mainly control this database from an ASP.NET app I built with VWD. Having this totals field would just help me out a bunch with my daily tasks. I will try that query suggestion, which is what I think "minion" was getting at as well?

I will update tomorrow morning with how it went!

Thanks Jim!
OK got that :)

Jim
Feb 8 '08 #10

100+
P: 164
OK got that :)

Jim
Okay, So I believe my query is as it should be? and I am still getting the enter parameter error.

query...

Expand|Select|Wrap|Line Numbers
  1. SELECT [Register Form].STATUS, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER].[Form]![Subform Register].[txtCOUNT] AS Count
  2. FROM [Register Form]
  3. WHERE ((([Register Form].STATUS)="IN"))
  4. ORDER BY [Register Form].Model;
  5.  
Feb 8 '08 #11

Post your reply

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