set value of txtbox in form = select query

  • Thread starter Thread starter skoalnreds
  • Start date Start date
S

skoalnreds

Cliff Notes version:

I have a form with a 'locked' textbox that I want to populate with the
result of a SELECT query.

The form has another textbox called ItemID. What I want to query is
the COUNT of records in the Inventory table where the Inventory Table
Item ID is equal to the value in the form's ItemID textbox, and
display the result in the locked textbox mentioned above.

As the user navigates from record to record, the locked textbox should
refresh/requery.

I'm very new to all this, so if this is uber-simple, please don't
flame me. Thanks.
 
You cannot assign a query to a textbox. The result of a query is 0, 1 or
more rows with 1 or more columns. The fact that this query results in 1
row and one column doesn't change that.

In this case you can use the DCount function. Make an unbound textbox on
the form and put into the ControlSource property something like:
=Nz(DCount("*","tblInventory","ItemID="  & ItemID),0)

--
Groeten,

Peterhttp://access.xps350.com

--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---- Hide quoted text -

- Show quoted text -
Click to expand...

Hi, thank you for your response, but it does not work. When I run the
form, the result shows #Error.

Here's what's in the controlsource:

=Nz(DCount("*","Inventory","Item_ID=" & [txtItem]),0)

the txtItem is another textbox on the form, whose value should be used
to count the number of rows in the Inventory table. I don't know why
Access keeps putting the [ ] around txtItem. I'm probably doing
something wrong but can't figure it out. Thanks again.
 
You cannot assign a query to a textbox. The result of a query is 0, 1 or
more rows with 1 or more columns. The fact that this query results in 1
row and one column doesn't change that.

In this case you can use the DCount function. Make an unbound textbox on
the form and put into the ControlSource property something like:
=Nz(DCount("*","tblInventory","ItemID="  & ItemID),0)

--
Groeten,

Peterhttp://access.xps350.com

--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---- Hide quoted text -

- Show quoted text -
Click to expand...

Figured it out. I changed the control source to read:
=Nz(DCount("*","Inventory","Item_ID=txtItem"),0)
and it works perfectly. Thanks for pointing me in the right direction.
 
Back
Top