Query run from form based on text box

[Joe_Hunt via AccessMonster.com]

Hello. I'm trying to move a process from Excel in to Access, and while it's
been a great learning experience (I'm good in Excel, but all but a newbie in
Access) I am having an issue that I hope someone would be kind enough to lend
me a hand with. I have a form that uses a query to pull in data on 70 odd
vehicles, with numerous calculations in other boxes. This works great after
much pounding of my forehead on the keyboard. My issue is that I need to be
able to pull data from one of the other rows in one of the same tables that
the first query is in. In other words I have a vehicle that is named 300_08,
meaning a Chrysler 300, 2008 model, and I need to be able to pull a record
from vehicle 300_07, the 2007 model, into the same form. I wrote a formula in
a text box that changes the 08 to an 07 nicely for all the vehicles (also if
it's an 09 changes it to an 08). I have another query written that seems to
work by itself that pulls in only the 07 records (I can adjust for the others
when I figure this out), but I need the query to reference the aforementioned
text box.

I had thought that maybe I could just put the textbox reference in the
criteria section of the query, but that didn't work. Here's what I used =
[Forms].[Residuals]![Text497] with Residuals being the name of the form and
Text Box 497 having the adjusted vehicle name.

If someone could help I would appreciate it. My forehead is starting to hurt.

 

[ghetto_banjo]

looks like you might just have your ! and . misplaced.

the format to reference a form field in query criteria is

[Forms]![formname].[fieldname]



give that a try and let us know.

 

[Joe_Hunt via AccessMonster.com]

It's asking for me to enter a parameter value. When I type something in it
works, but it needs to reference the form for the value in the text box.

looks like you might just have your ! and . misplaced.

the format to reference a form field in query criteria is

[Forms]![formname].[fieldname]

give that a try and let us know.

 

[ghetto_banjo]

Hmmm. When it asks for a parameter, that means it is not referencing
the form field name that you have entered. You are positive that
text547 is the right name? and you have your form open while running
the query?

 

[Joe_Hunt via AccessMonster.com]

Ah! Partial success. Thank you! I didn't have the query open (didn't know I
needed to do that, sorry. I'm trying to learn). When I'm in the query and
click the exclamation mark it works. I need to be able to pull one of these
fields into my form though. I have a textbox in the right place but it's not
pulling in the value based on what's in textbox497. I assume I need a formula
there that references the query correct?

I have =[Text497].[2007_CMV_Query]![Jan Value 30K Mls]. Text497 is where the
value is that needs the query, 2007_CMV_Query is the query name, and Jan
Value 30K Mls is the column name where the value is I need to pull in.

I really appreciate your help.

 

[Joe_Hunt via AccessMonster.com]

By the way, what I put above doesn't work.

Ah! Partial success. Thank you! I didn't have the query open (didn't know I
needed to do that, sorry. I'm trying to learn). When I'm in the query and
click the exclamation mark it works. I need to be able to pull one of these
fields into my form though. I have a textbox in the right place but it's not
pulling in the value based on what's in textbox497. I assume I need a formula
there that references the query correct?

I have =[Text497].[2007_CMV_Query]![Jan Value 30K Mls]. Text497 is where the
value is that needs the query, 2007_CMV_Query is the query name, and Jan
Value 30K Mls is the column name where the value is I need to pull in.

I really appreciate your help.

Hmmm. When it asks for a parameter, that means it is not referencing
the form field name that you have entered. You are positive that
text547 is the right name? and you have your form open while running
the query?

 

[ghetto_banjo]

If your query always returns exactly one row, then you can set your
other textbox to use the DLookup() function.

set the control source of the textbox to =DLookup("FieldNameInQuery",
"QueryName")

there should be a bunch of DLookup examples in this forum and
elsewhere.



Let us know if that works.

 

[Joe_Hunt via AccessMonster.com]

Woo hoo! That works! Thank you very much and I do appreciate it!