User input data

[Evan]

I want to use a query that will get a field value from user input on a
control in a form. In the control's after update module I am not sure how to
write the code so that the user input is imported into the query. How is
this done?
Thanks Evan

 

[bhammer]

I'm not sure the AfterUpdate event will do all of this. I've been successful
doing this by typing a reference to the user control (e.g. textbox) in the
Criteria under the corresponding field in Design View of the query. Then use
the AfterUpdate event to requery the data you want refreshed (form or query).

 

[Mike Painter]

You can use Forms!Yourformname!YourFieldName in the criteria for that field
in the query.
What happens if the user goes back and changes the value?
Note also that a subform based on the query will do this for you.

 

[Evan]

Thanks, That works but when I switch from query design view to form view I
get a pop-up box "Enter parameter value for field" after I enter a value I
get another pop-up box to enter a value for: [Forms]![User_Income]![Text0]
and after I enter a value. Then the query appears as a subform on my form
with all the correct data. In the Query field under criteria I have:
[Income]=[Forms]![User_Income]![Text0]. Where is my mistake?

Thanks Evan

 

[John W. Vinson]

Thanks, That works but when I switch from query design view to form view I
get a pop-up box "Enter parameter value for field" after I enter a value I
get another pop-up box to enter a value for: [Forms]![User_Income]![Text0]
and after I enter a value. Then the query appears as a subform on my form
with all the correct data. In the Query field under criteria I have:
[Income]=[Forms]![User_Income]![Text0]. Where is my mistake?

It would appear that either there is no control named Text0 on the form named
User_Income, or that the form named User_Income is not open at the time.

Be sure that there actually *is* such a form and such a textbox, and that the
form is open at the time you run the query (or open the Form or Report based
on the query). One handy way is to put a command button on User_Income to open
the desired form.

 

[Mike Painter]

Thanks, That works but when I switch from query design view to form
view I get a pop-up box "Enter parameter value for field" after I
enter a value I get another pop-up box to enter a value for:
[Forms]![User_Income]![Text0] and after I enter a value. Then the
query appears as a subform on my form with all the correct data. In
the Query field under criteria I have:
[Income]=[Forms]![User_Income]![Text0]. Where is my mistake?

It would appear that either there is no control named Text0 on the
form named User_Income, or that the form named User_Income is not
open at the time.

Be sure that there actually *is* such a form and such a textbox, and
that the form is open at the time you run the query (or open the Form
or Report based on the query). One handy way is to put a command
button on User_Income to open the desired form.

Is [Forms] seen as Forms in Access?

 

[John W. Vinson]

Is [Forms] seen as Forms in Access?

Yes, I routinely bracket all parameters. It may be essential to do so,
otherwise you may end up with a criterion searching for the string
"Forms!myform!text0" in the field!

 

[Evan]

