query an unbound field in form.. Possible?

[mbatestblrock]

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 txtReceivedtime s

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 txtReceivedtime s into a query. My query is pulling from the table of the main form and what I have so far looks like this.

SELECT [Register Form].Status, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER]![Receivedtimes] AS Received
FROM [Register Form]
WHERE ((([Register Form].Status)="IN"))
ORDER BY [Register Form].Model;

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!

[Jim Doherty]

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 :)

[mbatestblrock]

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.

[Minion]

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:

[code=sql]
SELECT [Register Form].Status, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER].[Form]![<<Sub Form Name Here>>].[Receivedtimes] AS Received
FROM [Register Form]
WHERE ((([Register Form].Status)="IN"))
ORDER BY [Register Form].Model;
[/code]

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.

[mbatestblrock]

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:

[code=sql]
SELECT [Register Form].Status, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER].[Form]![<<Sub Form Name Here>>].[Receivedtimes] AS Received
FROM [Register Form]
WHERE ((([Register Form].Status)="IN"))
ORDER BY [Register Form].Model;
[/code]

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*

[Jim Doherty]

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

[mbatestblrock]

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.mykesdesign s.com/access/trackingregfoot er.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.mykesdesign s.com/access/trackingregmain .jpg


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

http://www.mykesdesign s.com/access/trackingregform .jpg




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

http://www.mykesdesign s.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!txtCOU NT

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

http://www.mykesdesign s.com/access/ventoryquerydse rror.jpg




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

[Jim Doherty]

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.mykesdesign s.com/access/trackingregfoot er.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.mykesdesign s.com/access/trackingregmain .jpg


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

http://www.mykesdesign s.com/access/trackingregform .jpg




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

http://www.mykesdesign s.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!txtCOU NT

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

http://www.mykesdesign s.com/access/ventoryquerydse rror.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!txtCou nt is hierarchichal to the main form given its a mounted subform it should be Forms!yourmainf ormname![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 misunderstandin g 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 :)

[mbatestblrock]

Hi again,

Well firstly the form reference in the query syntax for the subform is not properly referenced in that Forms!subform register!txtCou nt is hierarchichal to the main form given its a mounted subform it should be Forms!yourmainf ormname![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 misunderstandin g 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!

[Jim Doherty]

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

[mbatestblrock]

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...

SELECT [Register Form].STATUS, [Register Form].Model, [Register Form].[Serial Number], [Forms]![TRACKING REGISTER].[Form]![Subform Register].[txtCOUNT] AS Count
FROM [Register Form]
WHERE ((([Register Form].STATUS)="IN"))
ORDER BY [Register Form].Model;