Passing value from text box to query

[abledog]

Hello, I am trying to pass a value from a text box in a form (this value is to be entered by the user). I need this value for a graph that I am generating in a report (the query for the grpah is given below).

So for example if the user enters 100, 1000 is replaced by 100 and 800 is replaced by 80. right now I have it hard coded it in the sql statement below. Thanks for the your help.


SELECT (Format([Commit Dat],"Short Date")) AS Expr1, Sum([CurrentMonth Query For Graph].[Labour Hours Est]) AS [SumOfLabour Hours Est], 1000 AS [100% Capacity], 800 AS [80% Capacity] FROM [CurrentMonth Query For Graph] GROUP BY (Format([Commit Dat],"Short Date")), 1000, 800, (Int([Commit Dat]));

 

[Brianwarnock]

To get data from a form code
Forms!formname!controlname
[] needed around any names with spaces, not considered good practice as it prevents access using its intellisense.

Brian

 

[abledog]

Thanks Brian, this works fine but I still need to fix a small little tweak.

I run into a problem if the form is not open and I try to run the report (with the graph) then the query asks me for a parametre value. I need the value that I am putting in the textbox (in the form ) to be saved somewhere and need this value to be used everytime in the query unless it gets overwritten by inputting a new value in the textbox.

Hopefully this makes sense...

Thanks

 

[abledog]

ok...so I was looking into it a little more and if I can pass the text box value to a particular row and a column in a table...I hav would have my work done....I just don't know how to pass this value to the table.....?

 

[Brianwarnock]

Make the table then base your form on the table.

Brian

 

[abledog]

I still can't figure it out...couple of things:
- The form on which this textbox resides, is referencing another table

ID NumberOfWorkers
1 11

-That is all what I need in my table, In this particular case I would need the 11 to get overwritten everytime a user enters a different value in that text box....

Thanks for your help...I am pretty new to this so don't have in depth understanding....

 

[fredalina]

This may very well be the longest route to solve this problem. Being a beginner, i sometimes suggest things that fall in "the dark side", so someone may come along and tell you so .

One thing you can do is to create a Public variable (you will have to put it in the General Declarations section and NOT within any module) - in my example i called it fldPub. On the AfterUpdate event of the textbox containing the item you need to remember, you will set the public variable to that textbox's value.

You will then need to write a very brief Public Function (i called it Pubb) that sets the value of the function to the public variable:

Public Function Pubb() As String
    Pubb = fldPub
End Function

Then you would call that module in your Query as a field in your Query.

Like i said, though, there may be more simple ways around it, but i've done it before and it does work, even if the form is not open. The only thing you have to beware of is that the form does not have multiple records, or that the latest updated record will have the correct data.

 

[abledog]

Thanks for your response...I tried the whole thing but I kept running into troubles with the Query not recognizing what the function pubb() is ...?

Anyway ....I am just going to make another form and link it to the table...not the prettiest way....but will do the job that I need it for...thanks for all your help...

 

[fredalina]

i'm glad you got it to work for you. i did test the code first, so it must be something in how you have the module set up, etc, but as long as it works for you...