There is a main form named [User_Income] on it is a text box named [Text0]
and a subform named [Tax Rate & Amt subform]
I'm still having a problem. I'm hoping you can help me solve it. The
subform is based on a query named [Tax Rate & Amt that has a field: Income:
[Forms]![User_Income]![Text0] and in the criteria I have: [Income] =
[Forms]![User_Income]![Text0]
When I use the main form to put a value in the text box or switch from
design view to data sheet view on the query I get the "Enter Parameter Value"
for Income popupbox. After entering a value I get another "Enter Parameter
Value" for Forms!User_Income!Text0 and after entering a value again in this
box the main form displays the subform with the queried values. Any idea on
what I'm doing wrong?

thanks Evan

Is [Forms] seen as Forms in Access?

Yes, I routinely bracket all parameters. It may be essential to do so,
otherwise you may end up with a criterion searching for the string
"Forms!myform!text0" in the field!

 

[John W. Vinson]

There is a main form named [User_Income] on it is a text box named [Text0]
and a subform named [Tax Rate & Amt subform]
I'm still having a problem. I'm hoping you can help me solve it. The
subform is based on a query named [Tax Rate & Amt that has a field: Income:
[Forms]![User_Income]![Text0] and in the criteria I have: [Income] =
[Forms]![User_Income]![Text0]

That makes no sense. Your *query* contains a field that isn't part of any
table, but is instead a reference to a form control; and you're trying to find
those records where the value of the form control is equal to... the value of
the form control!? IT will always be equal to itself!

When I use the main form to put a value in the text box or switch from
design view to data sheet view on the query I get the "Enter Parameter Value"
for Income popupbox. After entering a value I get another "Enter Parameter
Value" for Forms!User_Income!Text0 and after entering a value again in this
box the main form displays the subform with the queried values. Any idea on
what I'm doing wrong?

Using an incorrect query logic. You can't use a form control to search the
value of the form control!!!

What are the Recordsources of the mainform and the subform (post the SQL)?
What are the Master Link Field and Child Link Field? What is the logical
relationship between the tables? What do you intend to do with Text0? Would
you consider renaming Text0 to some meaningful name (if only for your own
benefit)?

 

[Evan]

I think I should start over. One form now with test box and query results as
orm. User opens form types in his income in text box renamed "UserInc." Is
it possible for the query to grab this value, run it through the query and
show it on the form?

What are the Recordsources of the mainform and the subform (post the SQL)?
What are the Master Link Field and Child Link Field? What is the logical
relationship between the tables? What do you intend to do with Text0? > rename Text0 to some meaningful name

 

[John W. Vinson]

I think I should start over. One form now with test box and query results as
orm. User opens form types in his income in text box renamed "UserInc." Is
it possible for the query to grab this value, run it through the query and
show it on the form?

I'd suggest a two-form solution. Have a form (it can be bound or unbound)
named, say, frmCrit with an unbound textbox named UserInc. In your query
expression use

[Forms]![frmCrit]![UserInc]

as a parameter.

Base a second form on this query. You can either put a command button on
frmCrit to open the second form, or use the second form as a Subform on
frmCrit, and requery the subform in the afterupdate event of UserInc.

 

[Evan]

John thanks so much for your help, but still having a problem. I created a
form"frmCrit" w/ textbox "UserInc" Then in my query created a field:
[Income] with criteria: [Forms]![frmCrit]![UserInc] and based a 2nd form on
this query and used it as a subform in my main form "frmCrit" but when I
switch to form view the "enter parameter Value for Income" popup box comes
up. So I 'm assuming that the criteria: [Forms]![frmCrit]![UserInc] goes
someplace other than a field named: [Income] but where?

My SQL statement for the query is:

SELECT C.CountryName, AllFdTxSch.TaxRate,
[Base]+([TaxRate]*([Income]-[Low])) AS TaxAmount
FROM C INNER JOIN AllFdTxSch ON C.CountryID = AllFdTxSch.CountryID
WHERE (((AllFdTxSch.Low)<=[Income]) AND ((AllFdTxSch.High)>[Income]) AND
(([Income])=[Forms]![frmCrit]![UserInc]))
ORDER BY [Base]+([TaxRate]*([Income]-[Low]));

Thanks Evan

 

[John W. Vinson]

John thanks so much for your help, but still having a problem. I created a
form"frmCrit" w/ textbox "UserInc" Then in my query created a field:
[Income] with criteria: [Forms]![frmCrit]![UserInc] and based a 2nd form on
this query and used it as a subform in my main form "frmCrit" but when I
switch to form view the "enter parameter Value for Income" popup box comes
up. So I 'm assuming that the criteria: [Forms]![frmCrit]![UserInc] goes
someplace other than a field named: [Income] but where?

My SQL statement for the query is:

SELECT C.CountryName, AllFdTxSch.TaxRate,
[Base]+([TaxRate]*([Income]-[Low])) AS TaxAmount
FROM C INNER JOIN AllFdTxSch ON C.CountryID = AllFdTxSch.CountryID
WHERE (((AllFdTxSch.Low)<=[Income]) AND ((AllFdTxSch.High)>[Income]) AND
(([Income])=[Forms]![frmCrit]![UserInc]))
ORDER BY [Base]+([TaxRate]*([Income]-[Low]));

Is there indeed a field named Income in your table named C, or in AllFdTxSch?
And do you want the user to match it exactly, to the penny, in their UserInc
criterion?

Step back a bit. What's in your tables? What is this form intended to
accomplish?

 

[Evan]

Neither table C or AllFdTxSch contain a field named Income. C contains the
names of countries and AllFdTxSch contains each country's tax table. Income
is not included in the tables because it is not knowable and is particular to
each user of the form. The form is intended to show what each user's tax
rates and amounts in various countries would be based on his income.

 

[John W. Vinson]

John thanks so much for your help, but still having a problem. I created a
form"frmCrit" w/ textbox "UserInc" Then in my query created a field:
[Income] with criteria: [Forms]![frmCrit]![UserInc] and based a 2nd form on
this query and used it as a subform in my main form "frmCrit" but when I
switch to form view the "enter parameter Value for Income" popup box comes
up. So I 'm assuming that the criteria: [Forms]![frmCrit]![UserInc] goes
someplace other than a field named: [Income] but where?

My SQL statement for the query is:

SELECT C.CountryName, AllFdTxSch.TaxRate,
[Base]+([TaxRate]*([Income]-[Low])) AS TaxAmount
FROM C INNER JOIN AllFdTxSch ON C.CountryID = AllFdTxSch.CountryID
WHERE (((AllFdTxSch.Low)<=[Income]) AND ((AllFdTxSch.High)>[Income]) AND
(([Income])=[Forms]![frmCrit]![UserInc]))
ORDER BY [Base]+([TaxRate]*([Income]-[Low]));

Replace all the instances of [Income] with [Forms]![frmCrit]![UserInc] then,
and remove the criterion

AND (([Income])=[Forms]![frmCrit]![UserInc]))

 

[Evan]

John thanks for all your help popup box fixed, but when I enter a value into
the UserInc text box nothing happens.

Do I need to set thesubform link child & master fields?

My Code is:
Option Compare Database

Private Sub Form_Load()
End Sub

Private Sub TaxRate_Amt_Enter()
End Sub

Private Sub UserInc_AfterUpdate()
Me!UserInc = Null
Me!UserInc.Requery
End Sub

Private Sub UserInc_BeforeUpdate(Cancel As Integer)
End Sub

 

[John W. Vinson]

John thanks for all your help popup box fixed, but when I enter a value into
the UserInc text box nothing happens.

Do I need to set thesubform link child & master fields?

My Code is:
Option Compare Database

Private Sub Form_Load()
End Sub

Private Sub TaxRate_Amt_Enter()
End Sub

Private Sub UserInc_AfterUpdate()
Me!UserInc = Null
Me!UserInc.Requery
End Sub

Private Sub UserInc_BeforeUpdate(Cancel As Integer)
End Sub

All of the pairs of lines

Private Sub...

immeditately followed by

End Sub

can be deleted; they do nothing, you're just cluttering your database with
unused code.

Your AfterUpdate line is setting the UserInc field to blank - and then
requerying the subform *WITH A BLANK VALUE* as the criterion (since you just
erased it). Remove the line

Me!UserInc = Null

 

[Evan]

Wow thanks, almost there. After a value is entered nothing happens until
I toggle to design view and back to form view. any ideas on what is going on?

Evan

 

[John W. Vinson]

Wow thanks, almost there. After a value is entered nothing happens until
I toggle to design view and back to form view. any ideas on what is going on?

Evan

ah... sorry I didn't see that! You need to requery the Subform in the combo
box's afterupdate event, not the combo box itself:

Private Sub UserInc_AfterUpdate()
Me!subformname.Form.Requery
End Sub

where subformname is the name of the Subform control on the main form.